TRIGGER WITH EX:

If I Inset Any Value In the Table it will Be Automatically Push to Another Table ,So Use This bellow steps…

 

CREATE TABLE [dbo].[Employee_Test](

      [Emp_ID] [int] IDENTITY(1,1) NOT NULL,

      [Emp_name] [varchar](100) NULL,

      [Emp_Sal] [decimal](10, 2) NULL

) ON [PRIMARY]

 

GO

 

INSERT A TABLE VALUE

 

INSERT INTO Employee_Test VALUES ('Anees',1000);

INSERT INTO Employee_Test VALUES ('Rick',1200);

INSERT INTO Employee_Test VALUES ('John',1100);

INSERT INTO Employee_Test VALUES ('Stephen',1300);

INSERT INTO Employee_Test VALUES ('Maria',1400);

 

 

Which Table U Want to Insert the content so Create a Table..

 

CREATE TABLE [dbo].[Employee_Test_Audit](

      [Emp_ID] [int] NULL,

      [Emp_name] [varchar](100) NULL,

      [Emp_Sal] [decimal](10, 2) NULL,

      [Audit_Action] [varchar](100) NULL,

      [Audit_Timestamp] [datetime] NULL

) ON [PRIMARY]

 

GO

 

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

 

 

 

AFTER UPDATE Trigger

This trigger is fired after an update on the table. Let's create the trigger as:-

 

CREATE TRIGGER trgAfterUpdate ON [dbo].[Employee_Test]

FOR UPDATE

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;

     

      if update(Emp_Name)

            set @audit_action='Updated Record -- After Update Trigger.';

      if update(Emp_Sal)

            set @audit_action='Updated Record -- After Update 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 UPDATE Trigger fired.'

GO

 

UPDATE THE TABLE:

 

 update Employee_Test set Emp_Sal=1550 where Emp_ID=6

  

THEN SELECT THE OUR REFERANCE TABLE:

 

 select * from Employee_Test_Audit

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

AFTER DELETE Trigger

This trigger is fired after a delete on the table. Let's create the trigger as:-

 

 

CREATE TRIGGER trgAfterDelete ON [dbo].[Employee_Test]

AFTER DELETE

AS

      declare @empid int;

      declare @empname varchar(100);

      declare @empsal decimal(10,2);

      declare @audit_action varchar(100);

 

      select @empid=d.Emp_ID from deleted d;   

      select @empname=d.Emp_Name from deleted d;     

      select @empsal=d.Emp_Sal from deleted d; 

      set @audit_action='Deleted -- After Delete 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 DELETE TRIGGER fired.'

GO

 

DELETE THE SELECTED ROW

 

delete from [Employee_Test] where [Emp_ID]=1

 

 select * from Employee_Test_Audit

 

--
--

Regards,

Basheer Ahamed