DROP TRIGGER (Delete a trigger)

Function

Deletes a trigger.

Privileges

Owner of a trigger
This user can delete his or her own triggers.
Users with the DBA privilege
These users can delete their own triggers and triggers owned by other users.

Format

DROP TRIGGER [authorization-identifier.]trigger-identifier[WITH PROGRAM]

Operands

authorization-identifier
Specifies the authorization identifier of the owner of the trigger to be deleted.
The default is the authorization identifier of the user issuing DROP TRIGGER.
trigger-identifier
Specifies the name of the trigger to be deleted.
WITH PROGRAM
When deleting a trigger, this option is specified to nullify an SQL object for which functions, procedures, and triggers that use the trigger are in effect.
If WITH PROGRAM is omitted, and if there is an SQL object for which functions, procedures, and triggers that use a trigger are in effect, the trigger cannot be deleted.

Common rule

  1. DROP TRIGGER cannot be executed from within a Java procedure if the following conditions are met:
    • The SQL object being executed is nullified or deleted.
    • The Java procedure being executed is deleted.

Notes

  1. DROP TABLE cannot be specified from an X/Open compliant UAP running under OLTP.
  2. If an SQL object for which functions, procedures, or triggers are in effect is nullified by specifying WITH PROGRAM, any information associated with the nullified functions, procedures, or triggers in the SQL_ROUTINE_RESOURCES dictionary table is deleted.
  3. Before executing the SQL object associated with the function, procedure, or trigger that was nullified by specifying WITH PROGRAM, you need to recreate the function, procedure, or trigger by executing ALTER ROUTINE, ALTER PROCEDURE, or ALTER TRIGGER.

Example

Delete the trigger (TRIG1).

DROP TRIGGER TRIG1