Sql Server Trigger

 

1. CREATE TRIGGER triggerName ON table   
2. AFTER INSERT |After Delete |After Upadte  
3. AS BEGIN  
4.   
5. INSERT INTO dbo.UserHistory............  
6.   
7. END   

Example

 

1. Create a Table:

1. CREATE TABLE Employee_Test  
2. (  
3. Emp_ID INT Identity,  
4. Emp_name Varchar(100),  
5. Emp_Sal Decimal (10,2)  
6. )  

2. Insert records into it:

1. INSERT INTO Employee_Test VALUES ('Anees',1000);  
2. INSERT INTO Employee_Test VALUES ('Rick',1200);  
3. INSERT INTO Employee_Test VALUES ('John',1100);  
4. INSERT INTO Employee_Test VALUES ('Stephen',1300);  
5. INSERT INTO Employee_Test VALUES ('Maria',1400);  

3. Create another table to store transaction records, like records of insert, delete and update on the Employee_Test Table:

1. CREATE TABLE Employee_Test_Audit  
2. (  
3. Emp_ID int,  
4. Emp_name varchar(100),  
5. Emp_Sal decimal (10,2),  
6. Audit_Action varchar(100),  
7. Audit_Timestamp datetime  
8. )  

create trigger AFTER_INSERT_DELETE 

on [dbo].[employee_test] 

after insert ,delete  

as

begin

declare @audit_action varchar(50);

set @audit_action='Inserted iteam';

insert into [dbo].[employee_test_audit]

(emp_id,emp_name,emp_sal,audit_action,audit_timestamp)

 select i.emp_id,i.emp_name,i.emp_sal,@audit_action,getdate() from inserted i;

set @audit_action='Deleted iteam';

insert into [dbo].[employee_test_audit]

select d.emp_id,d.emp_name,d.emp_sal,@audit_action,getdate() from deleted d;

end

AFTER UPDATE 

alter trigger update_salary  on [dbo].[employee_test]

after update ,insert,delete

as

begin

declare @empname varchar(25), @newsalary int,@audit_action varchar(40);

select @empname=i.emp_name from inserted i;

select @newsalary=i.emp_sal from inserted i;

if update(emp_name)

begin

set @audit_action='N_update';

end

if update(emp_sal)

begin

set @audit_action='S_update';

end

insert into [dbo].[employee_test_audit] (emp_id,emp_name,emp_sal,audit_action,audit_timestamp)

select i.emp_id,@empname,@newsalary,@audit_action,getdate() from inserted i;

end