What is Trigger and Why we use It.

Ans:

A trigger is a special kind of Stored Procedure or stored program that is automatically fired or executed when some event (insert, delete and update) occurs.




1.WHAT IS MAGIC TABLE :

  ans:  The tables "INSERTED" and "DELETED" are called magic tables of the
        SQL Server. We can not see these tables in the data base. But we can access these
        tables from the "TRIGGER" 


     ** When we insert the record into the table, the magic table "INSERTED" will be created
        In that table the current inserted row will be available. We can access this

        record in the "TRIGGER". 


If you write a trigger for an insert operation on a table, after firing the trigger, 
it creates a table named “INSERTED” in memory. 
Then it does the insert operation and then the statements inside the trigger executes.

We can query the “INSERTED” table to manipulate or use the inserted row(s) from the trigger.


Why and when to use a trigger


We use a trigger when we want some event to happen automatically on certain desirable scenarios.


Types of Triggers

In SQL Server we can create the following 2 types of triggers:

1.  After Triggers (For Triggers)
2.   Instead Of Triggers

After trigger:

This trigger is fired after an INSERT on the table. Let’s create the trigger as:

CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test] 
FOR INSERT
AS
 declare @empid int;
 declare @empname varchar(100);
 declare @empsal decimal(10,2);
 declare @audit_action varchar(100);

 select @empid=i.Emp_ID from inserted i; 
 select @empname=i.Emp_Name from inserted i; 
 select @empsal=i.Emp_Sal from inserted i; 
 set @audit_action='Inserted Record -- After Insert Trigger.';

 insert into Employee_Test_Audit
           (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
 values(@empid,@empname,@empsal,@audit_action,getdate());

 PRINT 'AFTER INSERT trigger fired.'
GO

INSTEAD Of Triggers
It will tell the database engine to execute the trigger instead of executing the statement. For example an insert trigger executes when an event occurs instead of the statement that would insert the values in the table . 

1. CREATE TRIGGER instoftr  
2. ON v11  
3. INSTEAD OF INSERT  
4. AS  
5. BEGIN  
6. INSERT INTO emp  
7. SELECT I.id, I.names  
8. FROM INSERTED I  
9.    
10. INSERT INTO emp1values  
11. SELECT I.id1, I.name1  
12. FROM INSERTED I  
13. END   

Difference between Trigger and Stored Procedure:

Note: One instead of trigger and any after triggers possible per table.


we can create instead of trigger in view and after trigger create in only one table.