What is Trigger ? After Trigger & Instead of Triggers with Examples

 

• A trigger is a SQL procedure that initiates an action when an event like Insert, Delete or Update occurs.
• Triggers are stored and managed by DBMS.
• Triggers are used to maintain data Integrity.
• Triggers cannot be called or executed , the DBMS automatically calls the trigger as a result of data modification to table.
• A trigger that contains data modification logic within itself is called a nested trigger.
• There are two types of Triggers : After Triggers and Instead of Triggers 

After Triggers :

• It runs after Insert, Delete or Update on a table. 
• No support for View.
• After Triggers are Further Classified as :

1. AFTER INSERT Trigger.  
2. AFTER UPDATE Trigger. 
3. AFTER DELETE Trigger. 

Example of AFTER INSERT Trigger.

CREATE TRIGGER mytrigger ON Table1

FOR INSERT

AS

       --declaring local variables

       declare @empid int;

       declare @empname varchar (150);

       --storing values in above declared variables

       select @empid=i.Emp_ID from inserted i;

       select @empname=i.Emp_Name from inserted i;      

       --inserting record in new table

       insert into Table2 (Emp_ID,Emp_Name) values (@empid,@empname);

GO