on 2018 Mar 16 9:00 AM
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 )
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
> 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.
User | Count |
---|---|
79 | |
11 | |
10 | |
8 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.