作者lisyu (游小虾)
看板Database
标题[SQL ] 用Trigger纪录LOG的问题
时间Fri Nov 8 18:47:19 2013
目前的Trigger写法如下
CREATE TRIGGER [tri_TABLE1_UPD] ON [TABLE1] AFTER UPDATE
AS
BEGIN
DECLARE @record XML
DECLARE @IsType TINYINT
SET @IsType=''
IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted) AND
(SELECT COUNT(*) FROM inserted
WHERE (LEFT(Field1,1)='Y'))>0
SET @IsType = 2 --Update
IF (@IsType <>'')
BEGIN
SET @record=(SELECT * FROM inserted FOR XML RAW('Customers'),
ELEMENTS,ROOT)
INSERT INTO [LOG]([dbname],[recoder],[istype])
VALUES('Customers',@record ,1)
SET @record=(SELECT * FROM deleted FOR XML RAW('Customers'),
ELEMENTS,ROOT)
INSERT INTO [LOG]([dbname],[recoder],[istype])
VALUES('Customers',@record ,3)
END
END;
不过目前纪录的只有资料库的内容变化
请问如果要记录是谁修改的例如IP或来自哪台电脑改的
有办法抓到相关资料吗?
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 218.210.209.2
1F：→ bohei:可以找找V$SESSION 看有没有你要的资料XD 11/08 21:51