如何使用 sql server 执行 BEFORE UPDATED 触发器？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/642822/
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
How can I do a BEFORE UPDATED trigger with sql server?
提问by Bigballs
I'm using Sqlserver express and I can't do before updatedtrigger. There's a other way to do that?
我正在使用 Sqlserver express，但无法before updated触发。还有其他方法可以做到这一点吗？
采纳答案by achinda99
MSSQL does not support BEFOREtriggers. The closest you have is INSTEAD OFtriggers but their behavior is different to that of BEFOREtriggers in MySQL.
MSSQL 不支持BEFORE触发器。最接近的是INSTEAD OF触发器，但它们的行为与BEFOREMySQL中的触发器不同。
You can learn more about them here, and note that INSTEAD OFtriggers "Specifies that the trigger is executed instead of the triggering SQL statement, thus overriding the actions of the triggering statements." Thus, actions on the update may not take place if the trigger is not properly written/handled. Cascading actions are also affected.
您可以在此处了解有关它们的更多信息，并注意INSTEAD OF触发器“指定执行触发器而不是触发 SQL 语句，从而覆盖触发语句的操作。” 因此，如果触发器未正确写入/处理，则可能不会对更新进行操作。级联操作也会受到影响。
You may instead want to use a different approach to what you are trying to achieve.
相反，您可能想要使用不同的方法来实现您的目标。
回答by Stamen
It is true that there aren't "before triggers" in MSSQL. However, you could still track the changes that were made on the table, by using the "inserted" and "deleted" tables together. When an update causes the trigger to fire, the "inserted" table stores the new values and the "deleted" table stores the old values. Once having this info, you could relatively easy simulate the "before trigger" behaviour.
MSSQL 中确实没有“触发器之前”。但是，您仍然可以通过同时使用“插入”和“删除”表来跟踪对表所做的更改。当更新导致触发器触发时，“插入”表存储新值，“删除”表存储旧值。获得此信息后，您可以相对轻松地模拟“触发前”行为。
回答by Charlie Joynt
Can't be sure if this applied to SQL Server Express, but you can still access the "before" data even if your trigger is happening AFTER the update. You need to read the data from either the deletedor insertedtable that is created on the fly when the table is changed. This is essentially what @Stamen says, but I still needed to explore further to understand that (helpful!) answer.
无法确定这是否适用于 SQL Server Express，但即使您的触发器在更新后发生，您仍然可以访问“之前”数据。您需要从表更改时动态创建的已删除或插入表中读取数据。这基本上是@Stamen 所说的，但我仍然需要进一步探索以了解（有用！）答案。
The deletedtable stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table...
The insertedtable stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the inserted table and the trigger table...
在删除过程中DELETE和UPDATE语句受影响的行的表存储副本。在执行 DELETE 或 UPDATE 语句期间，行从触发器表中删除并转移到已删除表中...
在插入过程中INSERT和UPDATE语句受影响的行的表存储副本。在插入或更新事务期间，新行被添加到插入表和触发器表中...
So you can create your trigger to read data from one of those tables, e.g.
因此，您可以创建触发器以从这些表之一读取数据，例如
CREATE TRIGGER <TriggerName> ON <TableName>
AFTER UPDATE
AS
BEGIN
INSERT INTO <HistoryTable> ( <columns...>, DateChanged )
SELECT <columns...>, getdate()
FROM deleted;
END;
My example is based on the one here:
我的示例基于此处的示例：
回答by Ian Nelson
T-SQL supports only AFTER and INSTEAD OF triggers, it does not feature a BEFORE trigger, as found in some other RDBMSs.
T-SQL 仅支持 AFTER 和 INSTEAD OF 触发器，它不具有 BEFORE 触发器，如在其他一些 RDBMS 中发现的那样。
I believe you will want to use an INSTEAD OF trigger.
我相信您会想要使用 INSTEAD OF 触发器。
回答by Tomalak
All "normal" triggers in SQL Server are "AFTER ..." triggers. There are no "BEFORE ..." triggers.
SQL Server 中的所有“正常”触发器都是“AFTER ...”触发器。没有“BEFORE ...”触发器。
To do something before an update, check out INSTEAD OF UPDATE Triggers.
要在更新之前做一些事情，请查看INSTEAD OF UPDATE Triggers。
回答by Luciano
To do a BEFORE UPDATEin SQL Server I use a trick. I do a false update of the record (UPDATE Table SET Field = Field), in such way I get the previous image of the record.
为了BEFORE UPDATE在 SQL Server 中做一个，我使用了一个技巧。我对记录进行了错误的更新 ( UPDATE Table SET Field = Field)，这样我就得到了记录的先前图像。
回答by HLGEM
Remember that when you use an instead trigger, it will not commit the insert unless you specifically tell it to in the trigger. Instead of really means do this instead of what you normally do, so none of the normal insert actions would happen.
请记住，当您使用替代触发器时，除非您在触发器中明确告诉它，否则它不会提交插入。而不是真正的意思是做这个而不是你通常做的，所以不会发生任何正常的插入操作。
回答by Nick Oetjen
Full example:
完整示例：
CREATE TRIGGER [dbo].[trig_020_Original_010_010_Gamechanger]
ON [dbo].[T_Original]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Old_Gamechanger int;
DECLARE @New_Gamechanger int;
-- Insert statements for trigger here
SELECT @Old_Gamechanger = Gamechanger from DELETED;
SELECT @New_Gamechanger = Gamechanger from INSERTED;
IF @Old_Gamechanger != @New_Gamechanger
BEGIN
INSERT INTO [dbo].T_History(ChangeDate, Reason, Callcenter_ID, Old_Gamechanger, New_Gamechanger)
SELECT GETDATE(), 'Time for a change', Callcenter_ID, @Old_Gamechanger, @New_Gamechanger
FROM deleted
;
END
END
