|Oracle7 Server Application Developer's Guide||
CREATE TRIGGER print_salary_changes BEFORE DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW WHEN (new.empno > 0) DECLARE sal_diff number; BEGIN sal_diff := new.sal - old.sal; dbms_output.put('Old salary: ' || :old.sal); dbms_output.put(' New salary: ' || :new.sal); dbms_output.put_line(' Difference ' || sal_diff); END; /
If you enter a SQL statement such as
UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10
the trigger will fire once for each row that is updated, and it prints the new and old salaries, and the difference.
The CREATE (or CREATE OR REPLACE) statement fails if any errors exist in the PL/SQL block.
The following sections use this example to illustrate the way that parts of a trigger are specified. For more realistic examples of CREATE TRIGGER statements, see "Examples of Trigger Applications" .
Additional Information: The location of this file is operating system dependent; see your platform-specific Oracle documentation.
Note: AFTER row triggers are slightly more efficient than BEFORE row triggers. With BEFORE row triggers, affected data blocks must be read (logical read, not physical read) once for the trigger and then again for the triggering statement. Alternatively, with AFTER row triggers, the data blocks need only be read once for both the triggering statement and the trigger.
DELETE FROM emp; INSERT INTO emp VALUES ( . . . ); INSERT INTO emp SELECT . . . FROM . . . ; UPDATE emp SET . . . ;
The previous example of the PRINT_SALARY_CHANGES trigger might have included a column list in the triggering statement, as in
. . . BEFORE DELETE OR INSERT OR UPDATE OF ename ON emp . . .
For example, you define the following trigger:
CREATE TRIGGER log_salary_increase AFTER UPDATE ON emp FOR EACH ROW WHEN (new.sal > 1000) BEGIN INSERT INTO emp_log (emp_id, log_date, new_salary, action) VALUES (:new.empno, SYSDATE, :new.sal, 'NEW SAL'); END;
and then issue the SQL statement
UPDATE emp SET sal = sal + 1000.0 WHERE deptno = 20;
If there are five employees in department 20, the trigger will fire five times when this statement is issued, since five rows are affected.
The following trigger fires only once for each UPDATE of the EMP table:
CREATE TRIGGER log_emp_update AFTER UPDATE ON emp BEGIN INSERT INTO emp_log (log_date, action) VALUES (SYSDATE, 'EMP COMMISSIONS CHANGED'); END;
For the order of trigger firing, see the Oracle7 Server Concepts manual.
For example, in the PRINT_SALARY_CHANGES trigger, the trigger body would not be executed if the new value of EMPNO is zero, NULL, or negative. In more realistic examples, you might test if one column value is less than another.
The expression in a WHEN clause of a row trigger can include correlation names, which are explained below. The expression in a WHEN clause must be a SQL expression and cannot include a subquery. You cannot use a PL/SQL expression (including user-defined functions) in the WHEN clause.
IF :new.sal > 10000 . . . IF :new.sal < :old.sal . . .
Old and new values are available in both BEFORE and AFTER row triggers. A NEW column value can be assigned in a BEFORE row trigger, but not in an AFTER row trigger (because the triggering statement takes effect before an AFTER row trigger is fired). If a BEFORE row trigger changes the value of NEW.COLUMN, an AFTER row trigger fired by the same statement sees the change assigned by the BEFORE row trigger.
Correlation names can also be used in the Boolean expression of a WHEN clause. A colon must precede the OLD and NEW qualifiers when they are used in a trigger's body, but a colon is not allowed when using the qualifiers in the WHEN clause or the REFERENCING option.
For example, assume you have a table named NEW with columns FIELD1 (number) and FIELD2 (character). The following CREATE TRIGGER example shows a trigger associated with the NEW table that can use correlation names and avoid naming conflicts between the correlation names and the table name:
CREATE TRIGGER PRINT_SALARY_CHANGES BEFORE UPDATE ON new REFERENCING new AS newest FOR EACH ROW BEGIN :newest.field2 := TO_CHAR (:newest.field1); END;
Notice that the NEW qualifier is renamed to NEWEST using the REFERENCING option, and is then used in the trigger body.
INSERT OR UPDATE ON emp
Within the code of the trigger body, you can include the following conditions:
IF INSERTING THEN . . . END IF; IF UPDATING THEN . . . END IF;
The first condition evaluates to TRUE only if the statement that fired the trigger is an INSERT statement; the second condition evaluates to TRUE only if the statement that fired the trigger is an UPDATE statement.
In an UPDATE trigger, a column name can be specified with an UPDATING conditional predicate to determine if the named column is being updated. For example, assume a trigger is defined as
CREATE TRIGGER . . . . . . UPDATE OF sal, comm ON emp . . . BEGIN . . . IF UPDATING ('SAL') THEN . . . END IF; END;
The code in the THEN clause executes only if the triggering UPDATE statement updates the SAL column. The following statement would fire the above trigger and cause the UPDATING (sal) conditional predicate to evaluate to TRUE:
UPDATE emp SET sal = sal + 100;
For more information about error processing in PL/SQL program units, see "Handling Errors" and "Declaring Exceptions and Exception Handling Routines" .
CREATE TRIGGER example AFTER INSERT ON emp FOR EACH ROW BEGIN INSERT INTO emp@remote -- <- compilation fails here VALUES ('x'); -- when dblink is inaccessible EXCEPTION WHEN OTHERS THEN INSERT INTO emp_log VALUES ('x'); END;
A trigger is compiled when it is created. Thus, if a remote site is unavailable when the trigger must compile, Oracle cannot validate the statement accessing the remote database, and the compilation fails. The previous example exception statement cannot execute because the trigger does not complete compilation.
Because stored procedures are stored in a compiled form, the work-around for the above example is as follows:
CREATE TRIGGER example AFTER INSERT ON emp FOR EACH ROW BEGIN insert_row_proc; END; CREATE PROCEDURE insert_row_proc BEGIN INSERT INTO emp@remote VALUES ('x'); EXCEPTION WHEN OTHERS THEN INSERT INTO emp_log VALUES ('x'); END;
The trigger in this example compiles successfully and calls the stored procedure, which already has a validated statement for accessing the remote database; thus, when the remote INSERT statement fails because the link is down, the exception is caught.
DDL statements are not allowed in the body of a trigger. Also, no transaction control statements are allowed in a trigger. The commands ROLLBACK, COMMIT, and SAVEPOINT cannot be used.
Note: A procedure called by a trigger cannot execute the above transaction control statements because the procedure executes within the context of the trigger body.
Statements inside a trigger can reference remote objects. However, pay special attention when calling remote procedures from within a local trigger; since if a timestamp or signature mismatch is found during execution of the trigger, the remote procedure is not executed and the trigger is invalidated.
When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Oracle allows up to 32 triggers to cascade at any one time. However, you can effectively limit the number of trigger cascades using the initialization parameter OPEN_CURSORS, because a cursor must be opened for every execution of a trigger.
Oracle executes all triggers of the same type before executing triggers of a different type. If you have multiple triggers of the same type on a single table, Oracle chooses an arbitrary order to execute these triggers. See the Oracle7 Server Concepts manual for more information on the firing order of triggers.
Each subsequent trigger sees the changes made by the previously fired triggers. Each trigger can see the old and new values. The old values are the original values and the new values are the current values as set by the most recently fired UPDATE or INSERT trigger.
To ensure that multiple triggered actions occur in a specific order, you must consolidate these actions into a single trigger (for example, by having the trigger call a series of procedures).
You cannot open a database that contains multiple triggers of the same type if you are using any version of Oracle before release 7.1, nor can you open such a database if your COMPATIBLE initialization parameter is set to a version earlier than 7.1.0.
Tables are never considered mutating or constraining for statement triggers unless the trigger is fired as the result of a DELETE CASCADE.
For all row triggers, or for statement triggers that were fired as the result of a DELETE CASCADE, there are two important restrictions regarding mutating and constraining tables. These restrictions prevent a trigger from seeing an inconsistent set of data.
Figure 9 - 1. Mutating Tables
Notice that the SQL statement is executed for the first row of the table and then an AFTER ROW trigger is fired. In turn, a statement in the AFTER ROW trigger body attempts to query the original table. However, because the EMP table is mutating, this query is not allowed by Oracle. If attempted, a runtime error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application.
Consider the following trigger:
CREATE OR REPLACE TRIGGER emp_count AFTER DELETE ON EMP FOR EACH ROW DECLARE n INTEGER; BEGIN SELECT COUNT(*) INTO n FROM emp; DBMS_OUTPUT.PUT_LINE(' There are now ' || n || ' employees.'); END;
If the SQL statement
DELETE FROM emp WHERE empno = 7499;
is issued, the following error is returned:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
Oracle returns this error when the trigger fires since the table is mutating when the first row is deleted. (Only one row is deleted by the statement, since EMPNO is a primary key, but Oracle has no way of knowing that.)
If you delete the line ``FOR EACH ROW'' from the trigger above, the trigger becomes a statement trigger, the table is not mutating when the trigger fires, and the trigger does output the correct data.
If you need to update a mutating or constraining table, you could use a temporary table, a PL/SQL table, or a package variable to bypass these restrictions. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers--an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.
Declarative integrity constraints are checked at various times with respect to row triggers; see the Oracle7 Server Concepts manual for information about the interaction of triggers and integrity constraints.
Because declarative referential integrity constraints are currently not supported between tables on different nodes of a distributed database, the constraining table restrictions do not apply to triggers that access remote nodes. These restrictions are also not enforced among tables in the same database that are connected by loop-back database links. A loop-back database link makes a local table appear remote by defining a SQL*Net path back to the database that contains the link.
You should not use loop-back database links to circumvent the trigger restrictions. Such applications might behave unpredictably. Trigger restrictions, which were implemented to prevent row triggers from seeing an inconsistent set of data, might be enforced on loop-back database links in future releases of Oracle.
SELECT username FROM USER_USERS
in a trigger, the name of the owner of the trigger is returned, not the name of user who is updating the table.
In Oracle7 release 7.3, triggers are fully compiled when the CREATE TRIGGER command is issued, and the pcode is stored in the data dictionary. Hence, firing the trigger no longer requires the opening of a shared cursor to run the trigger action. Instead, the trigger is executed directly.
If errors occur during the compilation of a trigger, the trigger is still created. If a DML statement fires this trigger, the DML statement will fail. (In both release 7.3 and releases 7.2 and earlier, runtime trigger errors always cause the DML statement to fail.) You can use the SHOW ERRORS command in SQL*Plus or Server Manager to see any compilation errors when you create a trigger, or you can SELECT the errors from the USER_ERRORS view.
You can examine the ALL_DEPENDENCIES view to see the dependencies for a trigger. For example, the statement
SELECT NAME, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE FROM ALL_DEPENDENCIES WHERE OWNER = 'SCOTT' and TYPE = 'TRIGGER';
shows the dependencies for the triggers in the SCOTT schema.
ALTER TRIGGER print_salary_changes COMPILE;
recompiles the PRINT_SALARY_CHANGES trigger
Downgrading from 7.3 or later to a release prior to 7.3 requires that you execute the cat73xxd.sql downgrade script. This handles portability issues between stored and non-stored trigger releases.
When replacing a trigger, you must include the OR REPLACE option in the CREATE TRIGGER statement. The OR REPLACE option is provided to allow a new version of an existing trigger to replace the older version without affecting any grants made for the original version of the trigger.
Alternatively, the trigger can be dropped using the DROP TRIGGER command, and you can rerun the CREATE TRIGGER command.
To drop a trigger, the trigger must be in your schema or you must have the DROP ANY TRIGGER system privilege.
An enabled trigger executes its trigger body if a triggering statement is issued and the trigger restriction (if any) evaluates to TRUE.
A disabled trigger does not execute its trigger body, even if a triggering statement is issued and the trigger restriction (if any) evaluates to TRUE.
ALTER TRIGGER reorder DISABLE;
All triggers associated with a table can be disabled with one statement using the ALTER TABLE command with the DISABLE clause and the ALL TRIGGERS option. For example, to disable all triggers defined for the INVENTORY table, enter the following statement:
ALTER TABLE inventory
DISABLE ALL TRIGGERS;
Enable a disabled trigger using the ALTER TRIGGER command with the ENABLE option. To enable the disabled trigger named REORDER of the INVENTORY table, enter the following statement:
ALTER TRIGGER reorder ENABLE;
All triggers defined for a specific table can be enabled with one statement using the ALTER TABLE command with the ENABLE clause with the ALL TRIGGERS option. For example, to enable all triggers defined for the INVENTORY table, enter the following statement:
ALTER TABLE inventory
ENABLE ALL TRIGGERS;
CREATE TRIGGER reorder AFTER UPDATE OF parts_on_hand ON inventory FOR EACH ROW WHEN(new.parts_on_hand < new.reorder_point) DECLARE x NUMBER; BEGIN SELECT COUNT(*) INTO x FROM pending_orders WHERE part_no = :new.part_no; IF x = 0 THEN INSERT INTO pending_orders VALUES (:new.part_no, :new.reorder_quantity, sysdate); END IF; END;
The following two queries return information about the REORDER trigger:
SELECT trigger_type, triggering_event, table_name FROM user_triggers WHERE name = 'REORDER'; TYPE TRIGGERING_STATEMENT TABLE_NAME ---------------- -------------------------- ------------ AFTER EACH ROW UPDATE INVENTORY SELECT trigger_body FROM user_triggers WHERE name = 'REORDER'; TRIGGER_BODY -------------------------------------------- DECLARE x NUMBER; BEGIN SELECT COUNT(*) INTO x FROM pending_orders WHERE part_no = :new.part_no; IF x = 0 THEN INSERT INTO pending_orders VALUES (:new.part_no, :new.reorder_quantity, sysdate); END IF; END;
Sometimes, the Oracle AUDIT command is considered a security audit facility, while triggers can provide financial audit facility.
When deciding whether to create a trigger to audit database activity, consider what Oracle's auditing features provide, compared to auditing defined by triggers.
DML as well as DDL auditing
Standard auditing options permit auditing of DML and DDL statements regarding all types of schema objects and structures. Comparatively, triggers only permit auditing of DML statements issued against tables.
Centralized audit trail
All database audit information is recorded centrally and automatically using the auditing features of Oracle.
Auditing features enabled using the standard Oracle features are easier to declare and maintain, and less prone to errors when compared to auditing functions defined by triggers.
Auditing options can be audited
Any changes to existing auditing options can also be audited to guard against malicious database activity.
Session and execution time auditing
Using the database auditing features, records can be generated once every time an audited statement is issued (BY ACCESS) or once for every session that issues an audited statement (BY SESSION). Triggers cannot audit by session; an audit record is generated each time a trigger-audited table is referenced.
Auditing of unsuccessful data access
Database auditing can be set to audit when unsuccessful data access occurs. However, any audit information generated by a trigger is rolled back if the triggering statement is rolled back.
Sessions can be audited
Connections and disconnections, as well as session activity (physical I/Os, logical I/Os, deadlocks, etc.), can be recorded using standard database auditing.
When using triggers to provide sophisticated auditing, AFTER triggers are normally used. By using AFTER triggers, auditing information is recorded after the triggering statement is subjected to any applicable integrity constraints, preventing cases where the audit processing is carried out unnecessarily for statements that generate exceptions to integrity constraints.
When to use AFTER row vs. AFTER statement triggers depends on the information being audited. For example, row triggers provide value-based auditing on a per-row basis for tables. Triggers can also require the user to supply a "reason code" for issuing the audited SQL statement, which can be useful in both row and statement-level auditing situations.
The following example demonstrates a trigger that audits modifications to the EMP table on a per-row basis. It requires that a "reason code" be stored in a global package variable before the update.
This trigger demonstrates
CREATE TRIGGER audit_employee AFTER INSERT OR DELETE OR UPDATE ON emp FOR EACH ROW BEGIN /* AUDITPACKAGE is a package with a public package variable REASON. REASON could be set by the application by a command such as EXECUTE AUDITPACKAGE.SET_REASON(reason_string). Note that a package variable has state for the duration of a session and that each session has a separate copy of all package variables. */
IF auditpackage.reason IS NULL THEN raise_application_error(-20201, 'Must specify reason' || ' with AUDITPACKAGE.SET_REASON(reason_string)'); END IF; /* If the above conditional evaluates to TRUE, the user-specified error number and message is raised, the trigger stops execution, and the effects of the triggering statement are rolled back. Otherwise, a new row is inserted into the predefined auditing table named AUDIT_EMPLOYEE containing the existing and new values of the EMP table and the reason code defined by the REASON variable of AUDITPACKAGE. Note that the "old" values are NULL if triggering statement is an INSERT and the "new" values are NULL if the triggering statement is a DELETE. */ INSERT INTO audit_employee VALUES (:old.ssn, :old.name, :old.job_classification, :old.sal, :new.ssn, :new.name, :new.job_classification, :new.sal, auditpackage.reason, user, sysdate ); END;
Optionally, you can also set the reason code back to NULL if you wanted to force the reason code to be set for every update. The following simple AFTER statement trigger sets the reason code back to NULL after the triggering statement is executed:
CREATE TRIGGER audit_employee_reset AFTER INSERT OR DELETE OR UPDATE ON emp BEGIN auditpackage.set_reason(NULL); END;
Notice that the previous two triggers are both fired by the same type of SQL statement. However, the AFTER row trigger is fired once for each row of the table affected by the triggering statement, while the AFTER statement trigger is fired only once after the triggering statement execution is completed.
Another example of using triggers to do auditing is shown below. This trigger tracks changes being made to the EMP table, and stores this information in AUDIT_TABLE and AUDIT_TABLE_VALUES.
CREATE OR REPLACE TRIGGER audit_emp AFTER INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW DECLARE time_now DATE; terminal CHAR(10); BEGIN -- get current time, and the terminal of the user time_now := SYSDATE; terminal := USERENV('TERMINAL'); -- record new employee primary key IF INSERTING THEN INSERT INTO audit_table VALUES (audit_seq.NEXTVAL, user, time_now, terminal, 'EMP', 'INSERT', :new.empno); -- record primary key of the deleted row ELSIF DELETING THEN INSERT INTO audit_table VALUES (audit_seq.NEXTVAL, user, time_now, terminal, 'EMP', 'DELETE', :old.empno); -- for updates, record the primary key -- of the row being updated ELSE INSERT INTO audit_table VALUES (audit_seq.NEXTVAL, user, time_now, terminal, 'EMP', 'UPDATE', :old.empno); -- and for SAL and DEPTNO, record old and new values IF UPDATING ('SAL') THEN INSERT INTO audit_table_values VALUES (audit_seq.CURRVAL, 'SAL', :old.sal, :new.sal); ELSIF UPDATING ('DEPTNO') THEN INSERT INTO audit_table_values VALUES (audit_seq.CURRVAL, 'DEPTNO', :old.deptno, :new.deptno); END IF; END IF; END; /
Declarative integrity constraints are statements about the database that are always true. A constraint applies to existing data in the table and any statement that manipulates the table; for more information, see Chapter 6.
Triggers constrain what a transaction can do. A trigger does not apply to data loaded before the definition of the trigger; therefore, it is not known if all data in a table conforms to the rules established by an associated trigger.
Although triggers can be written to enforce many of the same rules supported by Oracle's declarative integrity constraint features, triggers should only be used to enforce complex business rules that cannot be defined using standard integrity constraints. The declarative integrity constraint features provided with Oracle offer the following advantages when compared to constraints defined by triggers:
Centralized integrity checks
All points of data access must adhere to the global set of rules defined by the integrity constraints corresponding to each schema object.
Constraints defined using the standard integrity constraint features are much easier to write and are less prone to errors when compared with comparable constraints defined by triggers.
While most aspects of data integrity can be defined and enforced using declarative integrity constraints, triggers can be used to enforce complex business constraints not definable using declarative integrity constraints. For example, triggers can be used to enforce
When using triggers to maintain referential integrity, declare the PRIMARY (or UNIQUE) KEY constraint in the parent table. If referential integrity is being maintained between a parent and child table in the same database, you can also declare the foreign key in the child table, but disable it; this prevents the corresponding PRIMARY KEY constraint from being dropped (unless the PRIMARY KEY constraint is explicitly dropped with the CASCADE option).
To maintain referential integrity using triggers:
Several of the triggers include statements that lock rows (SELECT ... FOR UPDATE). This operation is necessary to maintain concurrency as the rows are being processed.
Foreign Key Trigger for Child Table The following trigger guarantees that before an INSERT or UPDATE statement affects a foreign key value, the corresponding value exists in the parent key. The mutating table exception included in the example below allows this trigger to be used with the UPDATE_SET_DEFAULT and UPDATE_CASCADE triggers. This exception can be removed if this trigger is used alone.
CREATE TRIGGER emp_dept_check BEFORE INSERT OR UPDATE OF deptno ON emp FOR EACH ROW WHEN (new.deptno IS NOT NULL) -- Before a row is inserted, or DEPTNO is updated in the EMP -- table, fire this trigger to verify that the new foreign -- key value (DEPTNO) is present in the DEPT table. DECLARE dummy INTEGER; -- used for cursor fetch below invalid_department EXCEPTION; valid_department EXCEPTION; mutating_table EXCEPTION; PRAGMA EXCEPTION_INIT (mutating_table, -4091); -- Cursor used to verify parent key value exists. If -- present, lock parent key's row so it can't be -- deleted by another transaction until this -- transaction is committed or rolled back. CURSOR PRINT_SALARY_CHANGES_cursor (dn NUMBER) IS SELECT deptno FROM dept WHERE deptno = dn FOR UPDATE OF deptno; BEGIN OPEN dummy_cursor (:new.deptno); FETCH dummy_cursor INTO dummy; -- Verify parent key. If not found, raise user-specified -- error number and message. If found, close cursor -- before allowing triggering statement to complete. IF dummy_cursor%NOTFOUND THEN RAISE invalid_department; ELSE RAISE valid_department; END IF; CLOSE dummy_cursor; EXCEPTION WHEN invalid_department THEN CLOSE dummy_cursor; raise_application_error(-20000, 'Invalid Department' || ' Number' || TO_CHAR(:new.deptno)); WHEN valid_department THEN CLOSE dummy_cursor; WHEN mutating_table THEN NULL; END;
UPDATE and DELETE RESTRICT Trigger for the Parent Table The following trigger is defined on the DEPT table to enforce the UPDATE and DELETE RESTRICT referential action on the primary key of the DEPT table:
CREATE TRIGGER dept_restrict BEFORE DELETE OR UPDATE OF deptno ON dept FOR EACH ROW -- Before a row is deleted from DEPT or the primary key -- (DEPTNO) of DEPT is updated, check for dependent -- foreign key values in EMP; rollback if any are found. DECLARE dummy INTEGER; -- used for cursor fetch below employees_present EXCEPTION; employees_not_present EXCEPTION; -- Cursor used to check for dependent foreign key values. CURSOR dummy_cursor (dn NUMBER) IS SELECT deptno FROM emp WHERE deptno = dn; BEGIN OPEN dummy_cursor (:old.deptno); FETCH dummy_cursor INTO dummy; -- If dependent foreign key is found, raise user-specified -- error number and message. If not found, close cursor -- before allowing triggering statement to complete. IF dummy_cursor%FOUND THEN RAISE employees_present; /* dependent rows exist */ ELSE RAISE employees_not_present; /* no dependent rows */ END IF; CLOSE dummy_cursor; EXCEPTION WHEN employees_present THEN CLOSE dummy_cursor; raise_application_error(-20001, 'Employees Present in' || ' Department ' || TO_CHAR(:old.deptno)); WHEN employees_not_present THEN CLOSE dummy_cursor; END;
Note: This trigger will not work with self-referential tables (that is, tables with both the primary/unique key and the foreign key). Also, this trigger does not allow triggers to cycle (such as, A fires B fires A).
UPDATE and DELETE SET NULL Triggers for Parent Table The following trigger is defined on the DEPT table to enforce the UPDATE and DELETE SET NULL referential action on the primary key of the DEPT table:
CREATE TRIGGER dept_set_null AFTER DELETE OR UPDATE OF deptno ON dept FOR EACH ROW -- Before a row is deleted from DEPT or the primary key -- (DEPTNO) of DEPT is updated, set all corresponding -- dependent foreign key values in EMP to NULL. BEGIN IF UPDATING AND :OLD.deptno != :NEW.deptno OR DELETING THEN UPDATE emp SET emp.deptno = NULL WHERE emp.deptno = :old.deptno; END IF; END;
DELETE Cascade Trigger for Parent Table The following trigger on the DEPT table enforces the DELETE CASCADE referential action on the primary key of the DEPT table:
CREATE TRIGGER dept_del_cascade AFTER DELETE ON dept FOR EACH ROW -- Before a row is deleted from DEPT, delete all -- rows from the EMP table whose DEPTNO is the same as -- the DEPTNO being deleted from the DEPT table. BEGIN DELETE FROM emp WHERE emp.deptno = :old.deptno; END;
Note: Typically, the code for DELETE cascade is combined with the code for UPDATE SET NULL or UPDATE SET DEFAULT to account for both updates and deletes.
UPDATE Cascade Trigger for Parent Table The following trigger ensures that if a department number is updated in the DEPT table, this change is propagated to dependent foreign keys in the EMP table:
-- Generate a sequence number to be used as a flag for -- determining if an update has occurred on a column. CREATE SEQUENCE update_sequence INCREMENT BY 1 MAXVALUE 5000 CYCLE;
CREATE PACKAGE integritypackage AS updateseq NUMBER; END integritypackage; CREATE or replace PACKAGE BODY integritypackage AS END integritypackage; ALTER TABLE emp ADD update_id NUMBER; -- create flag col. CREATE TRIGGER dept_cascade1 BEFORE UPDATE OF deptno ON dept DECLARE dummy NUMBER; -- Before updating the DEPT table (this is a statement -- trigger), generate a new sequence number and assign -- it to the public variable UPDATESEQ of a user-defined -- package named INTEGRITYPACKAGE. BEGIN SELECT update_sequence.NEXTVAL INTO dummy FROM dual; integritypackage.updateseq := dummy; END; CREATE TRIGGER dept_cascade2 AFTER DELETE OR UPDATE OF deptno ON dept FOR EACH ROW -- For each department number in DEPT that is updated, -- cascade the update to dependent foreign keys in the -- EMP table. Only cascade the update if the child row -- has not already been updated by this trigger. BEGIN IF UPDATING THEN UPDATE emp SET deptno = :new.deptno, update_id = integritypackage.updateseq /*from 1st*/ WHERE emp.deptno = :old.deptno AND update_id IS NULL; /* only NULL if not updated by the 3rd trigger fired by this same triggering statement */ END IF; IF DELETING THEN -- Before a row is deleted from DEPT, delete all -- rows from the EMP table whose DEPTNO is the same as -- the DEPTNO being deleted from the DEPT table. DELETE FROM emp WHERE emp.deptno = :old.deptno; END IF; END;
CREATE TRIGGER dept_cascade3 AFTER UPDATE OF deptno ON dept BEGIN UPDATE emp SET update_id = NULL WHERE update_id = integritypackage.updateseq; END;
Note: Because this trigger updates the EMP table, the EMP_DEPT_CHECK trigger, if enabled, is also fired. The resulting mutating table error is trapped by the EMP_DEPT_CHECK trigger. You should carefully test any triggers that require error trapping to succeed to ensure that they will always work properly in your environment.
CREATE TRIGGER salary_check BEFORE INSERT OR UPDATE OF sal, job ON emp FOR EACH ROW DECLARE minsal NUMBER; maxsal NUMBER; salary_out_of_range EXCEPTION; BEGIN /* Retrieve the minimum and maximum salary for the employee's new job classification from the SALGRADE table into MINSAL and MAXSAL. */ SELECT minsal, maxsal INTO minsal, maxsal FROM salgrade WHERE job_classification = :new.job; /* If the employee's new salary is less than or greater than the job classification's limits, the exception is raised. The exception message is returned and the pending INSERT or UPDATE statement that fired the trigger is rolled back. */ IF (:new.sal < minsal OR :new.sal > maxsal) THEN RAISE salary_out_of_range; END IF; EXCEPTION WHEN salary_out_of_range THEN raise_application_error (-20300, 'Salary '||TO_CHAR(:new.sal)||' out of range for ' ||'job classification '||:new.job ||' for employee '||:new.name); WHEN NO_DATA_FOUND THEN raise_application_error(-20322, 'Invalid Job Classification ' ||:new.job_classification); END;
When using a trigger to enforce a complex security authorization, it is best to use a BEFORE statement trigger. Using a BEFORE statement trigger has these benefits:
This example shows a trigger used to enforce security. The comments within the code explain the functionality of the trigger.
CREATE TRIGGER emp_permit_changes BEFORE INSERT OR DELETE OR UPDATE ON emp DECLARE dummy INTEGER; not_on_weekends EXCEPTION; not_on_holidays EXCEPTION; non_working_hours EXCEPTION; BEGIN /* check for weekends */ IF (TO_CHAR(sysdate, 'DY') = 'SAT' OR TO_CHAR(sysdate, 'DY') = 'SUN') THEN RAISE not_on_weekends; END IF; /* check for company holidays */ SELECT COUNT(*) INTO dummy FROM company_holidays WHERE TRUNC(day) = TRUNC(sysdate); /* TRUNC gets rid of time parts of dates */ IF dummy > 0 THEN RAISE not_on_holidays; END IF; /* Check for work hours (8am to 6pm) */ IF (TO_CHAR(sysdate, 'HH24') < 8 OR TO_CHAR(sysdate, 'HH24') > 18) THEN RAISE non_working_hours; END IF; EXCEPTION WHEN not_on_weekends THEN raise_application_error(-20324,'May not change ' ||'employee table during the weekend'); WHEN not_on_holidays THEN raise_application_error(-20325,'May not change ' ||'employee table during a holiday'); WHEN non_working_hours THEN raise_application_error(-20326,'May not change ' ||'emp table during non-working hours'); END;
The REORDER trigger example shows a trigger that reorders parts as necessary when certain conditions are met (that is, a triggering statement is issued and the PARTS_ON_HAND value is less than the REORDER_POINT value).
The following example illustrates how a trigger can be used to derive new column values for a table whenever a row is inserted or updated. Comments within the code explain its functionality.
BEFORE INSERT OR UPDATE OF ename ON emp /* Before updating the ENAME field, derive the values for the UPPERNAME and SOUNDEXNAME fields. Users should be restricted from updating these fields directly. */ FOR EACH ROW BEGIN :new.uppername := UPPER(:new.ename); :new.soundexname := SOUNDEX(:new.ename); END;
Copyright © 1996 Oracle Corporation.
All Rights Reserved.