April 9, 2009 at 6:14 am
Hi All
I am using MS SQL, Sqlserver 2005 with C# code.
I have two tables (table1 and table2).
I need to setup a trigger on table1, so when record - Insert, Update and Delete is done it will then write the key of table1 to table2.
Can any one help?
April 9, 2009 at 8:02 am
CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)
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);
CREATE TABLE Employee_Test_Audit
(
Emp_ID int,
Emp_name varchar(100),
Emp_Sal decimal (10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime
)
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
April 9, 2009 at 9:23 am
I wouldn't recommend the code above. If you have multiple updates, this won't help.
What you really want is an
insert table2 select PK from inserted
in your trigger to move all the changed data. the other thing is that you can check the deleted table as well, compare it with inserted for UPDATE changes and things that are only in deleted are deletes.
If you use separate triggers, this is easy to handle.
April 9, 2009 at 10:19 am
Further information here:
http://www.sqlservercentral.com/Forums/Topic693851-149-1.aspx
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply