DROP TRIGGER

DROP TRIGGER — remove a trigger

Syntax

Dropping a Trigger at Schema Level

DROP TRIGGER [ IF EXISTS ] [schema.]trigger_name

DROP TRIGGER [schema.]trigger_name [ IF EXISTS ]

Dropping a Trigger at Database Level

DROP TRIGGER [ IF EXISTS ] trigger_name on database;

DROP TRIGGER trigger_name [IF EXISTS] on database;

Note that DDL triggers do not belong to any schema, so the schema prefix is not allowed.

Description

Use the DROP TRIGGER statement to remove an existing trigger definition. The trigger must be in the current schema, or you must have the DROP ANY TRIGGER system privilege. To execute this command the current user must be the owner of the table for which the trigger is defined.

Parameters

IF EXISTS: If the trigger does not exist, and you specify IF EXISTS, NuoDB does not generate an error. If not specified and the trigger does not exist, an error is generated.
schema: Optional. The name of the schema that owns the trigger to be dropped. If schema is not provided, the trigger must be owned by the current schema.
trigger_name: Name of an existing trigger.

Examples

Example 1: Drop trigger that exists in the current schema

CREATE TABLE table1 (column1 string);
SET DELIMITER @
CREATE TRIGGER trg_t1_before_insert FOR table1 BEFORE INSERT
AS
    new.column1='Column1_string';
END_TRIGGER;
@
SET DELIMITER ;

INSERT INTO table1 VALUES ('1st record');
SELECT * FROM table1;

    COLUMN1
 --------------
 Column1_string

DROP TRIGGER trg_t1_before_insert;
INSERT INTO table1 VALUES ('2nd record');
SELECT * FROM table1;

    COLUMN1
 --------------
 Column1_string
 2nd record

Example 2: Drop trigger that does not exist

DROP TRIGGER nosuchtrigger;
  trigger HOCKEY.NOSUCHTRIGGER not defined

DROP TRIGGER IF EXISTS nosuchtrigger;
DROP TRIGGER nosuchtrigger IF EXISTS;

Example 3: Drop database level trigger

CREATE TABLE user_log (user_name STRING, operation_type STRING, timestamp TIMESTAMP WITHOUT TIME ZONE);
CREATE TRIGGER ddl_trigger_after_create_user ON DATABASE AFTER CREATE USER AS INSERT INTO user_log VALUES (DDL_OBJECT_NAME, DDL_OPERATION_TYPE, now()); END_TRIGGER;
CREATE USER alice PASSWORD 'blueCat123';
SELECT * FROM user_log;

 USER_NAME  OPERATION_TYPE          TIMESTAMP
 ---------- --------------- --------------------------

   ALICE      create_user   2023-11-02 15:22:31.167847

DROP TRIGGER ddl_trigger_after_create_user ON DATABASE;
CREATE USER bob PASSWORD 'greenDog456';
SELECT * FROM user_log;

 USER_NAME  OPERATION_TYPE          TIMESTAMP
 ---------- --------------- --------------------------

   ALICE      create_user   2023-11-02 15:22:31.167847