Trigger Procedures

In Oracle 7, triggers are needed to complement the declarative referential integrity constraints (foreign key) specifications only for referential integrity constraints associated with nullifies delete rules and multi-target delete rules. Oracle 7 trigger procedures generated from the OPM schema shown in section 3 are contained in file Example_ritriggers.ORA7:

CREATE OR REPLACE TRIGGER I_FACULTY
BEFORE INSERT ON FACULTY
FOR EACH ROW
DECLARE
    "_cnt1"       INTEGER;
BEGIN
    SELECT COUNT(*)
      INTO "_cnt1"
      FROM STUDENT
      WHERE STUDENT."_oid" = :new."_oid";

    IF ( "_cnt1" != 0 )
    THEN raise_application_error(-20007,
         'Cannot insert into FACULTY because the same instance '
         || '(' || :new."_oid" || ')'
         || ' already exists in exclusive relation STUDENT');
    END IF;
END I_FACULTY;
/

CREATE OR REPLACE TRIGGER U_FACULTY
BEFORE UPDATE ON FACULTY
FOR EACH ROW
DECLARE
    "_cnt1"       INTEGER;
BEGIN
    SELECT COUNT(*)
      INTO "_cnt1"
      FROM STUDENT
      WHERE STUDENT."_oid" = :new."_oid";

    IF ( "_cnt1" != 0 )
    THEN raise_application_error(-20007,
         'Cannot insert into FACULTY because the same instance '
         || '(' || :new."_oid" || ')'
         || ' already exists in exclusive relation STUDENT');
    END IF;
END U_FACULTY;
/

CREATE OR REPLACE TRIGGER I_STUDENT
BEFORE INSERT ON STUDENT
FOR EACH ROW
DECLARE
    "_cnt1"       INTEGER;
BEGIN
    SELECT COUNT(*)
      INTO "_cnt1"
      FROM FACULTY
      WHERE FACULTY."_oid" = :new."_oid";

    IF ( "_cnt1" != 0 )
    THEN raise_application_error(-20007,
         'Cannot insert into STUDENT because the same instance '
         || '(' || :new."_oid" || ')'
         || ' already exists in exclusive relation FACULTY');
    END IF;
END I_STUDENT;
/

CREATE OR REPLACE TRIGGER U_STUDENT
BEFORE UPDATE ON STUDENT
FOR EACH ROW
DECLARE
    "_cnt1"       INTEGER;
BEGIN
    SELECT COUNT(*)
      INTO "_cnt1"
      FROM FACULTY
      WHERE FACULTY."_oid" = :new."_oid";

    IF ( "_cnt1" != 0 )
    THEN raise_application_error(-20007,
         'Cannot insert into STUDENT because the same instance '
         || '(' || :new."_oid" || ')'
         || ' already exists in exclusive relation FACULTY');
    END IF;
END U_STUDENT;
/