DROP TRIGGER (SQL)
Synopsis
DROP TRIGGER [IF EXISTS] name [ FROM table ]
Description
The DROP TRIGGER command deletes a trigger. If you wish to modify an existing trigger you must invoke DROP TRIGGER to delete the old version of the trigger before invoking CREATE TRIGGER.
DROP TABLE drops all triggers associated with that table.
Privileges and Locking
The DROP TRIGGER command is a privileged operation. The user must have %DROP_TRIGGER administrative privilege to execute DROP TRIGGER. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have %DROP_TRIGGER privileges.
The user must have %ALTER privilege on the specified table. If the user is the Owner (creator) of the table, the user is automatically granted %ALTER privilege for that table. Otherwise, the user must be granted %ALTER privilege for the table. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have required %ALTER privilege needed to change the table definition for 'Schema.TableName'.
You can use the GRANT command to assign %DROP_TRIGGER and %ALTER privileges, if you hold appropriate granting privileges.
In embedded SQL, you can use the $SYSTEM.Security.Login()Opens in a new tab 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, see %SYSTEM.SecurityOpens in a new tab.
-
DROP TRIGGER cannot be used on a table projected from 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'.
-
DROP TRIGGER cannot be used on a table projected from a deployed persistent class. This operation fails with an SQLCODE -400 error with the %msg Unable to execute DDL that modifies a deployed class: 'classname'.
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. If the trigger name is unqualified, the trigger schema name defaults to the same schema as the table schema, as specified in the FROM clause. If the trigger name is unqualified, and there is no FROM clause, or the table name is also unqualified, the trigger schema defaults to the default schema name; schema search paths are not used. If both names are qualified, the trigger schema name must be the same as the table schema name. A schema name mismatch results in an SQLCODE -366 error; this should only occur when both the trigger name and the table name are qualified and they specify different schema names.
In InterSystems 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:
-
If no FROM clause is specified, and InterSystems IRIS locates a unique trigger in the schema that matches the specified name, InterSystems IRIS deletes the trigger.
-
If a FROM clause is specified, and InterSystems IRIS locates a unique trigger in the schema that matches both the specified name and the FROM table name, InterSystems IRIS deletes the trigger.
-
If no FROM clause is specified, and InterSystems IRIS locates more than one trigger that matches the specified name, InterSystems IRIS issues an SQLCODE -365 error.
-
If InterSystems IRIS locates no trigger that matches the specified name, either for the table specified in the FROM clause or, if there is no FROM clause, for any table in the schema, InterSystems IRIS issues an SQLCODE -363 error.
Arguments
IF EXISTS
An optional argument that suppresses the error if the command is executed on a nonexistent trigger.
name
The name of the trigger to be deleted. A trigger name may be qualified or unqualified; if qualified, its schema name must match the table’s schema name.
FROM table
An optional argument that specifies the table the trigger is to be deleted from. If the FROM clause is specified, only the table is searched for the named trigger. If the FROM clause is not specified, the entire schema specified in name is searched for the named trigger.
Examples
The following example deletes a trigger named Trigger_1 associated with any table in the system-wide default schema. (The initial default schema is SQLUser):
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 system-wide default schema. If a trigger named Trigger_3 is found, but it is not associated with Patient, InterSystems IRIS 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