Oracle PL/SQL – Delete Trigger example

This article shows you how to use DROP TRIGGER to delete a trigger.


-- delete  a trigger
DROP TRIGGER trigger_name;
Note
In DROP TRIGGER statement, recommend to specify a schema name before trigger name. If you don’t specify a schema name, then database will assume that the trigger is in your own schema.

1. Table + Trigger

1.1 Create a table and a trigger on it.

demo_data

create table demo_data
(
id number(5) primary key,
project_name varchar2(10)
);
trg_drop_example

CREATE OR REPLACE TRIGGER trg_drop_example
BEFORE
     UPDATE OR DELETE OR INSERT
ON demo_data
  FOR EACH ROW 

BEGIN

  dbms_output.put_line('trg_drop_example Trigger called.');
  
END;

2. DROP TRIGGER example

2.1 Check the trigger status :


SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS where upper(TRIGGER_NAME) = 'TRG_DROP_EXAMPLE';
TRIGGER_NAME

STATUS
TRG_DROP_EXAMPLE ENABLED

2.2 Delete the trigger.


DROP TRIGGER trg_drop_example;

-- output
-- Trigger trg_drop_example dropped.

2.3 Check the status again.


SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS where upper(TRIGGER_NAME) = 'TRG_DROP_EXAMPLE';

-- output
-- no rows selected.

References

  1. Oracle – DROP TRIGGER
  2. Oracle – PL/SQL Triggers
  3. Check trigger status
author image

Dhaval Dadhaniya

Dhaval Dadhaniya is a software engineer by profession and reader/writter by passion. He is working in a well reputed MNC as an applications Developer with 5 years of experience. He have good understanding and knowledge of Java, Database, Spring, Hibernate and exploring other technologies related to Software development. Befriend him on Facebook Read all published posts by

Comments

avatar