Trigger on Update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Trigger on Update

Author

Topic

baze7
Yak Posting Veteran

58 Posts

Posted - 2010-02-15 : 12:59:47

Is it possible to run a trigger on an update of a field, or just insert?

Thanks
Chad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 13:02:55

yup. its possible. however not for update of field alone but for update of entire table. however you can check if field was involved in update inside trigger code using UPDATE() function

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

akira220984
Starting Member

13 Posts

Posted - 2010-02-16 : 04:21:46

Funnily enough, I happened to use the UPDATE() function yesterday (before reading this post) inside a trigger that I have been working on.
In the first instance, I was checking the values of a column against the deleted tables, if a change had occurred, then do something, else leave alone.

The UPDATE() is a very useful solution and great time saver :)

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 04:33:28

What if some rows have changed in that column, and some rows have not?

(I'm curious to know, because I know about UPDATE() but have never used it for that reason)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 04:35:45

Great

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

akira220984
Starting Member

13 Posts

Posted - 2010-02-16 : 05:08:36

Kristen:

From what I can see, the rows that have changed ~~have~~ (in my example) have an action performed on them (just a simple dateadd). Those that have not changed keep their original value (value from the table).

Unless you can see any drawbacks? (Very keen on learning all angles :) )

Regards,

Ric

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 05:22:12

quote:
Originally posted by akira220984

Kristen:

From what I can see, the rows that have changed ~~have~~ (in my example) have an action performed on them (just a simple dateadd). Those that have not changed keep their original value (value from the table).

Unless you can see any drawbacks? (Very keen on learning all angles :) )

Regards,

Ric

you could compare contents of column in inserted and deleted tables for that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 05:52:36

"you could compare contents of column in inserted and deleted tables for that"

That's what I do (always) hence I never use UPDATED() - but I figure that UPDATED() is there for a reason, and maybe my Triggers could be more efficient / faster if I used UPDATED() [instead of compare columns in Inserted / Deleted]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 06:06:56

quote:
Originally posted by Kristen

"you could compare contents of column in inserted and deleted tables for that"

That's what I do (always) hence I never use UPDATED() - but I figure that UPDATED() is there for a reason, and maybe my Triggers could be more efficient / faster if I used UPDATED() [instead of compare columns in Inserted / Deleted]

Nope . If ever the column has involved in update we need to proceed. so trigger body starts with


IF UPDATE(Column)
BEGIN
--do processing
...
WHERE (ISNULL(d.col,'')<> ISNULL(i.col,'')
OR ISNULL(d.col2,-1)<> ISNULL(i.col2,-1)
...
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 07:43:38

OK, so Updated() just tells me that one, or more, rows has changed in that column.

So then I do my normal compare of Inserted and Delete.

But using UPDATED() "short circuits" if there have been NO rows changed in that column?

Given that I will do compare of Inserted and Delete anyway, is SQL Server (in effect) going to do that twice if I used UPDATED() - or does it have some in-memory flag, or somesuch, that means it will really short-circuit if that column has not changed [in any rows]? That's really the bit I have never been sure about - is there a real CPU/Disk saving?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 12:18:29

quote:
Originally posted by Kristen

OK, so Updated() just tells me that one, or more, rows has changed in that column.

So then I do my normal compare of Inserted and Delete.

But using UPDATED() "short circuits" if there have been NO rows changed in that column?

Given that I will do compare of Inserted and Delete anyway, is SQL Server (in effect) going to do that twice if I used UPDATED() - or does it have some in-memory flag, or somesuch, that means it will really short-circuit if that column has not changed [in any rows]? That's really the bit I have never been sure about - is there a real CPU/Disk saving?

UPDATE() returns a boolean result which indicates whether column was involved in an insert or update operation. So it can used in caes where we need to implement any specific business rules which needs to be done only if particular column update/insert was attempted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 12:22:28

Hehehe ... you're like a politician not answering a question

So is the bitmap formed during the physical update, and therefore "costs nothing" to use it [in the trigger], or is it calculated, just-in-time, by a complete pass of INSERTED / DELETED tables do you know?

I reckon it must be the former; maybe I should have been paying more attention to it all these years ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 12:50:52

quote:
Originally posted by Kristen

Hehehe ... you're like a politician not answering a question

So is the bitmap formed during the physical update, and therefore "costs nothing" to use it [in the trigger], or is it calculated, just-in-time, by a complete pass of INSERTED / DELETED tables do you know?

I reckon it must be the former; maybe I should have been paying more attention to it all these years ...

As i understand the bitmap formed as a result of check done if an insert/update attempt was attempted on column so I'm not sure if its calculated by a complete pass of INSERTED / DELETED tables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 13:42:08

Pah! I've made a test ... now I remember why I decided not to use it (given that I have to do the INSERTED / DELETED comparison anyway)

UPDATE(MyColumn) just tells you if MyColumn is included the UPDATE statement, NOT if the data in the column has changed [from what it was before]

So:

UPDATE MyTable
SET MyColumn = MyColumn

sets UPDATE(MyColumn) = True

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 13:45:53

quote:
Originally posted by Kristen

Pah! I've made a test ... now I remember why I decided not to use it (given that I have to do the INSERTED / DELETED comparison anyway)

UPDATE(MyColumn) just tells you if MyColumn is included the UPDATE statement, NOT if the data in the column has changed [from what it was before]

So:

UPDATE MyTable
SET MyColumn = MyColumn

sets UPDATE(MyColumn) = True

thats why i told if an insert/update attempt was attempted on column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 14:00:11

Sorry, missed the significancy of that statement.

So:

1) It should be "cheap" for SQL to deduce that a column was included in the Insert/Update statement.
2) Only really of much benefit if there are plenty of statements that do NOT include that column in the Update/Insert statement.

For me (2) is pretty rare I think

Thanks for your patience

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 00:11:24

quote:
Originally posted by Kristen

Sorry, missed the significancy of that statement.

So:

1) It should be "cheap" for SQL to deduce that a column was included in the Insert/Update statement.
2) Only really of much benefit if there are plenty of statements that do NOT include that column in the Update/Insert statement.

For me (2) is pretty rare I think

Thanks for your patience

No problem

My usage of this has been cases where we have lots of business rules to be implemented that's determined by if a particular column was attempted to insert/modify

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources