|
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). |
|
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 )
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.
(19 Mar '18, 03:58)
Frank Vestjens
Replies hidden
> 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.
(19 Mar '18, 07:44)
Breck Carter
|
