Identifying A Record from an On Update Trigger

I’ve created a trigger that checks to see whether a field has been updated by querying the status of the field ordinal Number with something like (03) & > 0 where 03 is my field ordinal.

I need to also read the updated record into a set of variables.

It is essential that I read the correct record as the field that is updated might not be unique.

How can I achieve this?
IT GuyDir of ITAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
You can select from the deleted and inserted tables, performing a comparison on the desired columns.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
In a trigger you have access to the delete and insert tables where on the first one you have all the rows that has been delete and on the latter you have all the new rows. In an UPDATE operation you'll find rows in both tables.
You can also use the UPDATE() function in a trigger to check if a column has been changed or not.
So you'll need something like:
CREATE TRIGGER myUpdTrigger
ON TableName
AFTER UPDATE   
AS   
IF UPDATE (ColumnName) 
    BEGIN  
        -- Do here what you need to do if the column has been updated
    END;  
GO  

Open in new window

0
 
IT GuyDir of ITAuthor Commented:
What I need to do it to be able to select the values of the entire record based upon one record having been updated.

I have my on update trigger, I have the ordinal of the column that has been updated - I just need a line and f code that will allow me to select the values from all fields that have been updated.

Do I select from deleted?
0
 
IT GuyDir of ITAuthor Commented:
I'll try it and let you know. Thank you.
0
 
IT GuyDir of ITAuthor Commented:
Worked - thank you
0
All Courses

From novice to tech pro — start learning today.