0

I need a trigger that update to Null a column everytime another column is changed.

This is what I have now:

ALTER TRIGGER [dbo].[ERROR_CLEAR]
ON [dbo].[contact]
AFTER UPDATE
AS BEGIN
SET NOCOUNT ON;
IF UPDATE (NAME) 
BEGIN
    UPDATE contact 
    SET ERROR = Null
    FROM contact c2 INNER JOIN Inserted I ON c2.ID = I.ID
    INNER JOIN Deleted D ON d.ID = c2.ID

    WHERE c2.NAME <> I.NAME
    AND I.NAME <> D.NAME

END 
END

I'm testing like this:

UPDATE contact SET NAME = 'John' WHERE NAME = 'Paul'

But its not working and I'm running out of ideas. Any help?

  • 2
    Because it is an AFTER UPDATE trigger, c2.Name = I.Name. Try to comment this condition c2.NAME <> I.NAME – Sabin Bio Apr 8 '16 at 13:23
  • Out of curiosity, is the trigger fired again when ERROR is updated or is that prevented somehow? – Lennart Apr 8 '16 at 13:35
  • 1
    @Lennart - See msdn.microsoft.com/en-us/library/ms190946.aspx for trigger recursion. – RLF Apr 8 '16 at 13:41
  • @Antonio what do you want to happen if the name is pdated to null (from not null) or to a not null value (from null)? Should the SET Error = Null happen in that case? And what about if the name is updated without the value changed? – ypercubeᵀᴹ Nov 18 '16 at 23:22
1

There are a few things that need to be fixed in your trigger:

  1. The WHERE condition of c2.NAME <> I.NAME will always be false since the inserted pseudo-table is the current definition of those same rows in the real table (at least in an AFTER Trigger). This one line was preventing anything else from happening.

  2. If it weren't for issue #1, you would be seeing "odd" behavior regarding what rows in the contact table were being updated by this Trigger. The issue here is that you have two independent references to the same table in the main UPDATE clause and the FROM clause. You have UPDATE contact (notice there is no table alias) and then FROM contact c2 (notice the c2 alias, making this a second reference to the same table. You can either remove the c2 alias from the FROM clause (such that both references are just contact), OR, what I recommend for readability is to keep the alias and use it in place of the table name in the UPDATE clause, such that it reads: UPDATE c2 ....

  3. Finally, when comparing strings to determine if there are any changes, you really need to use a binary collation (one ending in _BIN2 if available, else one ending in _BIN). This will prevent any "insensitive" options (such as case and/or accent) from masking changes, and it will also prevent string normalization from masking any changes.

Updated code:

ALTER TRIGGER [dbo].[ERROR_CLEAR]
ON [dbo].[contact]
AFTER UPDATE
AS BEGIN
  SET NOCOUNT ON;

  IF (UPDATE (NAME))
  BEGIN
    UPDATE con
    SET    con.ERROR = NULL
    FROM   contact con
    INNER JOIN DELETED del
            ON del.ID = con.ID
    WHERE  ins.NAME <> del.NAME COLLATE Latin1_General_100_BIN2
  END;
END;
0

This will update Error to NULL when names are not the same in your update. It will not handle NULLs. If name is nullable, then you will need to handle in the trigger.

ALTER TRIGGER [dbo].[ERROR_CLEAR]
ON [dbo].[contact]
AFTER UPDATE
AS BEGIN
SET NOCOUNT ON;
IF UPDATE (NAME) 
BEGIN
    UPDATE c2 
    SET ERROR = Null
    FROM contact c2 
    INNER JOIN Deleted D ON d.ID = c2.ID
    WHERE c2.NAME <> D.NAME

END 
END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.