保存对表的删除更新数据
SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM sys.OBJECTs WHERE [object_id] = OBJECT_ID(N'tri_host_db')AND [type] = N'TR')
DROP TRIGGER tri_host_db
GO
CREATE TRIGGER tri_host_db
ON dbo.dc_host_db
FOR DELETE,UPDATE
AS
BEGIN
DECLARE @infoMsg NVARCHAR(MAX),
@sql1 NVARCHAR(MAX),
@sql2 NVARCHAR(MAX),
@sql3 NVARCHAR(MAX)
SELECT * INTO #inserted FROM INSERTED
SELECT * INTO #deleted FROM DELETED
IF OBJECT_ID('tempdb..#oldValue') IS NOT NULL
BEGIN
DROP TABLE #oldValue
END
IF OBJECT_ID('tempdb..#newValue') IS NOT NULL
BEGIN
DROP TABLE #oldValue
END
CREATE TABLE #oldValue(v NVARCHAR(MAX))---保存每个字段对应的数据
CREATE TABLE #newValue(v NVARCHAR(MAX))
DECLARE @i INT,
@iMax INT,
@colName NVARCHAR(500),
@update BIT
DECLARE @t TABLE (colId INT, colName NVARCHAR(MAX))
---把dbo.dc_host_db表的所有字段保存到@t中
INSERT INTO @t
SELECT CAST(c.column_id AS INT),c.name FROM sys.[columns] c
WHERE c.[object_id] = OBJECT_ID('dbo.dc_host_db')
---求@t的列数，即dbo.dc_host_db表的字段数
SELECT @i = 1,@iMax = COUNT(1) FROM @t
IF NOT EXISTS (SELECT 1 FROM #inserted)
SET @infoMsg = '操作类型:删除'
IF EXISTS(SELECT 1 FROM #inserted t1
WHERE EXISTS(SELECT 1 FROM #deleted t2 WHERE t1.dbId = t2.dbId)
)
SET @infoMsg = '操作类型:更新'
---设置判断结果是否为空
SET @update = 0
WHILE @i <= @iMax
BEGIN
SELECT @colName = t.colName FROM @t t WHERE t.colId = @i
---删除记录时，保存被删除的记录
IF NOT EXISTS (SELECT 1 FROM #inserted)
BEGIN
SET @update = 1
SET @sql3 = 'INSERT INTO #oldValue SELECT '+@colName+' FROM #deleted'
EXEC (@sql3)
BEGIN
SELECT @infoMsg=@infoMsg+@colName+':'+ov.v+';' FROM #oldValue ov
END
TRUNCATE TABLE #oldValue
END
---更新记录时，保存更新前后的记录
IF EXISTS(SELECT 1 FROM #inserted t1
WHERE EXISTS(SELECT 1 FROM #deleted t2 WHERE t1.dbId=t2.dbId)
)
BEGIN
SET @sql1='INSERT INTO #oldValue SELECT '+@colName+' FROM #deleted'
EXEC (@sql1)
SET @sql2='INSERT INTO #newValue SELECT '+@colName+' FROM #inserted'
EXEC (@sql2)
IF EXISTS (SELECT v FROM #newValue nv
WHERE EXISTS(SELECT v FROM #oldValue ov WHERE nv.v<>ov.v)
)
BEGIN
SELECT @infoMsg=@infoMsg+@colName+':'+ov.v+'=>'+nv.v+'; 'FROM #oldValue ov,#newValue nv
SET @update=1
END
TRUNCATE TABLE #oldValue
TRUNCATE TABLE #newValue
END
SET @i = @i + 1
END
--结果非空，保存结果
IF @update = 1
INSERT INTO dbo.dc_log (dbId,logInfo) SELECT dbId,@infoMsg FROM #deleted
END
GO
