DROP TRIGGER statement

Use the DROP TRIGGER statement to remove a trigger definition from the database.

This statement is an extension to the ANSI/ISO standard for SQL.


1  DROP TRIGGER? IF EXISTS?   owner  .  trigger
Element Description Restrictions Syntax
owner Name of the owner of the trigger Must own the trigger Owner name
trigger Name of the trigger to drop The trigger must exist in the local database Identifier


You must be the owner of the trigger or have the DBA privilege to drop the trigger. Dropping a trigger removes the text of the trigger definition and the executable trigger from the database. The row describing the specified trigger is deleted from the systriggers system catalog table.

If you include the optional IF EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if no trigger of the specified name is registered in the current database.

Dropping an INSTEAD OF trigger on a complex view (a view with columns from more than one table) revokes any privileges on the view that the owner of the trigger received automatically when creating the trigger, and also revokes any privileges that the owner of the trigger granted to other users. (Dropping a trigger on a simple view does not revoke any privileges.)

The following statement drops the items_pct trigger:
DROP TRIGGER items_pct;

If a DROP TRIGGER statement appears inside an SPL routine that is called by a data manipulation (DML) statement, the database server returns an error.

When multiple triggers are defined on the same table or view for the same triggering event, the order in which the triggers execute is not guaranteed. If you have a preferred sequence of execution, but the triggers are executing in some other sequence, you might wish to drop all of the triggers except the one that you want to run first, and then re-create the other triggers in the relative order in which you want them to execute, so that they are listed in the system catalog in the intended order of execution.