TRIGGER Event Type

Description

• Instance events : An event type whose unit of operation is an instance. The types of instance events are as follows:
• • INSERT
  • UPDATE
  • DELETE
• Statement events : If you define a statement event as an event type, the trigger is called only once when the trigger starts even when there are multiple objects (instances) affected by the given statement (event). The types of statement events are as follows:
  • STATEMENT INSERT
  • STATEMENT UPDATE
  • STATEMENT DELETE
• Other events : COMMIT and ROLLBACK cannot be applied to individual instances.
• • COMMIT
  • ROLLBACK

Example 1

The following is an example of using an instance event. The example trigger is called by each instance affected by the database update. For example, if the score values of five instances in the history table are modified, the trigger is called five times. If you want the trigger to be called only once, before the first instance of the score column is updated, use the STATEMENT UPDATE type as in example 2.

CREATE TRIGGER example

...

BEFORE UPDATE ON history(score)

...

Example 2

The following is an example of using a statement event. If you define a statement event, the trigger is called only once before the first instance gets updated even when there are multiple instances affected by the update.

CREATE TRIGGER example

...

BEFORE STATEMENT UPDATE ON history(score)

...

Caution

• You must specify the event target when you define an instance or statement event as the event type.
• COMMIT and ROLLBACK cannot have an event target.