下面代码提供了大概的思路,但关键问题还没解决,大家帮忙改一下啊.CREATE TRIGGER PLAN_LOG_Trig ON HR_PLAN_DATA FOR UPDATE AS BEGIN DECLARE @col SYSNAME DECLARE @NO VARCHAR(20) DECLARE @InputTime DATETIME SET @InputTime = GETDATE() SELECT @NO = NO FROM Deleted SELECT * INTO #TB1 FROM Deleted
SELECT * INTO #TB2 FROM Inserted
DECLARE p_Cursor_Log CURSOR SCROLL FOR SELECT name FROM SYSCOLUMNS WHERE id = OBJECT_ID('HR_PLAN_DATA')
--打开游标 OPEN p_Cursor_Log
--循环读取 FETCH FIRST FROM p_Cursor_Log INTO @col WHILE @@FETCH_STATUS = 0 BEGIN IF (SELECT @col FROM TB1) <> (SELECT @col FROM TB2) BEGIN
INSERT INTO HR_PLAN_LOGtest (NO, TableName, FieldName, PriorContent, NewContent, OpType, InputTime, InputChName) VALUES (@NO, 'HR_PLAN_DATA', @col, (SELECT @col FROM TB1), (SELECT @col FROM TB2), 1, @InputTime) END END --关闭游标 CLOSE p_Cursor_Log DEALLOCATE p_Cursor_Log END
下面代码提供了大概的思路,但关键问题还没解决,大家帮忙改一下啊.CREATE TRIGGER PLAN_LOG_Trig ON HR_PLAN_DATA FOR UPDATE AS BEGIN DECLARE @col SYSNAME DECLARE @NO VARCHAR(20) DECLARE @InputTime DATETIME SET @InputTime = GETDATE() SELECT @NO = NO FROM Deleted SELECT * INTO #TB1 FROM Deleted
SELECT * INTO #TB2 FROM Inserted
DECLARE p_Cursor_Log CURSOR SCROLL FOR SELECT name FROM SYSCOLUMNS WHERE id = OBJECT_ID('HR_PLAN_DATA')
--打开游标 OPEN p_Cursor_Log
--循环读取 FETCH FIRST FROM p_Cursor_Log INTO @col WHILE @@FETCH_STATUS = 0 BEGIN IF (SELECT @col FROM TB1) <> (SELECT @col FROM TB2) BEGIN
INSERT INTO HR_PLAN_LOGtest (NO, TableName, FieldName, PriorContent, NewContent, OpType, InputTime, InputChName) VALUES (@NO, 'HR_PLAN_DATA', @col, (SELECT @col FROM TB1), (SELECT @col FROM TB2), 1, @InputTime) END FETCH FIRST FROM p_Cursor_Log INTO @col &&新加的 END --关闭游标 CLOSE p_Cursor_Log DEALLOCATE p_Cursor_Log END
CREATE TRIGGER PLAN_LOG_Trig ON HR_PLAN_DATA FOR UPDATE AS BEGIN DECLARE @col SYSNAME DECLARE @NO VARCHAR(20) DECLARE @InputTime DATETIME SET @InputTime = GETDATE() SELECT @NO = NO FROM Deleted SELECT * INTO #TB1 FROM Deleted
SELECT * INTO #TB2 FROM Inserted
DECLARE p_Cursor_Log CURSOR SCROLL FOR SELECT name FROM SYSCOLUMNS WHERE id = OBJECT_ID('HR_PLAN_DATA')
--打开游标 OPEN p_Cursor_Log
--循环读取 FETCH FIRST FROM p_Cursor_Log INTO @col WHILE @@FETCH_STATUS = 0 BEGIN IF (SELECT @col FROM TB1) <> (SELECT @col FROM TB2) BEGIN
INSERT INTO HR_PLAN_LOGtest (NO, TableName, FieldName, PriorContent, NewContent, OpType, InputTime, InputChName) VALUES (@NO, 'HR_PLAN_DATA', @col, (SELECT @col FROM TB1), (SELECT @col FROM TB2), 1, @InputTime,'姓名*') &&少了姓名 END FETCH FIRST FROM p_Cursor_Log INTO @col &&新加的 END --关闭游标 CLOSE p_Cursor_Log DEALLOCATE p_Cursor_Log END
比如说有一个update触发了,我怎样获得被修改字段名,修改前和修改后的内容?修改前内容 PriorContent
修改后内容 NewContent
FOR UPDATE
AS
BEGIN
DECLARE @col SYSNAME
DECLARE @NO VARCHAR(20)
DECLARE @InputTime DATETIME
SET @InputTime = GETDATE() SELECT @NO = NO FROM Deleted
SELECT * INTO #TB1
FROM Deleted
SELECT * INTO #TB2
FROM Inserted
DECLARE p_Cursor_Log CURSOR SCROLL FOR
SELECT name FROM SYSCOLUMNS
WHERE id = OBJECT_ID('HR_PLAN_DATA')
--打开游标
OPEN p_Cursor_Log
--循环读取
FETCH FIRST FROM p_Cursor_Log INTO @col
WHILE @@FETCH_STATUS = 0
BEGIN
IF (SELECT @col FROM TB1) <> (SELECT @col FROM TB2)
BEGIN
INSERT INTO HR_PLAN_LOGtest
(NO, TableName, FieldName, PriorContent, NewContent, OpType, InputTime, InputChName)
VALUES (@NO, 'HR_PLAN_DATA', @col, (SELECT @col FROM TB1), (SELECT @col FROM TB2), 1, @InputTime)
END
END
--关闭游标
CLOSE p_Cursor_Log
DEALLOCATE p_Cursor_Log
END
FOR UPDATE
AS
BEGIN
DECLARE @col SYSNAME
DECLARE @NO VARCHAR(20)
DECLARE @InputTime DATETIME
SET @InputTime = GETDATE() SELECT @NO = NO FROM Deleted
SELECT * INTO #TB1
FROM Deleted
SELECT * INTO #TB2
FROM Inserted
DECLARE p_Cursor_Log CURSOR SCROLL FOR
SELECT name FROM SYSCOLUMNS
WHERE id = OBJECT_ID('HR_PLAN_DATA')
--打开游标
OPEN p_Cursor_Log
--循环读取
FETCH FIRST FROM p_Cursor_Log INTO @col
WHILE @@FETCH_STATUS = 0
BEGIN
IF (SELECT @col FROM TB1) <> (SELECT @col FROM TB2)
BEGIN
INSERT INTO HR_PLAN_LOGtest
(NO, TableName, FieldName, PriorContent, NewContent, OpType, InputTime, InputChName)
VALUES (@NO, 'HR_PLAN_DATA', @col, (SELECT @col FROM TB1), (SELECT @col FROM TB2), 1, @InputTime)
END
FETCH FIRST FROM p_Cursor_Log INTO @col &&新加的
END
--关闭游标
CLOSE p_Cursor_Log
DEALLOCATE p_Cursor_Log
END
FOR UPDATE
AS
BEGIN
DECLARE @col SYSNAME
DECLARE @NO VARCHAR(20)
DECLARE @InputTime DATETIME
SET @InputTime = GETDATE() SELECT @NO = NO FROM Deleted
SELECT * INTO #TB1
FROM Deleted
SELECT * INTO #TB2
FROM Inserted
DECLARE p_Cursor_Log CURSOR SCROLL FOR
SELECT name FROM SYSCOLUMNS
WHERE id = OBJECT_ID('HR_PLAN_DATA')
--打开游标
OPEN p_Cursor_Log
--循环读取
FETCH FIRST FROM p_Cursor_Log INTO @col
WHILE @@FETCH_STATUS = 0
BEGIN
IF (SELECT @col FROM TB1) <> (SELECT @col FROM TB2)
BEGIN
INSERT INTO HR_PLAN_LOGtest
(NO, TableName, FieldName, PriorContent, NewContent, OpType, InputTime, InputChName)
VALUES (@NO, 'HR_PLAN_DATA', @col, (SELECT @col FROM TB1), (SELECT @col FROM TB2), 1, @InputTime,'姓名*') &&少了姓名
END
FETCH FIRST FROM p_Cursor_Log INTO @col &&新加的
END
--关闭游标
CLOSE p_Cursor_Log
DEALLOCATE p_Cursor_Log
END