Hi,

Is there any way to find out what fired a trigger?
I have AFTER INSERT trigger for example.
I need to know that it was fired by a stored procedure, another trigger or
simple insert statement.

TIA,
Blueman

Triggers cannot be fired by anything but an operation
that changes a row; in this an Insert statement. Now
if that insert happens to be coded in a trigger or part
of another trigger's actions then that would still be an
insert, just not a simple case of a user/application coded
insert.

Translating the transaction log (-z) may reveal some
more of the context, but if it was caused by a stored
procedure you will have to figure that out from the
tracks left behind and not a record of a call statement.
[request level logging would have captured the Call
statement but if you didn't have that enabled that
opportunity was lost]

If this is reproducible then procedure profiling may
be able to help analyze this deeper [along with
request level logging].

"Blueman" <blue (AT) nomail (DOT) com> wrote

corrected that first paragraph ....

Triggers cannot be fired by anything but an operation
that changes a row; in this case an Insert statement. Now
if that insert happens to be coded in a stored procedure
or as part of another trigger's actions then that would still
be an insert, just not as simple a case as a user/application
coded insert.

Translating the transaction log (-z) may reveal some
more of the context, but if it was initiated by a stored
procedure you will have to figure that out from the
tracks left behind and not a record of a call statement.
[request level logging would have captured the Call
statement but if you didn't have that enabled that
opportunity was lost]

If this is reproducible then procedure profiling may
be able to help analyze this deeper [along with
request level logging].

"Blueman" <blue (AT) nomail (DOT) com> wrote

Hi,

Is there any way to find out what fired a trigger?
I have AFTER INSERT trigger for example.
I need to know that it was fired by a stored procedure, another trigger or
simple insert statement.

TIA,
Blueman

"Nick Elson [Sybase iAnywhere]" wrote in message
news:4aae5870$1 (AT) forums-1-dub (DOT) ..

Yes, of course. I wasn't so precise as I should be.
I need to know what called an insert statement, that fires a trigger.
In some cases I have triggers inserting rows into their own tables.
This automatically fires the after insert trigger.
So I need to know who inserted a row - a trigger (in that case
I don't watnt to do anything) or a stored procedure (or an application)
(in that case trigger should insert the second row and do a few another
things).
I thought that it's possible to read some stack or anything else
to figure out what inserted the row.
We have a lot of triggers to modify, so we want to do it well.

Yes, but we need that information while the server and applications are
working, so log translation is not the solution.

Regards,

Blueman

Okay that clears up my confusion ... somewhat ... and the answer is . . .
42? Is that it?

Mind if I ask why so many triggers are involved? To me triggers that
insert additional rows into the same table sounds like a pretty rare
thing to need to do.

O\W I understand your trigger logic needs to know if a prior instance of
the trigger itself caused the change .... and you want to prevent unbounded
or unnecessary recursion (not to mention the extra rows).

A **statement-level** trigger is probably part of a simpler answer but if
it must be a row-level trigger then read on . . .

Preventing trigger recursion can always be detected by including a state,
either in the row or in a companion table (tied 1-to-1 by primary) key
and checking for that.

Another approach is to invert you trigger logic and make it into a
search condition for an IF NOT Exists( select 1 from table where .....)
conditional to wrap you insert statement in. A related approach
would be to utilize the WHEN clause.

Something in the data itself may lend itself to this as well. For instance
some designs [though I don't like them usually] will mimick forms and
insert 15 item-detail rows from a before/after insert trigger. Ths solution
to that kind of design problem is often to normalize the schema a little
and put that kind of logic on a normalized header-table and leave the
details table triggerless (or at least just recursion free triggers). Such
a small (but totally natural) schema mod. can break the need to recurse
entirely.

If you were running 10.0.1 or higher you could maybe leverage the
INSTEAD OF style triggers ... but unless you are going to upgrade
soon then that may not be an option.

I don't know of any 'reading the stack' approach available to you.

"Blueman" <blue (AT) nomail (DOT) com> wrote

User "Nick Elson [Sybase iAnywhere]" <@nick@.@elson@@sybase@.@com@> wrote in
message news:4aafb3cb$1 (AT) forums-1-dub (DOT) ..

We have large database. A lot of tables are used to store data in
chronological order. We need to have continuity in the dates. Triggers
maintain the order in the data. We do prevent trigger recursion, it's not a
problem. The problem is that we want to log every important change in those
tables, without changes made by triggers. I can write a trigger that logs
every insert, update or delete. But I don't want to log changes made by
triggers.

There is a few hundreds of triggers, so the solution is only one - add
triggers that can determine what changed a data and log only data changed by
user, not a trigger. We don't have a time to change all existing triggers.

I heard about solution like this in SQL Server.
Thanks for your answers and hints. I'll try to find some solution.

Best regards,

Blueman.