Update statement for when a specific field changes

Question

Post reply

Update statement for when a specific field changes

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

kyle.doouss SSCommitted Points: 1513 More actions · Answer 1

kyle.doouss

SSCommitted

Points: 1513

December 5, 2011 at 6:02 am

#1416626

Soz, Will do!

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

Dev (12/5/2011)

But a much better approach would be using the COLUMNS_UPDATED function.

http://msdn.microsoft.com/en-us/library/ms186329.aspx%5B/quote%5D

It requires bit comparison. If OP is comfortable with it, GO Ahead. I usually avoid it (my discomfort).

Side note on this. If the column is listed in the UPDATE clause of the calling statement, this bit is turned on. It does NOT check for data-changes, it merely checks if the column was used in the UPDATE.

IE: UPDATE tbl SET cola = 1, colb=1, colc=1 WHERE cola=1

This will activate all three, cola, colb, and colc, bits, even though it's (nearly) impossible that colA had changed.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

tfifield SSCrazy Eights Points: 9655 More actions · Answer 3

Kyle,

The trigger as it stands won't do anything for an INSERT since the inserted table won't join to the deleted table. The IF UPDATE(STK_COSTPRICE) will always test true for an insert. If you actually want the update to occur for an INSERT then you could use EXCEPT between the inserted and deleted tables:



ALTER TRIGGER UDEF_COSTPRICE_UPDATE_DATE_KWD

ON stk_STOCK AFTER INSERT, UPDATE

AS

IF UPDATE(STK_COSTPRICE)

    BEGIN

        SET NOCOUNT ON ;

        UPDATE	S

        SET     S.STK_P_WGHT_NAME = GETDATE()

        FROM    STK_STOCK S

        INNER JOIN

          (SELECT i.STK_PRIMARY, i.STK_COSTPRICE

           FROM inserted i

           EXCEPT

           SELECT d.STK_PRIMARY, d.STK_COSTPRICE

           FROM deleted d

          )  AS X ON

            S.STK_PRIMARY = X.STK_PRIMARY

    END