June 7, 2012 at 8:10 pm
I have a script like this.
"SELECT * FROM absent_new.dbo.absent INTO absent.dbo.absent"
The aim: how to make every happened a process (either insert / update / delete)
table ABSENT in the database ABSENT, it autoforward directly to table absent in database ABSENT_NEW
June 7, 2012 at 11:59 pm
Here is one example, you could modify this script accordingly.
Create TRIGGER [dbo].[Tr_Test_Table_Audit]
ON [dbo].[Test_table] FOR INSERT, UPDATE, DELETE NOT FOR REPLICATION
AS
-- SET NOCOUNT ON added to prevent extra result sets from query execution
SET NOCOUNT ON;
-- Determine if this is an INSERT,UPDATE, or DELETE Action
DECLARE @operation as Varchar(10)
DECLARE @Count as int
SET @operation = 'Inserted' -- Setting operation to 'Inserted'
SELECT @Count = COUNT(*) FROM DELETED
if @Count > 0
BEGIN
SET @operation = 'Deleted' -- Set Operation to 'Deleted'
SELECT @Count = COUNT(*) FROM INSERTED
IF @Count > 0
SET @operation = 'Updated' -- Set Operation to 'Updated'
END
-- Capturing Delete Operation
if @operation = 'Deleted'
BEGIN
Insert into Test_table_Audit(Effective_date,Operation,Id,Name,phonenumber)
SELECT GETDATE(),'Deleted',ID,Name,phonenumber from deleted
END
ELSE
BEGIN
-- trigger treats insert and update as same, so we can make it clear here
SELECT GETDATE(),'inserted',ID,Name,phonenumber from inserted
--Capturing Insert Operation
if @operation = 'Inserted'
BEGIN
Insert into Test_table_Audit(Effective_date,Operation,Id,Name,phonenumber)
SELECT GETDATE(),'inserted',ID,Name,phonenumber from inserted
END
-- Capture Update Operation
ELSE
BEGIN
INSERT INTO Test_table_Audit(Effective_date,Operation,Id,Name,phonenumber)
SELECT GETDATE(),'Updated',ID,Name,phonenumber from inserted
END
END
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply