建一个跟源表一样的表,记录源表所有操作信息。
ALTER trigger [dbo].[test_Trg] on [dbo].[test]
for insert,update,delete
AS
If not exists(Select 1 from deleted)
INSERT INTO test_Trg SELECT *, 'INSERT', getdate(), substring(SUSER_NAME(),1,50) FROM inserted;
If exists(select 1 from inserted) and exists(select 1 from deleted)
INSERT INTO test_Trg SELECT *, 'UPDATE', getdate(),substring(SUSER_NAME(),1,50) FROM inserted;
If not exists(select 1 from inserted)
INSERT INTO test_Trg SELECT *, 'DELETE', getdate(), substring(SUSER_NAME(),1,50) FROM deleted;