DROP TRIGGER statement

Removes a trigger from the database.

Syntax

DROP TRIGGER [ IF EXISTS ] [ owner.] [ table-name.]trigger-name

Remarks

Use the IF EXISTS clause if you do not want an error returned when the DROP statement attempts to remove a database object that does not exist.

Privileges

To drop a trigger on a table, one of the following must be true:

You are the owner of the table.
You have ALTER privilege on the table.
You have the ALTER ANY TABLE system privilege.
You have the ALTER ANY OBJECT system privilege.

To drop a trigger on a view owned by someone else, you must have either the ALTER ANY VIEW or ALTER ANY OBJECT system privilege.

Side effects

Automatic commit.

Standards

ANSI/ISO SQL Standard
DROP TRIGGER comprises part of optional ANSI/ISO SQL Language Feature T211, "Basic trigger capability". The IF EXISTS clause is not in the standard.

Example

This example creates, and then drops, a trigger called emp_upper_postal_code to ensure that postal codes are in upper case before updating the Employees table. If the trigger does not exist, an error is returned.

CREATE TRIGGER emp_upper_postal_code
BEFORE UPDATE OF PostalCode
ON GROUPO.Employees
REFERENCING NEW AS new_emp
FOR EACH ROW
WHEN ( ISNUMERIC( new_emp.PostalCode ) = 0 )
BEGIN
   -- Ensure postal code is uppercase (employee might be 
   -- in Canada where postal codes contain letters)
   SET new_emp.PostalCode = UPPER(new_emp.PostalCode)
END;
DROP TRIGGER MyTrigger;