Trigger of column not updating

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

General SQL Server Forums

New to SQL Server Programming

Trigger of column not updating

Author

Topic

sinva
Starting Member

23 Posts

Posted - 2010-06-12 : 04:00:48

Hi all,

I have a question that as I know trigger is based on a table when I want to check the changes about a column I could use the values in inserted and deleted. How could I check if the following update statement: update table_a set column_a = column_a where key_value = 'A', is updating the column_a that is same as the original one; but not updating other columns but not involved column_a like update table_a set column_b = 'ABC' where key_value = 'A'.

Thanks all

Kristen
Test

22859 Posts

Posted - 2010-06-12 : 05:30:03

Not sure I understand your question, but


SELECT *
FROM Inserted AS I
    JOIN Deleted AS D
        ON D.MyPK = I.MyPK
WHERE (
          I.column_a <> D.column_a COLLATE Latin1_General_BIN2
       OR (I.column_a IS NULL AND D.column_a IS NOT NULL)
       OR (I.column_a IS NOT NULL AND D.column_a IS NULL)
      )

will select records where the UPDATE has changed the value in column_a

sinva
Starting Member

23 Posts

Posted - 2010-06-12 : 05:52:04

Kristen, Thank you for your reply.

Yes this could check if the column is updated or not but my question is even I the value in the inserted and deleted is the same, it could not show that the same is due to which reasons: (1) I update the column to have the same value explicitly using set column_a = column_a or set column_a = 'ABC' that 'ABC' is the original value or (2) I actually did not update the column_a in the statement. Do you get my question?

Kristen
Test

22859 Posts

Posted - 2010-06-12 : 05:55:07

You cannot tell the difference between situation (1a) or (1b)

If (2) means that you want to know if the column was part of the SET statement in the UPDATE statement then you can use COLUMNS_UPDATED() to detect that

So using COLUMNS_UPDATED() combined with my test above you can deduce that a) column was included in the SET statement and b) whether Column Value was Changed, or Not changed

sinva
Starting Member

23 Posts

Posted - 2010-06-12 : 06:36:23

Thank you Kristen, I just want to deduce a) column was included in the SET statement and b) whether Column Value was Changed, or Not changed. Thank you for your advice

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources