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.

asked 30 Jun, 05:54

Frank's gravatar image

Frank
539111833
accept rate: 18%

1

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

(30 Jun, 06:32) Volker Barth

It does not show the trigger names and times

(30 Jun, 08:44) Frank

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

answered 30 Jun, 07:58

Breck%20Carter's gravatar image

Breck Carter
30.6k494676989
accept rate: 20%

edited 30 Jun, 09:23

1

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

(30 Jun, 11:20) Volker Barth
Replies hidden
1


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

(30 Jun, 14:23) Breck Carter

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

(01 Jul, 03:21) Volker Barth

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.

(01 Jul, 18:16) Sako
Replies hidden
1

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.

(02 Jul, 02:05) Volker Barth

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.

permanent link

answered 01 Jul, 03:29

Volker%20Barth's gravatar image

Volker Barth
36.4k343504757
accept rate: 34%

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:

×72
×38

question asked: 30 Jun, 05:54

question was seen: 124 times

last updated: 02 Jul, 07:46