更新触发器后的 SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25568526/
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
SQL Server after update trigger
提问by user3927897
I have a problem with this trigger. I would like it to update the requested information only to the row in question (the one I just updated) and not the entire table.
我有这个触发器的问题。我希望它仅将请求的信息更新到相关行（我刚刚更新的行）而不是整个表。
CREATE TRIGGER [dbo].[after_update]
ON [dbo].[MYTABLE]
AFTER UPDATE
AS
BEGIN
UPDATE MYTABLE
SET mytable.CHANGED_ON = GETDATE(),
CHANGED_BY=USER_NAME(USER_ID())
How do I tell the trigger that this applies only to the row in question?
我如何告诉触发器这仅适用于相关行？
回答by Juan
Here is my example after a test
这是我测试后的示例
CREATE TRIGGER [dbo].UpdateTasadoresName
ON [dbo].Tasadores
FOR UPDATE
AS
UPDATE Tasadores
SET NombreCompleto = RTRIM( Tasadores.Nombre + ' ' + isnull(Tasadores.ApellidoPaterno,'') + ' ' + isnull(Tasadores.ApellidoMaterno,'') )
FROM Tasadores
INNER JOIN INSERTED i ON Tasadores.id = i.id
The inserted special table will have the information from the updated record.
插入的特殊表将包含来自更新记录的信息。
回答by Homer J. Simpson
Try this (update, not after update)
试试这个（更新，而不是更新后）
CREATE TRIGGER [dbo].[xxx_update] ON [dbo].[MYTABLE]
FOR UPDATE
AS
BEGIN
UPDATE MYTABLE
SET mytable.CHANGED_ON = GETDATE()
,CHANGED_BY = USER_NAME(USER_ID())
FROM inserted
WHERE MYTABLE.ID = inserted.ID
END
回答by kdnerd
It is very simple to do that, First create a copy of your table that your want keep the log for For example you have Table dbo.SalesOrder with columns SalesOrderId, FirstName,LastName, LastModified
这样做非常简单，首先创建您想要保留日志的表的副本 例如，您有表 dbo.SalesOrder，其中包含 SalesOrderId、FirstName、LastName、LastModified 列
Your Version archieve table should be dbo.SalesOrderVersionArchieve with columns SalesOrderVersionArhieveId, SalesOrderId, FirstName,LastName, LastModified
您的版本存档表应该是 dbo.SalesOrderVersionArchieve，其中包含列 SalesOrderVersionArhieveId、SalesOrderId、FirstName、LastName、LastModified
Here is the how you will set up a trigger on SalesOrder table
这是在 SalesOrder 表上设置触发器的方法
USE [YOURDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Karan Dhanu
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER dbo.[CreateVersionArchiveRow]
ON dbo.[SalesOrder]
AFTER Update
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.SalesOrderVersionArchive
SELECT *
FROM deleted;
END
Now if you make any changes in saleOrder table it will show you the change in VersionArchieve table
现在，如果您在 saleOrder 表中进行任何更改，它将显示 VersionArchieve 表中的更改
回答by Hardik
try this solution.
试试这个解决方案。
DECLARE @Id INT
DECLARE @field VARCHAR(50)
SELECT @Id= INSERTED.CustomerId
FROM INSERTED
IF UPDATE(Name)
BEGIN
SET @field = 'Updated Name'
END
IF UPDATE(Country)
BEGIN
SET @field = 'Updated Country'
END
INSERT INTO CustomerLogs
VALUES(@Id, @field)
// OR
-- If you wish to update existing table records.
UPDATE YOUR_TABLE SET [FIELD]=[VALUE] WHERE {CONDITION}
I didn't checked this with older version of sql server but this will work with sql server 2012.
我没有用旧版本的 sql server 检查过这个，但这适用于 sql server 2012。
回答by Kane
You should be able to access the INSERTEDtable and retrieve ID or table's primary key. Something similar to this example ...
您应该能够访问该INSERTED表并检索 ID 或表的主键。类似于这个例子的东西......
CREATE TRIGGER [dbo].[after_update] ON [dbo].[MYTABLE]
AFTER UPDATE AS
BEGIN
DECLARE @id AS INT
SELECT @id = [IdColumnName]
FROM INSERTED
UPDATE MYTABLE
SET mytable.CHANGED_ON = GETDATE(),
CHANGED_BY=USER_NAME(USER_ID())
WHERE [IdColumnName] = @id
Here's a link on MSDN on the INSERTEDand DELETEDtables available when using triggers: http://msdn.microsoft.com/en-au/library/ms191300.aspx
下面是在MSDN上的链接INSERTED，并DELETED使用触发器时表可供选择：http://msdn.microsoft.com/en-au/library/ms191300.aspx
回答by Kane
First off, your trigger as you already see is going to update every record in the table. There is no filtering done to accomplish jus the rows changed.
首先，您已经看到的触发器将更新表中的每条记录。没有过滤来完成只是改变行。
Secondly, you're assuming that only one row changes in the batch which is incorrect as multiple rows could change.
其次，您假设批次中只有一行更改，这是不正确的，因为多行可能更改。
The way to do this properly is to use the virtual inserted and deleted tables: http://msdn.microsoft.com/en-us/library/ms191300.aspx
正确执行此操作的方法是使用虚拟插入和删除表：http: //msdn.microsoft.com/en-us/library/ms191300.aspx
回答by Tyler Feldkamp
Try this script to create a temporary table TESTTEST and watch the order of precedence as the triggers are called in this order: 1) INSTEAD OF, 2) FOR, 3) AFTER
试试这个脚本来创建一个临时表 TESTTEST 并观察优先顺序，因为触发器按以下顺序调用：1) INSTEAD OF, 2) FOR, 3) AFTER
All of the logic is placed in INSTEAD OF trigger and I have 2 examples of how you might code some scenarios...
所有逻辑都放在 INSTEAD OF 触发器中，我有 2 个示例说明您如何编写某些场景...
Good luck...
祝你好运...
CREATE TABLE TESTTEST
(
ID INT,
Modified0 DATETIME,
Modified1 DATETIME
)
GO
CREATE TRIGGER [dbo].[tr_TESTTEST_0] ON [dbo].TESTTEST
INSTEAD OF INSERT,UPDATE,DELETE
AS
BEGIN
SELECT 'INSTEAD OF'
SELECT 'TT0.0'
SELECT * FROM TESTTEST
SELECT *, 'I' Mode
INTO #work
FROM INSERTED
UPDATE #work SET Mode='U' WHERE ID IN (SELECT ID FROM DELETED)
INSERT INTO #work (ID, Modified0, Modified1, Mode)
SELECT ID, Modified0, Modified1, 'D'
FROM DELETED WHERE ID NOT IN (SELECT ID FROM INSERTED)
--Check Security or any other logic to add and remove from #work before processing
DELETE FROM #work WHERE ID=9 -- because you don't want anyone to edit this id?!?!
DELETE FROM #work WHERE Mode='D' -- because you don't want anyone to delete any records
SELECT 'EV'
SELECT * FROM #work
IF(EXISTS(SELECT TOP 1 * FROM #work WHERE Mode='I'))
BEGIN
SELECT 'I0.0'
INSERT INTO dbo.TESTTEST (ID, Modified0, Modified1)
SELECT ID, Modified0, Modified1
FROM #work
WHERE Mode='I'
SELECT 'Cool stuff would happen here if you had FOR INSERT or AFTER INSERT triggers.'
SELECT 'I0.1'
END
IF(EXISTS(SELECT TOP 1 * FROM #work WHERE Mode='D'))
BEGIN
SELECT 'D0.0'
DELETE FROM TESTTEST WHERE ID IN (SELECT ID FROM #work WHERE Mode='D')
SELECT 'Cool stuff would happen here if you had FOR DELETE or AFTER DELETE triggers.'
SELECT 'D0.1'
END
IF(EXISTS(SELECT TOP 1 * FROM #work WHERE Mode='U'))
BEGIN
SELECT 'U0.0'
UPDATE t SET t.Modified0=e.Modified0, t.Modified1=e.Modified1
FROM dbo.TESTTEST t
INNER JOIN #work e ON e.ID = t.ID
WHERE e.Mode='U'
SELECT 'U0.1'
END
DROP TABLE #work
SELECT 'TT0.1'
SELECT * FROM TESTTEST
END
GO
CREATE TRIGGER [dbo].[tr_TESTTEST_1] ON [dbo].TESTTEST
FOR UPDATE
AS
BEGIN
SELECT 'FOR UPDATE'
SELECT 'TT1.0'
SELECT * FROM TESTTEST
SELECT 'I1'
SELECT * FROM INSERTED
SELECT 'D1'
SELECT * FROM DELETED
SELECT 'TT1.1'
SELECT * FROM TESTTEST
END
GO
CREATE TRIGGER [dbo].[tr_TESTTEST_2] ON [dbo].TESTTEST
AFTER UPDATE
AS
BEGIN
SELECT 'AFTER UPDATE'
SELECT 'TT2.0'
SELECT * FROM TESTTEST
SELECT 'I2'
SELECT * FROM INSERTED
SELECT 'D2'
SELECT * FROM DELETED
SELECT 'TT2.1'
SELECT * FROM TESTTEST
END
GO
SELECT 'Start'
INSERT INTO TESTTEST (ID, Modified0) VALUES (9, GETDATE())-- not going to insert
SELECT 'RESTART'
INSERT INTO TESTTEST (ID, Modified0) VALUES (10, GETDATE())--going to insert
SELECT 'RESTART'
UPDATE TESTTEST SET Modified1=GETDATE() WHERE ID=10-- gointo to update
SELECT 'RESTART'
DELETE FROM TESTTEST WHERE ID=10-- not going to DELETE
SELECT 'FINISHED'
SELECT * FROM TESTTEST
DROP TABLE TESTTEST
回答by Reza Jenabi
you can call INSERTED, SQL Server uses these tables to capture the data of the modified row before and after the event occurs.I assume in your table the name of the key is Id
您可以调用INSERTED，SQL Server 使用这些表来捕获事件发生前后修改行的数据。我假设在您的表中键的名称是Id
I think the following code can help you
我认为以下代码可以帮助您
CREATE TRIGGER [dbo].[after_update]
ON [dbo].[MYTABLE]
AFTER UPDATE
AS
BEGIN
UPDATE dbo.[MYTABLE]
SET dbo.[MYTABLE].CHANGED_ON = GETDATE(),
dbo.[MYTABLE].CHANGED_BY = USER_NAME(USER_ID())
FROM INSERTED
WHERE INSERTED.Id = dbo.[MYTABLE].[Id]
END
回答by January Mmako
CREATE TRIGGER [dbo].[after_update] ON [dbo].[MYTABLE]
AFTER UPDATE
AS
BEGIN
DECLARE @ID INT
SELECT @ID = D.ID
FROM inserted D
UPDATE MYTABLE
SET mytable.CHANGED_ON = GETDATE()
,CHANGED_BY = USER_NAME(USER_ID())
WHERE ID = @ID
END
