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).

asked 16 Mar, 09:00

Frank's gravatar image

Frank
1897817
accept rate: 33%


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 )
permanent link

answered 16 Mar, 10:23

Breck%20Carter's gravatar image

Breck Carter
27.5k471627903
accept rate: 21%

edited 16 Mar, 10:35

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, 03:58) Frank
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, 07:44) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×62

question asked: 16 Mar, 09:00

question was seen: 84 times

last updated: 19 Mar, 07:44