SQL 触发器插入旧值 - 更新的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3181305/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Trigger insert old values- values that was updated
提问by user383875
I need to create trigger in SQL Server 2008 that gone insert all values from one row in which some value was changed into Log table!
我需要在 SQL Server 2008 中创建触发器，该触发器从一行中插入所有值，其中某些值已更改为日志表！
For example if i have table Employees that have column id, name , password, and i update this table and insert new value for column name, than i need to insert values that was in table Employees after update in table Log.
例如，如果我有包含列 ID、名称、密码的员工表，并且我更新此表并为列名插入新值，那么我需要在更新表日志后插入员工表中的值。
How I can do this? Thanks!
我怎么能做到这一点？谢谢！
回答by marc_s
In your trigger, you have two pseudo-tables available, Insertedand Deleted, which contain those values.
在您的触发器中，您有两个可用的伪表Inserted和Deleted，它们包含这些值。
In the case of an UPDATE, the Deletedtable will contain the old values, while the Insertedtable contains the new values.
在 UPDATE 的情况下，该Deleted表将包含旧值，而该Inserted表包含新值。
So if you want to log the ID, OldValue, NewValuein your trigger, you'd need to write something like:
因此，如果您想ID, OldValue, NewValue在触发器中登录，则需要编写如下内容：
CREATE TRIGGER trgEmployeeUpdate
ON dbo.Employees AFTER UPDATE
AS
INSERT INTO dbo.LogTable(ID, OldValue, NewValue)
SELECT i.ID, d.Name, i.Name
FROM Inserted i
INNER JOIN Deleted d ON i.ID = d.ID
Basically, you join the Insertedand Deletedpseudo-tables, grab the ID (which is the same, I presume, in both cases), the old value from the Deletedtable, the new value from the Insertedtable, and you store everything in the LogTable
基本上，您连接Inserted和Deleted伪表，获取 ID（我认为在这两种情况下都是相同的）、Deleted表中的旧值、表中的新值Inserted，然后将所有内容存储在LogTable
回答by Andomar
Here's an example update trigger:
这是一个示例更新触发器：
create table Employees (id int identity, Name varchar(50), Password varchar(50))
create table Log (id int identity, EmployeeId int, LogDate datetime,
OldName varchar(50))
go
create trigger Employees_Trigger_Update on Employees
after update
as
insert into Log (EmployeeId, LogDate, OldName)
select id, getdate(), name
from deleted
go
insert into Employees (Name, Password) values ('Zaphoid', '6')
insert into Employees (Name, Password) values ('Beeblebox', '7')
update Employees set Name = 'Ford' where id = 1
select * from Log
This will print:
这将打印：
id EmployeeId LogDate OldName
1 1 2010-07-05 20:11:54.127 Zaphoid
回答by Martin Smith
In SQL Server 2008 you can use Change Data Capture for this. Details of how to set it up on a table are here http://msdn.microsoft.com/en-us/library/cc627369.aspx
在 SQL Server 2008 中，您可以为此使用更改数据捕获。如何在桌子上设置它的详细信息在这里http://msdn.microsoft.com/en-us/library/cc627369.aspx
回答by Masum
createTRIGGER [dbo].[Table] ON [dbo].[table]
FOR UPDATE
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);
declare @old_v varchar(100)
select @empid=i.Col_Name1 from inserted i;
select @empname=i.Col_Name2 from inserted i;
select @empsal=i.Col_Name2 from inserted i;
select @old_v=d.Col_Name from deleted d
if update(Col_Name1)
set @audit_action='Updated Record -- After Update Trigger.';
if update(Col_Name2)
set @audit_action='Updated Record -- After Update Trigger.';
insert into Employee_Test_Audit1(Col_name1,Col_name2,Col_name3,Col_name4,Col_name5,Col_name6(Old_values))
values(@empid,@empname,@empsal,@audit_action,getdate(),@old_v);
PRINT '----AFTER UPDATE Trigger fired-----.'
回答by user9658686
ALTER trigger ETU on Employee FOR UPDATE AS insert into Log (EmployeeId, LogDate, OldName) select EmployeeId, getdate(), name from deleted go
ALTER 在 Employee FOR UPDATE AS 上触发 ETU 插入日志 (EmployeeId, LogDate, OldName) select EmployeeId, getdate(), name from deleted go
