DROP TRIGGER (Delete a trigger)
Function
DROP TRIGGER 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.]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 re-create the function, procedure, or trigger by executing ALTER ROUTINE, ALTER PROCEDURE, or ALTER TRIGGER.
Example
Delete the trigger (TRIG1).
DROP TRIGGER TRIG1