September 14, 2011 at 9:12 am
Please tell me how to get a field value in the After Update trigger?
Suppose I have a trigger:
CREATE TRIGGER myTrigger ON myTable
AFTER UPDATE
AS
BEGIN
If UPDATE(column1)
begin
-- Here I want to get the current value of column1 to a variable
-- How I do this?
end
END
September 14, 2011 at 9:18 am
Pham Tuan Phat (9/14/2011)
Please tell me how to get a field value in the After Update trigger?Suppose I have a trigger:
CREATE TRIGGER myTrigger ON myTable
AFTER UPDATE
AS
BEGIN
If UPDATE(column1)
begin
-- Here I want to get the current value of column1 to a variable
-- How I do this?
end
END
What do you mean the current value? This is an update so the concept of current is nearly impossible to determine. The inserted table has the new values and deleted has the values prior to the update. Take a look at both the inserted and deleted tables in BOL
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 14, 2011 at 10:10 am
CREATE TRIGGER myTrigger ON myTable
AFTER UPDATE
AS
BEGIN
If UPDATE(column1)
-- returns "new" updated value
select column1 from inserted
-- returns old, pre-update value
select column1 from deleted
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 14, 2011 at 2:15 pm
You can't put the value of Column1 into a variable - or at least if you do the variable will only ever be able to hold the value of Column1 of a single row.
Which is exactly why the inserted and deleted "magic" (hate that term) tables exist. They contain the "before" and "after" values of all columns for every updated row.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply