Content
What is Trigger?
Type of Trigger
After Trigger
Instead of Trigger
I. What is a Trigger
- A trigger is a special kind of a store procedure that executes in response to certain action on the table like inserting, deleting or updating of data.
- It is a database object which is bound to a table and is executed automatically.
- The only way to do this is by performing the required action no the table that they are assigned to.
II. Types Of Triggers
- There are three action query types that you use in SQL which are INSERT, UPDATE and DELETE. So, there are three types of triggers and hybrids that come from mixing and matching the events and timings that fire them.
- Basically, triggers are classified into two main types:
- After Triggers
- Instead Of Triggers
a. After Triggers (using FOR/AFTER CLAUSE)
- This trigger fires after SQL Server completes the execution of the action successfully that fired it.
- These triggers run after an insert, update or delete on a table. They are not supported for views.
- Example :If you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the After Trigger.
After Insert Trigger
This trigger is fired after an INSERT on the table.
Syntax
CREATE TRIGGER Tr_Name ON TABLE_NAME
FOR INSERT AS
SQL STATEMENT
After Update Trigger
This trigger is fired after an update on the table.
Syntax
CREATE TRIGGER Tr_Name ON TABLE_NAME
FOR UPDATE AS
SQL STATEMENT
AFTER DELETE Trigger
This trigger is fired after a delete on the table.
Syntax
CREATE TRIGGER Tr_Name ON TABLE_NAME
FOR DELETE AS
SQL STATEMENT
III. Instead Of Triggers (using INSTEAD OF CLAUSE)
- This trigger fires before SQL Server starts the execution of the action that fired it. This is much more different from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table.
- Example :If you insert record/row in a table then the trigger associated with the insert event on this table will fire before the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.
Instead of Insert Trigger
Syntax
CREATE TRIGGER Tr_Name ON TABLE_NAME
INSTEAD OF INSERT AS
SQL STATEMENT
Instead of Update Trigger
Syntax
CREATE TRIGGER Tr_Name ON TABLE_NAME
INSTEAD OF UPDATE AS
SQL STATEMENT
Instead of Delete Trigger
Syntax
CREATE TRIGGER Tr_Name ON TABLE_NAME
INSTEAD OF DELETE AS
SQL STATEMENT
