如何在更新SQL Server 2005中的表值之前编写触发器 [英] How to write trigger for before update the table value in SQL server 2005

问题描述

我正在使用Sql Server 2005.我需要在更新表之前为sql表写入Trigger。我有两个表，一个是tblEmpDetail，另一个是tblEmpHistoryDe​​tail。

表中有字段

i）tblEmpDetail： -

ID数字（18）AutoIncrement，

EmpName nvarchar（50），

Dob datetime

ii） tblEmpHistoryDe​​tail

I'm using Sql Server 2005.I need to write Trigger for sql table before Update the table.i've two tables one is tblEmpDetail and Another one is tblEmpHistoryDetail .
The table has fields
i)tblEmpDetail :-
ID numeric(18) AutoIncrement,
EmpName nvarchar(50),
Dob datetime
ii)tblEmpHistoryDetail

ID numeric(18) AutoIncrement,
MasterID numeric(18),
EmpName nvarchar(20),
Dob datetime

如何在更新前创建触发器



我尝试过：



我的触发代码是

how to create Trigger for before an update

What I have tried:

My Trigger code is

CREATE TRIGGER trgrBeforeUpdate on tblEmpDetail 
For Update
 declare @MasterID numeric(18)
 declare @EmpName nvarchar(50);
 declare @Dob Datetime;

select @MasterID=i.ID from inserted i;
select @EmpName =i.EmpName from inserted i;
select @Dob =i.Dob from inserted i;
	INSERT INTO tblEmpHistoryDetail 
         (MasterID,EmpName,Dob)
        Values
        (@MasterID,@EmpName,@Dob)
	PRINT 'BEFORE UPDATE trigger fired.'

推荐答案

编写触发器时，您需要记住插入和已更新的虚拟表可能包含多行。按照目前的情况，您的触发器将仅记录每个 UPDATE 语句更新的最后一行的详细信息。



常见的SQL Server错误 - 多行DML触发器 - SQLServerCentral [ ^ ]



根据您的描述，我怀疑您要记录触发器中的旧列值。您不需要在 UPDATE 语句之前运行触发器来执行此操作;你只需要使用已删除的虚拟表。



使用插入和删除的表 - SQL Server | Microsoft Docs [ ^ ]

（该文档说从SQL 2008开始，但这是错误的。这些信息至少适用于SQL 2000;它是只是2008年是最早仍然支持的版本。）

When writing a trigger, you need to remember that the inserted and updated virtual tables could contain multiple rows. As it stands, your trigger will only log the details of the last row updated by each UPDATE statement.

Common SQL Server Mistakes – Multi Row DML Triggers - SQLServerCentral[^]

Based on your description, I suspect you want to log the old column values from your trigger. You don't need the trigger to run before the UPDATE statement to do that; you just need to use the deleted virtual table instead.

Use the inserted and deleted Tables - SQL Server | Microsoft Docs[^]
(That documentation says "Starting with SQL 2008", but that's wrong. The information applies from at least SQL 2000; it's just that 2008 is the earliest version that's still supported.)

CREATE TRIGGER trgrBeforeUpdate 
ON tblEmpDetail 
AFTER UPDATE
As
BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO tblEmpHistoryDetail (MasterID, EmpName, Dob)
    SELECT MasterID, EmpName, Dob
    FROM deleted;
END;

如果真的想要一个在更新表之前触发的触发器，然后你需要使用一个INSTEAD OF触发器 [ ^ ]。触发器也将负责更新源表。



值得注意的是，SQL 2005的支持在2016年4月结束，并支持SQL 2008和2008 R2将于7月结束。如果可能的话，您应该寻求升级到支持的版本 - 特别是因为运行2005/2008 / 2008R2的Windows版本可能也会失去支持。

If you really want a trigger that fires before the table is updated, then you would need to use an INSTEAD OF trigger[^]. The trigger would then be responsible for updating the source table as well.

It's worth noting that support for SQL 2005 ended in April 2016, and support for SQL 2008 and 2008 R2 will end in July. If at all possible, you should be looking to upgrade to a supported version - especially as the versions of Windows that 2005/2008/2008R2 run on will probably be out of support as well.

这篇关于如何在更新SQL Server 2005中的表值之前编写触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！