4

I have a table with many columns but my snippet is simplified. I have the trigger shown below but how can I have it so that nothing is inserted if only the created_at column has changed?

ALTER TRIGGER [dbo].[t_upd_insert]
ON [dbo].[Player]
FOR INSERT, UPDATE
AS
SET XACT_ABORT, NOCOUNT ON;


BEGIN
INSERT INTO dbo.Changes (
            [name]
            [created_at]
       )
SELECT 
            ins.[name],
            ins.[created_at]                
FROM INSERTED ins 

I could do a WHERE name != ins.[name] for each column but there are a lot of columns and tables that need this; is there a shorter syntax?

EDIT I've tried adding

WHERE
      [name] != ins.[name] OR 
      [age] != ins.[age]
      ....

but that doesn't work either. I thought that would be the naive method but that doesn't work.

  • 1
    You can use either UPDATE() or COLUMNS_UPDATED() functions for this. – ypercubeᵀᴹ Jun 29 '16 at 11:35
  • 1
    Just note that those don't tell you if the value has actually changed as a result of the update. – Aaron Bertrand Jun 29 '16 at 11:55
2
ALTER TRIGGER [dbo].[t_upd_insert]
ON [dbo].[Player]
FOR INSERT, UPDATE
AS
SET XACT_ABORT, NOCOUNT ON;


BEGIN
IF (update(name))
BEGIN
  INSERT INTO dbo.Changes (
            [name]
            [created_at]
       )
  SELECT 
            ins.[name],
            ins.[created_at]                
  FROM INSERTED ins
END
  • 2
    This tells you that name was one of the columns in the SET list, but not whether the actual value has changed. – Aaron Bertrand Jun 29 '16 at 11:56
  • True if you really need to know if the value changed then I don't see other option than looking in the inserted table. – DoubleT28 Jun 29 '16 at 12:46
  • I've updated my question showing how I'm looking in the inserted table but even that isn't working. Any ideas why? – TryNCode Jun 29 '16 at 15:17
1

I cross-posted this to Stackoverflow and found a working answer, even if it meant having to type out the column names (which explains why my WHERE clause didn't work)

SELECT ins.[name], ins.[created_at]                
FROM INSERTED ins JOIN
     DELETED del
     ON ins.id = del.id
WHERE del.[name] <> ins.[name] OR 
      del.[age] <> ins.[age];

I won't accept the answer here for now in case someone has a way of not having to type every column name that could change instead of just the created_at column.

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.