TRIGGER Definition

Description

A trigger is created by defining a trigger target, condition and action to be performed in the CREATE TRIGGER statement. A trigger is a database object that performs a defined action when a specific event occurs in the target table.

Syntax

CREATE TRIGGER trigger_name

[ STATUS { ACTIVE | INACTIVE } ]

[ PRIORITYkey ]

event_time event_type[ event_target ]

[ IFcondition ]

EXECUTE [ AFTER | DEFERRED ] action [ ; ]

event_time:

   • BEFORE

   • AFTER

   • DEFERRED

event_type: 

   • INSERT

   • STATEMENT INSERT 

   • UPDATE

   • STATEMENT UPDATE 

   • DELETE

   • STATEMENT DELETE

   • ROLLBACK

   • COMMIT

event_target: 

   • ONtable_name

   • ONtable_name [ (column_name) ]

condition: 

   • expression

action: 

 • REJECT    

 • INVALIDATE TRANSACTION 

 •  PRINT message_string

 •  INSERT statement

 •  UPDATE statement

 •  DELETE statement  

• trigger_name  : Specifies the name of the trigger to be defined.
• [ STATUS { ACTIVE | INACTIVE } ] : Defines the state of the trigger (if not defined, the default value is ACTIVE).
  • If ACTIVE state is specified, the trigger is executed every time the corresponding event occurs.
  • If INACTIVE state is specified, the trigger is not executed even when the corresponding event occurs. The state of the trigger can be modified. For more information, see Altering TRIGGER Definition section.
• [ PRIORITY key ] : Specifies a trigger priority if multiple triggers are called for an event. key must be a floating point value that is not negative. If the priority is not defined, the lowest priority 0 is assigned. Triggers having the same priority are executed in a random order. The priority of triggers can be modified. For more information, see Altering TRIGGER Definition section.
• event_time : Specifies the point of time when the conditions and actions are executed. BEFORE, AFTER or DEFERRED can be specified. For more information, see the Event Time section.
• event_type : Trigger types are divided into a user trigger and a table trigger. For more information, see the TRIGGER Event Type section.
• event_target : An event target is used to specify the target for the trigger to be called. For more information, see the TRIGGER Event Target section.
• condition : Specifies the trigger condition. For more information, see the TRIGGER Condition section.
• action : Specifies the trigger action. For more information, see the TRIGGER Action section.

Example

The following is an example of creating a trigger that rejects the update if the number of medals won is smaller than 0 when an instance of the participant table is updated.

As shown below, the update is rejected if you try to change the number of gold medals that Korea won in the 2004 Olympic Games to a negative number.

CREATE TRIGGER medal_trigger

BEFORE UPDATE ON participant

IF new.gold < 0 OR new.silver < 0 OR new.bronze < 0

EXECUTE REJECT;

UPDATE participant SET gold = -5 WHERE nation_code = 'KOR'

AND host_year = 2004;

ERROR: The operation has been rejected by trigger "medal_trigger".