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;
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.