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
