Update trigger on non updated value

[sapator]

Just wanted to make sure that in Mssql an IF UPDATE(column value) has updated with the same value, the if will work.
Thanks.

[Zvoni]

Just wanted to make sure that in Mssql an IF UPDATE(column value) has updated with the same value, the if will work.
Thanks.

Haven't understood a word of it?!?!?

[sapator]

OK , more clear

ALTER TRIGGER [dbo].[zz_tr_cognetic_members_membership_Update]
    ON [dbo].[cognetic_members_membership]
    FOR INSERT, UPDATE
AS
	
	IF UPDATE(membership_clubid)
.....

Let's say that someone do an update from clubid 10 to clubid 10. Will the 'IF' continue? Also since on the subject, can I have multiple hits of a trigger? Meaning not only if one specific field is updated but another.
Something like IF UPDATE in (membership_clubid,membership_name,membership_ID) , or I need multiple triggers?

Thanks.

[Zvoni]

Ah.... --> https://learn.microsoft.com/en-us/sq...l-server-ver16

About half way down the page

Testing for UPDATE or INSERT Actions to Specific Columns

You can design a Transact-SQL trigger to do certain actions based on UPDATE or INSERT modifications to specific columns. Use UPDATE() or COLUMNS_UPDATED in the body of the trigger for this purpose. UPDATE() tests for UPDATE or INSERT attempts on one column. COLUMNS_UPDATED tests for UPDATE or INSERT actions that run on multiple columns. This function returns a bit pattern that indicates which columns were inserted or updated.

As to your original question: https://learn.microsoft.com/en-us/sq...l-server-ver16

If a trigger applies to a column, the UPDATED value will return as true or 1, even if the column value remains unchanged. This is by-design, and the trigger should implement business logic that determines if the insert/update/delete operation is permissible or not.

So to get a "real" check:
IF INSERTED.ClubID=DELETED.ClubID THEN /*ClubID has NOT been updated/changed*/ ELSE DoSomething /*ClubID has really been changed */ IFEND

EDIT: btw: Easy to find out.
Just implement something like this in the Trigger-Body (AIRCODE!!)

IF INSERTED.ClubID=DELETED.ClubID THEN 
  INSERT INTO MyTestTable (SomeField) VALUES('ClubID not Changed')
ELSE
  INSERT INTO MyTestTable (SomeField) VALUES('ClubID Changed')

[sapator]

ALL ok EXCEPT THIS

IF INSERTED.ClubID=DELETED.ClubID

what will happen if the have the same value, meaning changed but with the same value?

Thanks.

[Zvoni]

ALL ok EXCEPT THIS

IF INSERTED.ClubID=DELETED.ClubID

what will happen if the have the same value, meaning changed but with the same value?

Thanks.

Then it's still UPDATED, but NOT CHANGED

Like code in any frontend/programming language you use

IF INSERTED.ClubID=DELETED.ClubID THEN 
  /* will only execute if still same VALUE */
ELSE
  /* Will only execute if different VALUES*/

The Status "UPDATE(ed)" (Trigger has fired) only applies to the TABLE and WHOLE RECORD itself,
somthing along the lines of "Oy! Something has happened in this record"

[sapator]

I don't know why I'm confusing this, probably because I'm running back and forth today on the job so I have to look it calmly.

Thanks.

[Zvoni]

An Idea for your tests

Create your Trigger with Body
.... IF UPDATED(ClubID) THEN DoSomething /*Insert a record in a temp-table and then look it up */

Then from outside
UPDATE MyTable
SET ClubID=10 /* THIS SHOULD Pass the IF-Clause into the THEN-Clause! */
, SomeOtherField='SomeValue'
WHERE ClubID=10

and look up your temp. Table

repeat, but with this:
UPDATE MyTable
SET SomeOtherField='SomeOtherValue' /* THIS SHOULD NOT Pass the IF-Clause into the THEN-Clause! */
WHERE ClubID=10

and look again in your temp. table

As far as i understood the mechanism:
UPDATED(SomeColumn) just returns a flag "Yes, a Value for this column has been passed"
It doesn't COMPARE the old with the new value

[sapator]

Cool.
Thanks

(can't rep but thanks again)