how to write trigger which will fire on column upd

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 2005 Forums

Transact-SQL (2005)

how to write trigger which will fire on column upd

Author

Topic

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2010-02-24 : 01:09:22

Hi all,

i want to write a trigger which will fire only when my perticuler column will update.

any help appricated.

Pankaj

Pankaj

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-24 : 01:19:43

see http://msdn.microsoft.com/en-us/library/ms187326.aspx

KH
[spoiler]Time is always against us[/spoiler]

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2010-02-24 : 01:22:42

quote:
Originally posted by khtan

see http://msdn.microsoft.com/en-us/library/ms187326.aspx

KH
[spoiler]Time is always against us[/spoiler]

Thanks it's working

Pankaj

Kristen
Test

22859 Posts

Posted - 2010-02-24 : 01:50:48

Note that UPDATE() does not test that the data in the column has actually changed, only that the column was included in the update statement.


UPDATE MyTable
SET MyColumn = MyColumn

will set UPDATE(MyColumn) = True

This will test for an actual change in the column:


IF EXISTS
(
    SELECT *
    FROM   Inserted AS I
           JOIN Deleted AS D
               ON D.MyPK_ID = I.MyPK_ID
              AND (D.MyColumn <> I.MyColumn
                   OR (D.MyColumn IS NULL AND I.MyColumn IS NOT NULL)
                   OR (D.MyColumn IS NOT NULL AND I.MyColumn IS NULL))
)
BEGIN
    .... Actions to perform ....
END

and note that if MyColumn is char/varchar you need to force Binary Collation on that comparison test to catch a difference in just the case of the text.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-24 : 02:04:19

good point.

Looks like I need to add more coal or woods. On second thought, may be should change to something green

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources