ALTER TRIGGER

Description

In the trigger definition, STATUS and PRIORITY options can be changed by using the ALTER statement. If you need to alter other parts of the trigger (event targets or conditional expressions), you must delete and then re-create the trigger.

Syntax

ALTER TRIGGERtrigger_name trigger_option [ ; ]

trigger_option :

? STATUS { ACTIVE | INACTIVE }

? PRIORITYkey

trigger_name : Specifies the name of the trigger to be changed.

trigger_option :

STATUS { ACTIVE | INACTIVE } : Changes the status of the trigger.

PRIORITYkey : Changes the priority.

Example

The following is an example of creating the medal_trig trigger and then changing its state to INACTIVE and its priority to 0.7.

CREATE TRIGGER medal_trig

STATUS ACTIVE

BEFORE PDATE ON participant

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

EXECUTE REJECT;

ALTER TRIGGER medal_trig STATUS INACTIVE;

ALTER TRIGGER medal_trig PRIORITY 0.7;

Caution

Only one option can be specified in a single ALTER TRIGGER statement.

To change a table trigger, you must be the trigger owner or granted the ALTER privilege on the table where the trigger belongs.

A user trigger can only be changed by its owner. For more information on these options, see the CREATE TRIGGER (Syntax) section. The key specified together with the PRIORITY option must be a non-negative floating point value.