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 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.

A trigger can be created using the CREATE TRIGGER statement.

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 statemens
[ 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(MySql):

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;

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.

Note:
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

Source>>http://www.samspublishing.com/library/content.asp?b=STY_Sql_24hours&seqNum=184&rl=1