比较SQL Server 2008中已删除和已插入的表 [英] Compare deleted and inserted table in SQL Server 2008
问题描述
我是SQL Server 2008的新手,我需要所有人的建议。我想在一个表的SQL Server 2008的插入的
和
删除的
的表中查找更改的值,因为当前正在进行审计跟踪以保留旧的和新的价值。如何循环所有列以从已删除和已插入的表中找出哪个字段的值发生了变化?我已经尝试过if else语句进行比较
例如:
在
开始
后更新
后在dbo.table
上创建触发器trg_XXX
声明
@oldID varchar(6),
@newID varchar(6)
select @oldID =已删除的
select @newID =已插入的
if(@oldID!= @newID)
插入到表(a,b)中的值(@oldID,@newID)
否则
print('do no'')
有没有一种方法可以使用游标遍历已删除和插入的表,或者可以使用其他任何方法?可以举个例子吗?
我不太确定,您的目标是什么,我认为这可能类似于这个。
假设我们有一个这样的表:
创建表产品
(
ID INT PRIMARY KEY,
名称NVARCHAR(100)NOT NULL,
价格DECIMAL(10,2)NOT NULL
);
和一些审计表,如下所示:
创建表ProductAudit
(
AuditID INT IDENTITY PRIMARY KEY,
ProductID INT NOT NULL
OldName NVARCHAR(100)NULL,
OldPrice DECIMAL(10,2)NULL,
NewName NVARCHAR(100)NULL,
NewPrice DECIMAL(10,2)NULL
);
然后创建触发器:
在产品
上创建触发器TR_AUDIT
,以便插入,更新,删除
作为
开始
插入ProductAudit(ProductID,OldName, OldPrice,NewName,NewPrice)
选择
COALESCE(I.ID,D.ID),
D.Name,
D.Price,
I.Name,
I.Price
从
插入我在I.ID = D.ID上删除的完整外部联接;
END
GO
有。
I am new to SQL Server 2008 and I need advice from all of you. I want to find out the changed value in inserted
and deleted
tables of the SQL Server 2008 for a table because I am currently doing the audit trail to keep the old and new value. How can I loop all the column to find out which field's value change from the deleted and inserted table? I had tried the if else statement to compare
For example:
create trigger trg_XXX on dbo.table
after update
as
begin
declare
@oldID varchar(6),
@newID varchar(6)
select @oldID = ID from deleted
select @newID = ID from inserted
if(@oldID != @newID)
insert into table (a, b) values (@oldID, @newID)
else
print('do nothing')
Is there a way to do using cursor to loop through the deleted and inserted table or any alternative way? Can give me some example?
I'm not quite sure, what your goal ist, i think it might be something like this. Let's say we have a table like this:
CREATE TABLE Product
(
ID INT PRIMARY KEY,
Name NVARCHAR(100) NOT NULL,
Price DECIMAL(10,2) NOT NULL
);
and some audit table like this:
CREATE TABLE ProductAudit
(
AuditID INT IDENTITY PRIMARY KEY,
ProductID INT NOT NULL
OldName NVARCHAR(100) NULL,
OldPrice DECIMAL(10,2) NULL,
NewName NVARCHAR(100) NULL,
NewPrice DECIMAL(10,2) NULL
);
Then you create a trigger:
CREATE TRIGGER TR_AUDIT
ON Product
FOR INSERT, UPDATE, DELETE
AS
BEGIN
INSERT INTO ProductAudit (ProductID, OldName, OldPrice, NewName, NewPrice)
SELECT
COALESCE(I.ID, D.ID),
D.Name,
D.Price,
I.Name,
I.Price
FROM
INSERTED I FULL OUTER JOIN DELETED D ON I.ID = D.ID;
END
GO
There you have it.
这篇关于比较SQL Server 2008中已删除和已插入的表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!