Example 1: Defining a cascading delete trigger
The following SQL procedure shows a trigger from the sample
personnel database that deletes rows in several tables before
deleting a row in the EMPLOYEES table. Each associated employee
row (from the tables that have foreign keys referring to the
primary key in the employee row) is deleted. The employee
identification number being deleted (00164) belongs to an
employee who is also a manager; therefore, the MANAGER_ID column
in the DEPARTMENTS table is set to null, as specified by the
trigger.
SQL> SET TRANSACTION READ WRITE;
SQL> --
SQL> -- Display the EMPLOYEE_ID_CASCADE_DELETE trigger
SQL> -- in the sample database:
SQL> --
SQL> SHOW TRIGGER EMPLOYEE_ID_CASCADE_DELETE
EMPLOYEE_ID_CASCADE_DELETE
Source:
EMPLOYEE_ID_CASCADE_DELETE
BEFORE DELETE ON EMPLOYEES
(DELETE FROM DEGREES D WHERE D.EMPLOYEE_ID =
EMPLOYEES.EMPLOYEE_ID)
FOR EACH ROW
(DELETE FROM JOB_HISTORY JH WHERE JH.EMPLOYEE_ID =
EMPLOYEES.EMPLOYEE_ID)
FOR EACH ROW
(DELETE FROM SALARY_HISTORY SH WHERE SH.EMPLOYEE_ID =
EMPLOYEES.EMPLOYEE_ID)
FOR EACH ROW
! Also, if an employee is terminated and that employee
! is the manager of a department, set the manager_id
! null for that department.
(UPDATE DEPARTMENTS D SET D.MANAGER_ID = NULL
WHERE D.MANAGER_ID = EMPLOYEES.EMPLOYEE_ID)
FOR EACH ROW
SQL> --
SQL> -- The EMPLOYEES table has a value of '00164'
SQL> -- in the EMPLOYEE_ID column:
SQL> --
SQL> SELECT * FROM EMPLOYEES E WHERE E.EMPLOYEE_ID = '00164';
EMPLOYEE_ID LAST_NAME FIRST_NAME MIDDLE_INITIAL
ADDRESS_DATA_1 ADDRESS_DATA_2 CITY
STATE POSTAL_CODE SEX BIRTHDAY STATUS_CODE
00164 Toliver Alvin A
146 Parnell Place Chocorua
NH 03817 M 28-Mar-1947 1
1 row selected
SQL> --
SQL> --
SQL> -- The DEGREES table has two values of '00164'
SQL> -- in the EMPLOYEE_ID column:
SQL> --
SQL> SELECT * FROM DEGREES D WHERE D.EMPLOYEE_ID = '00164';
EMPLOYEE_ID COLLEGE_CODE YEAR_GIVEN DEGREE DEGREE_FIELD
00164 PRDU 1973 MA Applied Math
00164 PRDU 1982 PhD Statistics
2 rows selected
SQL> --
SQL> --
SQL> -- The JOB_HISTORY table has the value of '00164' in
SQL> -- several rows in the EMPLOYEE_ID column:
SQL> --
SQL> SELECT * FROM JOB_HISTORY JH WHERE JH.EMPLOYEE_ID = '00164';
EMPLOYEE_ID JOB_CODE JOB_START JOB_END DEPARTMENT_CODE
SUPERVISOR_ID
00164 DMGR 21-Sep-1981 NULL MBMN
00228
00164 SPGM 5-Jul-1980 20-Sep-1981 MCBM
00164
2 rows selected
SQL> --
SQL> --
SQL> -- The SALARY_HISTORY table has a value of '00164'
SQL> -- in several rows in the EMPLOYEE_ID column:
SQL> --
SQL> SELECT * FROM SALARY_HISTORY SH WHERE SH.EMPLOYEE_ID = '00164';
EMPLOYEE_ID SALARY_AMOUNT SALARY_START SALARY_END
00164 $26,291.00 5-Jul-1980 2-Mar-1981
00164 $51,712.00 14-Jan-1983 NULL
00164 $26,291.00 2-Mar-1981 21-Sep-1981
00164 $50,000.00 21-Sep-1981 14-Jan-1983
4 rows selected
SQL> --
SQL> --
SQL> -- The DEPARTMENTS table has a value of '00164'
SQL> -- in the MANAGER_ID column:
SQL> --
SQL> SELECT * FROM DEPARTMENTS D WHERE D.MANAGER_ID = '00164';
DEPARTMENT_CODE DEPARTMENT_NAME MANAGER_ID
BUDGET_PROJECTED BUDGET_ACTUAL
MBMN Board Manufacturing North 00164
NULL NULL
1 row selected
SQL> --
SQL> --
SQL> -- Test the trigger by deleting the row with a value of '00164'
SQL> -- in the EMPLOYEE_ID column from the EMPLOYEES table:
SQL> --
SQL> DELETE FROM EMPLOYEES E WHERE E.EMPLOYEE_ID = '00164';
1 row deleted
SQL> --
SQL> -- The row with a value of '00164' in the EMPLOYEE_ID column
SQL> -- was deleted from the EMPLOYEES table:
SQL> --
SQL> SELECT * FROM EMPLOYEES E WHERE E.EMPLOYEE_ID = '00164';
0 rows selected
SQL> --
SQL> -- The rows with a value of '00164' in the EMPLOYEE_ID column
SQL> -- were deleted from the DEGREES table:
SQL> --
SQL> SELECT * FROM DEGREES D WHERE D.EMPLOYEE_ID = '00164';
0 rows selected
SQL> --
SQL> -- The rows with a value of '00164' in the EMPLOYEE_ID
SQL> -- column were deleted from the JOB_HISTORY table:
SQL> --
SQL> SELECT * FROM JOB_HISTORY JH WHERE JH.EMPLOYEE_ID = '00164';
0 rows selected
SQL> --
SQL> -- The rows with a value of '00164' in the EMPLOYEE_ID
SQL> -- column were deleted from the SALARY_HISTORY table:
SQL> --
SQL> SELECT * FROM SALARY_HISTORY SH WHERE SH.EMPLOYEE_ID = '00164';
0 rows selected
SQL> --
SQL> -- The value of '00164' in the MANAGER_ID column was set to null
SQL> -- in the DEPARTMENTS table:
SQL> --
SQL> SELECT * FROM DEPARTMENTS D WHERE D.DEPARTMENT_CODE = 'MBMN';
DEPARTMENT_CODE DEPARTMENT_NAME MANAGER_ID
BUDGET_PROJECTED BUDGET_ACTUAL
MBMN Board Manufacturing North NULL
NULL NULL
1 row selected
SQL> --
SQL> ROLLBACK;
Example 2: Defining a trigger that performs an update
Before the STATUS_CODE column in WORK_STATUS table is updated,
the STATUS_CODE_CASCADE_UPDATE trigger in the following SQL
procedure updates the associated rows in the EMPLOYEES table. The
REFERENCING clause specifies OLD_WORK_STATUS as the correlation
name for the values in the WORK_STATUS table before the UPDATE
statement executes, and NEW_WORK_STATUS as the correlation
name for the values in the WORK_STATUS table after the UPDATE
statement executes.
SQL> -- Display the STATUS_CODE_CASCADE_UPDATE trigger in
SQL> -- the sample database:
SQL> --
SQL> SHOW TRIGGER STATUS_CODE_CASCADE_UPDATE
STATUS_CODE_CASCADE_UPDATE
Source:
STATUS_CODE_CASCADE_UPDATE
BEFORE UPDATE OF STATUS_CODE ON WORK_STATUS
REFERENCING OLD AS OLD_WORK_STATUS
NEW AS NEW_WORK_STATUS
(UPDATE EMPLOYEES E
SET E.STATUS_CODE = NEW_WORK_STATUS.STATUS_CODE
WHERE E.STATUS_CODE = OLD_WORK_STATUS.STATUS_CODE)
FOR EACH ROW
SQL> --
SQL> -- Change the STATUS_CODE column with a value of 2 to a value of 3:
SQL> --
SQL> UPDATE WORK_STATUS WS SET STATUS_CODE="3" WHERE STATUS_CODE="2";
1 row updated
SQL> --
SQL> -- The trigger changes any STATUS_CODE column in the EMPLOYEES table
SQL> -- with a value of 2 to a value of 3. Therefore, no rows are
SQL> -- selected for the first query that follows, but several are selected
SQL> -- for the second query:
SQL> --
SQL> SELECT * FROM EMPLOYEES E WHERE E.STATUS_CODE = "2";
0 rows selected
SQL> --
SQL> SELECT * FROM EMPLOYEES E WHERE E.STATUS_CODE = "3";
EMPLOYEE_ID LAST_NAME FIRST_NAME MIDDLE_INITIAL
ADDRESS_DATA_1 ADDRESS_DATA_2 CITY
STATE POSTAL_CODE SEX BIRTHDAY STATUS_CODE
00165 Smith Terry D
120 Tenby Dr. Chocorua
NH 03817 M 15-May-1954 3
00178 Goldstone Neal NULL
194 Lyons Av, Colebrook
NH 03576 M 25-Apr-1952 3
.
.
.
00358 Lapointe Jo Ann C
70 Tenby Dr. Chocorua
NH 03817 F 24-Feb-1931 3
12 rows selected
SQL> --
SQL> ROLLBACK;
Example 3: Defining a trigger that updates a sales summary
The following example defines a trigger that updates a monthly
sales total after each daily sale is made.
SQL> --
SQL> -- Create the table to keep track of monthly sales:
SQL> CREATE TABLE MONTHLY_SALES
cont> ( SALES_AMOUNT INTEGER);
SQL> --
SQL> -- Create the table to keep track of sales made today:
SQL> CREATE TABLE DAILY_SALES
cont> ( SALES_AMOUNT INTEGER);
SQL> --
SQL> -- Assume that $250.00 of sales have been made during the current month:
SQL> INSERT INTO MONTHLY_SALES
cont> (SALES_AMOUNT) VALUES (250);
1 row inserted
SQL> --
SQL> -- After adding a new value to the SALES_AMOUNT column in
SQL> -- DAILY_SALES table, SQL updates the SALES column in
SQL> -- the MONTHLY_SALES table with the amount of the new sale:
SQL> CREATE TRIGGER UPDATE_SALES_TOTAL_ON_NEW_SALE
cont> AFTER INSERT ON DAILY_SALES
cont> (UPDATE MONTHLY_SALES M
cont> SET M.SALES_AMOUNT = M.SALES_AMOUNT + DAILY_SALES.SALES_AMOUNT)
cont> FOR EACH ROW;
SQL> --
SQL> -- The following statement records a new $5.00 sale for today:
SQL> INSERT INTO DAILY_SALES
cont> (SALES_AMOUNT) VALUES (5);
1 row inserted
SQL> --
SQL> -- The value for the SALES_AMOUNT column of the DAILY_SALES table
SQL> -- is $5.00 and the value of the SALES_AMOUNT column of the
SQL> -- MONTHLY_SALES table is $255.00:
SQL> SELECT * FROM DAILY_SALES;
SALES_AMOUNT
5
1 row selected
SQL> --
SQL> SELECT * FROM MONTHLY_SALES;
SALES_AMOUNT
255
1 row selected
SQL> --
SQL> -- When a new $9.00 sale is made, the values in the two rows of the
SQL> -- SALES_AMOUNT column of the DAILY_SALES table are $5.00 and $9.00
SQL> -- and the value of the SALES_AMOUNT column of the MONTHLY_SALES
SQL> -- table is $264.00:
SQL> INSERT INTO DAILY_SALES
cont> (SALES_AMOUNT) VALUES (9);
1 row inserted
SQL> --
SQL> SELECT * FROM DAILY_SALES;
SALES_AMOUNT
5
9
2 rows selected
SQL> --
SQL> SELECT * FROM MONTHLY_SALES;
SALES_AMOUNT
264
1 row selected
SQL> --
SQL> ROLLBACK;
SQL> --
Example 4: Defining a trigger that sets column values to null
Before the STATUS_CODE column in the WORK_STATUS table is
deleted, this trigger causes the associated WORK_STATUS columns
in the EMPLOYEES table to be set to null.
SQL> CREATE TRIGGER STATUS_CODE_ON_DELETE_SET_NULL
cont> BEFORE DELETE ON WORK_STATUS
cont> (UPDATE EMPLOYEES E SET E.STATUS_CODE = NULL
cont> WHERE E.STATUS_CODE = WORK_STATUS.STATUS_CODE)
cont> FOR EACH ROW;
SQL> --
SQL> -- Delete any row in the WORK_STATUS table where the STATUS_CODE
SQL> -- column has a value of 1:
SQL> DELETE FROM WORK_STATUS WS WHERE WS.STATUS_CODE = "1";
1 row deleted
SQL> --
SQL> -- This trigger sets the STATUS_CODE column value to null in many
SQL> -- rows in the EMPLOYEES table:
SQL> SELECT * FROM EMPLOYEES E WHERE E.STATUS_CODE IS NULL;
EMPLOYEE_ID LAST_NAME FIRST_NAME MIDDLE_INITIAL
ADDRESS_DATA_1 ADDRESS_DATA_2 CITY
STATE POSTAL_CODE SEX BIRTHDAY STATUS_CODE
00416 Ames Louie A
61 Broad st. NULL Alton
NH 03809 M 13-Apr-1941 NULL
00374 Andriola Leslie Q
111 Boston Post Rd. NULL Salisbury
NH 03268 M 19-Mar-1955 NULL
.
.
.
00200 Ziemke Al F
121 Putnam Hill Rd. NULL Winnisquam
NH 03289 M 27-Oct-1928 NULL
88 rows selected
SQL> ROLLBACK;
Example 5: Defining a trigger that prevents deletion of a row
that exists in two tables
Suppose that a user wants to delete only those rows in the JOB_
HISTORY table that do not also exist in the JOBS table. This is
difficult to do with constraints because a row can exist in one
table with a key number that does not exist in the other table.
The following statement creates a trigger that causes an error
when the user tries to delete a row that exists in table JOB_
HISTORY.
SQL> CREATE TRIGGER DELETE_GUARD
cont> BEFORE DELETE ON JOB_HISTORY
cont> WHEN EXISTS (SELECT JOBS.JOB_CODE FROM JOBS
cont> WHERE JOBS.JOB_CODE=JOB_HISTORY.JOB_CODE)
cont> (ERROR) FOR EACH ROW;
SQL> --
SQL> -- Now attempt a deletion that violates the trigger.
SQL> --
SQL> DELETE FROM JOB_HISTORY WHERE JOB_CODE = 'DMGR';
%RDB-E-TRIG_INV_UPD, invalid update; encountered error condition
defined for trigger
-RDMS-E-TRIG_ERROR, trigger DELETE_GUARD forced an error
-RDB-F-ON_DB, on database DISK1:[DEPT3.SQL]MF_PERSONNEL.RDB;1
Example 6: Defining a trigger that saves audit information
SQL> -- Create new table to record changes made to
SQL> -- EMPLOYEES table
SQL> CREATE TABLE AUDIT_TRAIL
cont> (LOG DATE VMS,
cont> PERSON CHAR(31),
cont> TBL_NAME CHAR(10),
cont> OPER CHAR(1));
SQL> COMMIT;
SQL> -- Create a trigger so that each time
SQL> -- an INSERT operation is performed,
SQL> -- a record is stored in the AUDIT_TRAIL table.
SQL> CREATE TRIGGER EMPS_TRIGGER
cont> AFTER INSERT
cont> ON EMPLOYEES
cont> (INSERT INTO AUDIT_TRAIL
cont> VALUES (CURRENT_TIMESTAMP,
cont> CURRENT_USER, 'EMPLOYEES', 'I'))
cont> FOR EACH STATEMENT;
SQL> -- The AUDIT_TRAIL table currently has no records.
SQL> SELECT * FROM AUDIT_TRAIL;
0 rows selected
SQL> -- Insert a record into EMPLOYEES
SQL> INSERT INTO EMPLOYEES
cont> (EMPLOYEE_ID, LAST_NAME)
cont> VALUES ('00964', 'FRENCH');
1 row inserted
SQL> -- See if trigger updated the AUDIT_TRAIL table.
SQL> SELECT * FROM AUDIT_TRAIL;
LOG PERSON TBL_NAME OPER
17-JUN-2003 15:04:31.43 STEWART EMPLOYEES I
1 row selected
Example 7: Using TRACE as a trigger action
SQL> set flags 'TRACE';
SQL> create table M_TABLE (a integer, b integer);
SQL>
SQL> create trigger T_A
cont> after insert on M_TABLE
cont> (trace 'in a trigger: ' || cast(M_TABLE.a as varchar(10)))
cont> for each row;
SQL>
SQL> insert into M_TABLE (a, b) values (1, 10);
~Xt: in a trigger: 1
1 row inserted
SQL>
Example 8: Using SIGNAL as a trigger action
SQL> create table M_TABLE (a integer, b integer);
SQL>
SQL> create trigger T_A
cont> after insert on M_TABLE2
cont> when (M_TABLE2.a is not null)
cont> (signal '12345' ('in a trigger: '
cont> || cast(M_TABLE2.a as varchar(10))))
cont> for each row;
SQL>
SQL> insert into M_TABLE2 (a, b) values (1, 10);
%RDB-E-SIGNAL_SQLSTATE, routine "T_A" signaled SQLSTATE "12345"
-RDB-I-TEXT, in a trigger: 1
SQL>
Example 9: Using CALL as a trigger action
SQL> create module M_MODULE
cont> language SQL
cont>
cont> procedure M_K (in :a int);
cont> trace 'called from a trigger: ' || cast(:a as varchar(10));
cont>
cont> end module;
SQL>
SQL> create table M_TABLE (a integer, b integer);
SQL>
SQL> create trigger T_A
cont> after insert on M_TABLE
cont> (call M_K (M_TABLE.a))
cont> for each row;
SQL>
SQL> insert into M_TABLE (a, b) values (1, 10);
~Xt: called from a trigger: 1
1 row inserted
SQL>