?
Solved

Get data in trigger

Posted on 2005-03-16
4
Medium Priority
?
514 Views
Last Modified: 2007-12-19
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.
Example:
    The table has two columns.
CulumnA (nvarchar 20)
ColumnB (int)

and the trigger:

CREATE TRIGGER MyTableUpdate ON My_Table_Name
FOR Update
AS
   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?
0
Comment
Question by:melodiesoflife
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 33

Accepted Solution

by:
sajuks earned 1200 total points
ID: 13561967
try this
SELECT @newcolumnA= columnname FROM INSERTED
SELECT @oldcolumnA = columnname FROM DELETED
0
 
LVL 33

Expert Comment

by:sajuks
ID: 13561974
basically
select * from inserted contains the new/inserted values
select * from deleted contains the old/deleted values

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 800 total points
ID: 13562006
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.
0
 
LVL 6

Expert Comment

by:jchopde
ID: 13562027
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
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question