• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 517
  • Last Modified:

Get data in trigger

Hi all

I'm writting a trigger for update event, and I want to compare the old data with the new data before doing something.
    The table has two columns.
CulumnA (nvarchar 20)
ColumnB (int)

and the trigger:

CREATE TRIGGER MyTableUpdate ON My_Table_Name
FOR Update
   Declare @oldColumnA as nvarchar(20)
   Declare @newColumnA as nvarchar(20)
   Declare @oldColumnB as int
   Declare @newColumnB as int

   Set @newColumnA= /* new value */
   Set @oldColumnA = /* old value */
   /// Compare them and do something.

How can I get the old data and new data in my trigger?
  • 2
2 Solutions
try this
SELECT @newcolumnA= columnname FROM INSERTED
SELECT @oldcolumnA = columnname FROM DELETED
select * from inserted contains the new/inserted values
select * from deleted contains the old/deleted values

Anthony PerkinsCommented:
Triggers use the logical tables Inserted and Deleted that you can use to compare values.

>>   Set @newColumnA= /* new value */
   Set @oldColumnA = /* old value */<<

Unfortunately it is not as straight forward as that.  If it is a batch update the trigger is only fired once at the end. So the Inserted and Deleted would contain all the rows updated.

If you can be absolutely sure that there will never be more than one upate at a time than you can do something like this:

Select @newColumnA = ColumnA
From Inserted

Select @oldColumnB = ColumnA
From Deleted

However, if there should be more than on row updated than those variables will contain the last one only.
When writing the trigger, bear in mind that the inserted and deleted tables can contain multiple rows if an UPDATE is performed that affects multiple rows in the table

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now