Determine the columns that have been modified in a row in a table

[fvestjens]

MS SQL has something called COLUMNS_UPDATED which is a varbinary bitpattern that indicates the columns in a table that have been inserted or updated.

Is there something similar available in SQL Anywhere? Is there maybe another way to retreive the same result?

I want a list of column names that have been modified in an insert/update statement (TRIGGER).

[Breck_Carter]

It is possible to combine the IF INSERTING and IF UPDATING syntax from the Watcom SQL version of triggers in SQL Anywhere with the funky IF UPDATE ( column-name ) syntax from the Transact SQL version of triggers.

Note that this trigger is coded in the Watcom SQL syntax (CREATE TRIGGER ... BEGIN END), and it just appropriates the IF UPDATE ( column-name ) syntax...

CREATE TABLE t ( 
   col1  INTEGER PRIMARY KEY,
   col2  INTEGER,
   col3  INTEGER );

CREATE TRIGGER triu_t
   BEFORE INSERT, UPDATE ON t
   REFERENCING OLD AS old_t NEW AS new_t
   FOR EACH ROW
BEGIN
   CASE 
      WHEN INSERTING THEN MESSAGE STRING ( CURRENT TIMESTAMP, ' INSERTING' ) TO CONSOLE;
      WHEN UPDATING  THEN MESSAGE STRING ( CURRENT TIMESTAMP, ' UPDATING'  ) TO CONSOLE;
   END;
   IF UPDATE ( col1 ) THEN  MESSAGE STRING ( CURRENT TIMESTAMP, ' UPDATE ( col1 )'  ) TO CONSOLE; END IF;
   IF UPDATE ( col2 ) THEN  MESSAGE STRING ( CURRENT TIMESTAMP, ' UPDATE ( col2 )'  ) TO CONSOLE; END IF;
   IF UPDATE ( col3 ) THEN  MESSAGE STRING ( CURRENT TIMESTAMP, ' UPDATE ( col3 )'  ) TO CONSOLE; END IF;
END; 

INSERT t VALUES ( 1, 2, 3 );
UPDATE t SET col2 = 11;
UPDATE t SET col2 = 22, col3 = 33;

2018-03-16 10:16:09.165 INSERTING
2018-03-16 10:16:09.165 UPDATE ( col1 )
2018-03-16 10:16:09.165 UPDATE ( col2 )
2018-03-16 10:16:09.165 UPDATE ( col3 )
2018-03-16 10:16:09.180 UPDATING
2018-03-16 10:16:09.180 UPDATE ( col2 )
2018-03-16 10:16:09.180 UPDATING
2018-03-16 10:16:09.180 UPDATE ( col2 )
2018-03-16 10:16:09.180 UPDATE ( col3 )

[fvestjens]

Thanks.

However I'm looking for a more general solution without using the column names. I don't want to add all 263 column names in the trigger.

What I'm looking for is saving every modification made on a column in a row to a log table. Maybe it can be done in a different way then I'm trying to do now.

[Breck_Carter]

> saving every modification made on a column in a row to a log table

The SQL Server COLUMNS_UPDATED doesn't even come close to "saving every modification made on a column"... it just tells you the row positions of columns that were modified. You would have to code the column names if you wanted to get the modified values.

You might find this approach helpful: Triggering an Audit Trail.