_TriggerLevel

_TriggerLevel lets you know if a trigger is firing because an event is taking place on the table directly, or if the trigger is firing because it was called from another trigger.

Usage

nLevel = _TRIGGERLEVEL

Parameter	Value	Meaning
nLevel	0	No trigger is in effect.
	1	A top-level trigger event is executing.
	2 or more	A trigger procedure is running within another trigger.

Using the Relational Integrity Builder or your own homegrown version, one trigger can call another. In a one-to-many relationship with cascaded deletion enabled, deleting a parent record fires the delete trigger procedure of the parent, which in turn attempts to delete the child records. This fires the delete trigger in the child table. _TriggerLevel in the parent procedure is 1; it is 2 when it reaches the child. Similarly, in a cascaded update, a change to the parent's primary key fires the update trigger procedure first of the parent, at _TriggerLevel 1, then the child's update trigger, at _TriggerLevel 2.We can envision many circumstances where it would be important to know if the individual trigger is being fired, or if the trigger is being fired as part of a cascade. In the classic order entry scenario of an order header and many order detail records, you may want the delete trigger of the order detail record to confirm that the user really wants to delete a line item, perhaps with a MessageBox(). If, however, an entire order is to be deleted, you don't want to ask the order entry clerk to confirm every detail line deletion, but rather ask once at the order level to confirm that the entire order is to be deleted. Checking _TriggerLevel in the order detail table's delete trigger will tell you if the individual line is being deleted, or if the entire order is on its way out.

Example

IF _TRIGGERLEVEL > 1  && called from another trigger