Sql-server – Insert if something changed other than specific column

insertsql-server-2012t-sqltrigger

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.

Best Answer

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

Related Solutions

PostgreSQL – How to Disable/Enable Trigger Inside Function for the Same Table

One option would be to:

Jack up the security on the table so normal users can't directly access it at all, and remove the protecting trigger.
Create an updatable view on the table which is accessible by normal users, and add an INSTEAD OF trigger to protect the column from updates (assuming you want it still visible; if not, simply omit it completely from the view definition.)
Create a function with definer's rights which bypasses the view and directly updates the column in the base table.

PostgreSQL – Modify Function for Use in Multiple Triggers

Answers so far still suffer from one or the other shortcoming.

Use the USING clause of EXECUTE to pass values. Do not concatenate the text representation of values into the SQL string. Expensive and error-prone.
Properly escape all identifiers when used in dynamic SQL to defend against sneaky errors with double quoted identifiers and possible SQL injection. Even if you think you do not need it. Do it on principal.

But this would be an error waiting to happen:

~~'_bit' || quote_ident(TG_TABLE_NAME)~~

For a table name "MyTable" this would produce _bit"MyTable" - syntactical nonsense. It must be:
```
quote_ident('_bit' || TG_TABLE_NAME)
```
Or use format('... %I ...', '_bit' || TG_TABLE_NAME)

The only correct solution is what you added to your question yourself:

CREATE OR REPLACE FUNCTION bitacoratabla()
  RETURNS trigger AS
$func$
BEGIN
   EXECUTE format('INSERT INTO %I SELECT $1.*', '_bit' || TG_TABLE_NAME)
   USING OLD;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

Detailed explanation:

INSERT with dynamic table name in trigger function

But why does it work with an additional column?

the other table has the old values and an additional column to store the date data

This happens to work as long as the additional column in the target table comes last because, quoting the manual on INSERT:

If no list of column names is given at all, the default is all the columns of the table in their declared order; or the first N column names, if there are only N columns supplied by the VALUES clause or query.

So you better be sure the additional column(s) come last in the table definition of the target table, and all other columns are listed in matching order, now and in all future - or you must add an explicit target column list (which would prevent a generic function to begin with).

Best Answer

Related Solutions

PostgreSQL – How to Disable/Enable Trigger Inside Function for the Same Table

PostgreSQL – Modify Function for Use in Multiple Triggers

But why does it work with an additional column?

Related Question