How to log which triggers executed and in what sequence on insert, update or delete of a record

[fvestjens]

I'm looking for something to log what triggers have been executed and in what sequence when a record is inserted, modified or deleted.

I'm looking for something else then adding messages to every individual trigger.

[VolkerBarth]

And DBTRAN with option -t or -z does not suffice?

[fvestjens]

It does not show the trigger names and times

[Breck_Carter]

As Volker said, the transaction log records which rows were inserted, updated and deleted and in what order. It doesn't specifically record which triggers were executed, however.

There is no trigger trace feature built in to SQL Anywhere. You can build one using a CREATE TABLE to hold one row per trigger execution (current timestamp, table name, trigger name, triggering operation), and a CREATE EVENT to INSERT a row to record the trigger execution.

Add a TRIGGER EVENT statement to the trigger logic and pass the timestamp, etc, as EVENT_PARAMETER values. An event must be used because it needs to do a COMMIT and you can't code a COMMIT in a trigger; an event runs as a separate connection.

You can also turn the trace on and off by setting a CREATE VARIABLE Y/N in the application and checking the value in the trigger... a separate connection level variable value will exist for each application connection and it will be visible in all the procedures and triggers executing on the application connection.

The CREATE EVENT and TRIGGER EVENT statements provide a powerful tool for launching parallel processes in SQL Anywhere.

The following code shows (and hopefully clears up) some of the confusion surrounding the scope of EVENT_PARAMETER variables...

CREATE EVENT eee
HANDLER BEGIN
   DECLARE @p VARCHAR ( 128 );
   SET @p = COALESCE ( EVENT_PARAMETER ( '@p' ), 'NULL' );
   MESSAGE STRING ( '@p passed to event: ', @p ) TO CONSOLE;
   MESSAGE STRING ( CONNECTION_PROPERTY ( 'Name' ) ) TO CONSOLE;
END;

-- Testing...

BEGIN
   DECLARE @p VARCHAR ( 128 );
   DECLARE @v VARCHAR ( 254 );
   SET @p = 'hello';
   SET @v = 'world';
   MESSAGE STRING ( '@p before event: ', @p );
   TRIGGER EVENT eee ( @p = @v );
   MESSAGE STRING ( '@p after event: ', @p );
END;

@p before event: hello
@p after event: hello
@p passed to event: world
eee

[VolkerBarth]

Well, do you really need to log triggers that have been rolled back? Otherwise, you could just use the original connection to log the trigger's call...

[Breck_Carter]

@Frank: Forget all that EVENT stuff, just CALL a PROCEDURE that does all the same stuff as the EVENT... except the COMMIT.

[VolkerBarth]

No facepalming required IMHO, my question was a real one - because for diagnostic purposes one might also want to know whether a particular trigger failed and as such forced a rollback, and for such purposes an event would be necessary...

[Baron]

For me it was interesting the order of the messages! For the first moment I expected to see the messages in this order:

@ p before event: hello

@ p passed to event: world

eee

@ p after event: hello

Yes, because the event is executed in a new separate connection I can understand how is this order achieved, but the question is, does the order stay always the same, or it can change (according the thread allocation...).

For example if I add waitfor delay ('00:00:01') between TRIGGER EVENT eee ( @ p = @ v ) and MESSAGE STRING ( '@ p after event: ', @ p ), then I get the order as I expect.

[VolkerBarth]

Well, different connections do run in parallel, so you better do not make assumptions about a serial order... If you need to synchronize connections, mutexes may help, or the WAITFOR ... AFTER MESSAGE BREAK statement.

[VolkerBarth]

Just to add: In case you want to log the context in which the triggers were invoked (from a batch, procedure or the like), see that question and the sa_stack_trace() system procedure.