or
Trigger is a type of stored procedure that implicitly executed when user performs DML operation on the table. It will not accept any parameters.
Types of Triggers:
1. After Trigger: These are the triggers,which performs their own operation after performing insert,delete,and update operations on a specific table.
Syntax:
CREATE TRIGGER TRIGGERNAME ON TABLENAME FOR/AFTER {INSERT/UPDATE/DELETE}
AS
BEGIN
SQL STATEMENT
END
A)INSERT TRIGGER: This trigger fires when user performs insert operation on the table.When user insert a record into the table the temporary table called inserted is created the newly inserted record is also stored table temporarily.
B)DELETE TRIGGER: This trigger fires when user performs delete operation on the table.When user delete a record from the table the temporary table called deleted is created the deleted record is also stored in deleted table temporarily.
C)UPDATE TRIGGER: This trigger fires when user performs update operation on the table.When user update a record into the table the temporary tables called inserted and deleted are created the new values placed into inserted table and old values will be placed in deleted table temporarily.
Example:
CREATE TRIGGER T1 ON DEPT FOR INSERT
AS
BEGIN
INSERT INTO DEPT1 SELECT * FROM INSERTED
END
The above triggers fires after performing INSERT operation on DEPT table. It will inserts the newly inserted records into DEPT1.
2)Instead of Triggers:These are the triggers,which performs their operations instead of performing user specified operations.
Syntax: CREATE TRIGGER TRIGGERNAME ON TABLE NAME INSTEAD OF {INSERT/UPDATE/DELETE}
AS
BEGIN
SQL STATEMENT
END
Example:CREATE TRIGGER T4 ON DEPT INSTEAD OF INSERT,UPDATE,DELETE
AS
BEGIN
PRINT 'THESE OPERATIONS ARE NOT ALLOWED'
END
The above trigger fires automatically and shows a message THESE OPERATIONS ARE NOT ALLOWED, when user try to perform INSERT,UPDATE,DELETE operations on DEPT table.
