Value referenced by NEW on an unchanged column in an UPDATE trigger

I have a table Group_Members with a DELETE trigger and an UPDATE trigger. The DELETE trigger updates some rows in A hence invoking the UPDATE trigger.

Here’s the DELETE trigger code:

UPDATE Group_Members
SET Role = 'Admin'
WHERE JoinDate = (SELECT MIN(JoinDate)
                  FROM Group_Members
                  WHERE GroupName = OLD.GroupName);

Here’s the invoked UPDATE trigger code:

INSERT INTO Group_Notifications (Destination, Source, "Group", Type)
VALUES (OLD.Username, NEW.Affector, OLD.GroupName, 'Membership Promotion');

My question is: what would be the value referenced by NEW in the UPDATE trigger given the column value has not been changed i.e. in this scenario, what would be the value referenced by NEW.Affector?

Answer

new has the column values as they will be after the UPDATE. So if affector isn’t changed by the UPDATE, its value after the UPDATE will be the same value it had before. In that case new.affector has the same value as old.affector.

Advertisement

Answer