| Oracle7 Server Application Developer's Guide |  Library |
 Product |
 Contents |
 Index |
| Oracle7 Server Application Developer's Guide | Library |
Product |
Contents |
Index |
If you are using Trusted Oracle, see the Trusted Oracle7 Server Administrator's Guide for more information about defining and using database triggers.
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" ![[*]](jump.gif)
.
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.
fires after any DELETE, INSERT, or UPDATE on the EMP table. Any of the following statements would trigger the PRINT_SALARY_CHANGES trigger given in the previous example:
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.
The new column values are referenced using the NEW qualifier before the column name, while the old column values are referenced using the OLD qualifier before the column name. For example, if the triggering statement is associated with the EMP table (with the columns SAL, COMM, etc.), you can include statements in the trigger body similar to
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.
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.
enabled
An enabled trigger executes its trigger body if a triggering statement is issued and the trigger restriction (if any) evaluates to TRUE.
disabled
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;
ALTER TABLE inventory
DISABLE ALL TRIGGERS;
ALTER TRIGGER reorder ENABLE;
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.
Declarative method
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 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.
Example
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;
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.
Declarative method
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;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;
Example
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;
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |