Triggers

A trigger is a compiled SQL procedure in the database used to perform actions based on other actions that occur within the database. A trigger is a form of a stored procedure that is executed when a specified (Data Manipulation Language) action is performed on a table. The trigger can be executed before or after an INSERT, DELETE, or UPDATE. Triggers can also be used to check data integrity before and INSERT, DELETE, or UPDATE. Triggers can roll back transactions, and they can modify data in one table and read from another table in another database.

Triggers, for the most part, are very good functions to use; they can, however, cause more I/O overhead. Triggers should not be used when a stored procedure or a program can accomplish the same results with less overhead.

The CREATE TRIGGER Statement

A trigger can be created using the CREATE TRIGGER statement.

The ANSI standard syntax is:

CREATE TRIGGER TRIGGER NAME
[[BEFORE | AFTER] TRIGGER EVENT ON TABLE NAME]
[REFERENCING VALUES ALIAS LIST]
[TRIGGERED ACTION
TRIGGER EVENT::=
INSERT | UPDATE | DELETE [OF TRIGGER COLUMN LIST]
TRIGGER COLUMN LIST ::= COLUMN NAME [,COLUMN NAME]
VALUES ALIAS LIST ::=
VALUES ALIAS LIST ::=
OLD [ROW] [AS] OLD VALUES CORRELATION NAME |
NEW [ROW] [AS] NEW VALUES CORRELATION NAME |
OLD TABLE [AS] OLD VALUES TABLE ALIAS |
NEW TABLE [AS] NEW VALUES TABLE ALIAS
OLD VALUES TABLE ALIAS ::= IDENTIFIER
NEW VALUES TABLE ALIAS ::= IDENTIFIER
TRIGGERED ACTION ::=
[FOR EACH [ROW | STATEMENT] [WHEN SEARCH CONDITION]]
TRIGGERED SQL STATEMENT
TRIGGERED SQL STATEMENT ::=
SQL STATEMENT | BEGIN ATOMIC [SQL STATEMENT;]
END

The Microsoft SQL Server syntax to create a trigger is as follows:

CREATE TRIGGER TRIGGER_NAME
ON TABLE_NAME
FOR { INSERT | UPDATE | DELETE [, ..]}
AS
SQL_STATEMENTS
[ RETURN ]

The basic syntax for Oracle is as follows:

CREATE [ OR REPLACE ] TRIGGER TRIGGER_NAME
[ BEFORE | AFTER]
[ DELETE | INSERT | UPDATE]
ON [ USER.TABLE_NAME ]
[ FOR EACH ROW ]
[ WHEN CONDITION ]
[ PL/SQL BLOCK ]

The following is an example trigger:

							CREATE TRIGGER EMP_PAY_TRIG
							AFTER UPDATE ON EMPLOYEE_PAY_TBL
							FOR EACH ROW
							BEGIN
							INSERT INTO EMPLOYEE_PAY_HISTORY
							(EMP_ID, PREV_PAY_RATE, PAY_RATE, DATE_LAST_RAISE,
							TRANSACTION_TYPE)
							VALUES
							(:NEW.EMP_ID, :OLD.PAY_RATE, :NEW.PAY_RATE,
							:NEW.DATE_LAST_RAISE, 'PAY CHANGE'),
							END;
							/
						

Trigger created.

This example shows the creation of a trigger called EMP_PAY_TRIG. This trigger inserts a row into the EMPLOYEE_PAY_HISTORY table, reflecting the changes made every time a row of data is updated in the EMPLOYEE_PAY_TBL table.

The DROP TRIGGER Statement

A trigger can be dropped using the DROP TRIGGER statement. The syntax for dropping a trigger is as follows:

DROP TRIGGER TRIGGER_NAME