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  

Example

The following example shows how to create 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".