DROP TRIGGER (Delete a trigger)
Function
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.]trigger-identifier[WITH PROGRAM]
- 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
- 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
- DROP TABLE cannot be specified from an X/Open compliant UAP running under OLTP.
- 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.
- 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