| 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 an 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] ' OLD VALUES CORRELATION NAME NEW [ROW] ' NEW VALUES CORRELATION NAME OLD TABLE ' OLD VALUES TABLE ALIAS NEW TABLE ' 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. The preceding 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 body of a trigger cannot be altered . You must either replace or re-create the trigger. Some implementations allow a trigger to be replaced (if the trigger with the same name already exists) as part of the CREATE TRIGGER statement. | 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 |