I have a table called AssociateMembers.AssociateMembershipApplication containing a column called FoundSSO, which is a unique identifier for a person (but not the primary key).
In brief the concept is that a person applying for membership of an organisation will cause a new row in this table. As part of their application, staff will check if they've been a member before, and if so, add their previous SSO into this column. Then, the Update trigger will search for the PersonID relating to that found SSO, and add it to the record.
As I'm having trouble getting the trigger to do what I want, I've simplified it to troubleshoot, and now have this code:
CREATE TRIGGER AssociateMembers.trgApplicationSSOUpdated
ON AssociateMembers.AssociateMembershipApplication
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DeletedSSO VARCHAR(20)
DECLARE @InsertedSSO VARCHAR(20)
IF UPDATE(FoundSSO)
BEGIN
SELECT @DeletedSSO=d.FoundSSO, @InsertedSSO=i.FoundSSO
FROM INSERTED i
INNER JOIN DELETED d ON i.AssociateMemberApplicationID = d.AssociateMemberApplicationID
WHERE i.FoundSSO IS NOT NULL
AND i.FoundSSO<>d.FoundSSO
PRINT 'InsertedSSO:'
PRINT ISNULL(@InsertedSSO,'NULL')
PRINT 'DeletedSSO:'
PRINT ISNULL(@DeletedSSO,'NULL')
END
END
GO
When I issue this update command:
UPDATE AssociateMembers.AssociateMembershipApplication
SET FOUNDSSO='ball1138'
WHERE AssociateMemberApplicationID=64
The row is being updated with the new SSO, but both the variables hold NULL.
My first thought was that there wasn't an update happening, but I've double-checked that there is indeed a change. However, this did reveal that the trigger works when there was already a value in FoundSSO and it's changed, but not when the existing FoundSSO was NULL and we're updating it to hold a value for the first time - which will be the situation most of the time.
What am I missing about the logic of my trigger? And how should I update it to work when the user updates the row from a NULL in this column to a value?
Now that we can see what you actually want to achieve from your answer:
You have a number of fatal flaws in your trigger:
OR a IS NULL AND b IS NOT NULL
etc, or use the new IS DISTINCT FROM
. Do not use ISNULL
as that means that ''
and NULL
compare as the same.UPDATE
function only tells you if the column was present in the UPDATE
statement, not if any rows were affected or if the column value ended being set to the same thing.UPDATE
statement in your new answer. You need to always use the table alias on the first line to make the update, otherwise it just cross-joins the whole table again.CREATE OR ALTER TRIGGER AssociateMembers.trgApplicationSSOUpdated
ON AssociateMembers.AssociateMembershipApplication
AFTER UPDATE
AS
SET NOCOUNT ON;
IF NOT UPDATE(FoundSSO) OR NOT EXISTS (SELECT 1 FROM inserted)
RETURN; -- early bailout
UPDATE ama -- use alias
SET FoundPersonID = CASE WHEN i.FoundSSO IS NOT NULL THEN P.PersonID END
FROM AssociateMembers.AssociateMembershipApplication ama
INNER JOIN INSERTED i ON ama.AssociateMemberApplicationID = i.AssociateMemberApplicationID
INNER JOIN DELETED d ON i.AssociateMemberApplicationID = d.AssociateMemberApplicationID
INNER JOIN People.People p ON i.FoundSSO = p.SSO
WHERE i.FoundSSO IS DISTINCT FROM d.FoundSSO;
Exactly why you need a trigger anyway is not clear: your application should just do the right thing in the first place.
Note also that because you are using INNER JOIN
, if the FoundSSO
is not matched then you may get no changes. You may want to do instead:
LEFT JOIN People.People p ON i.FoundSSO = p.SSO