Caché SQL Reference
DROP TRIGGER
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

Deletes a trigger.
Synopsis
DROP TRIGGER name FROM table
Arguments
name The name of the trigger to be deleted.
FROM table Optional — The table the trigger is to be deleted from. If the FROM clause is not specified, the entire schema is searched for the named trigger.
Description
The DROP TRIGGER statement deletes a trigger.
Privileges and Locking
The DROP TRIGGER command is a privileged operation. Prior to using DROP TRIGGER it is necessary for your process to have %DROP_TRIGGER administrative privilege. Failing to do so results in an SQLCODE –99 error (Privilege Violation). You can use the GRANT command to assign %DROP_TRIGGER privileges, if you hold appropriate granting privileges.
In embedded SQL, you can use the $SYSTEM.Security.Login() method to log in as a user with appropriate privileges:
   DO $SYSTEM.Security.Login("_SYSTEM","SYS")
   &sql(      )
You must have the %Service_Login:Use privilege to invoke the $SYSTEM.Security.Login method. For further information, refer to %SYSTEM.Security in the InterSystems Class Reference.
DROP TRIGGER cannot be used on a table created by defining a persistent class, unless the table class definition includes [DdlAllowed]. Otherwise, the operation fails with an SQLCODE -300 error with the %msg DDL not enabled for class 'Schema.tablename'.
The DROP TRIGGER statement acquires a table-level lock on table. This prevents other processes from modifying the table’s data. This lock is automatically released at the conclusion of the DROP TRIGGER operation.
FROM Clause
A trigger and its table must reside in the same schema. A trigger name may be qualified or unqualified. If unqualified, the trigger schema defaults to the table schema, as specified in the FROM clause. If there is no FROM clause, or the table name is unqualified, the trigger schema defaults to the system default schema. If both the trigger name and the table name are qualified, they must both specify the same schema or Caché issues an SQLCODE -366 error.
In Caché SQL, a trigger name must be unique within its schema for a specific table. Thus it is possible to have more than one trigger in a schema with the same name. The optional FROM clause is used to determine which trigger to delete:
Examples
The following example deletes a trigger named Trigger_1 associated with any table in the default schema (usually SQLUser is the default schema):
DROP TRIGGER Trigger_1
The following example deletes a trigger named Trigger_2 associated with any table in the A schema.
DROP TRIGGER A.Trigger_2
The following example deletes a trigger named Trigger_3 associated with the Patient table in the default schema. If a trigger named Trigger_3 is found, but it is not associated with Patient, Caché issues an SQLCODE -363 error.
DROP TRIGGER Trigger_3 FROM Patient
The following examples all delete a trigger named Trigger_4 associated with the Patient table in the Test schema.
DROP TRIGGER Test.Trigger_4 FROM Patient
DROP TRIGGER Trigger_4 FROM Test.Patient
DROP TRIGGER Test.Trigger_4 FROM Test.Patient
See Also