Oracle PL/SQL – Enable and Disable Triggers

摘要: This article shows you how to use ALTER TRIGGER and ALTER TABLE to enable and disable triggers.

This article shows you how to use ALTER TRIGGER and ALTER TABLE to enable and disable triggers.

-- enable / disable a trigger
ALTER TRIGGER trigger_name ENABLE;
ALTER TRIGGER trigger_name DISABLE;
-- enable / disable all triggers for a specific table
ALTER TABLE table_name ENABLE ALL TRIGGERS;
ALTER TABLE table_name DISABLE ALL TRIGGERS;

1. Table + Triggers

1.1 Create a table and two triggers on it.

test_data
--Creating test_data table.
CREATE TABLE test_data
  id number(5) primary key,
  test_result number(5)
);
trg_test_a
SET SERVEROUTPUT ON;
CREATE OR REPLACE TRIGGER trg_test_a
BEFORE
     UPDATE OR DELETE OR INSERT
ON test_data
  FOR EACH ROW 
BEGIN
  -- business logic will be here to perform on any insert/update/delete
  dbms_output.put_line('trg_test_a Trigger called.');
END;
trg_test_b
SET SERVEROUTPUT ON;
CREATE OR REPLACE TRIGGER trg_test_b
BEFORE
     UPDATE OR DELETE OR INSERT
ON test_data
  FOR EACH ROW 
BEGIN
  dbms_output.put_line('trg_test_b Trigger called.');
END;

When you create triggers, they will be ENABLED by default.

SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS where upper(TRIGGER_NAME) in ('TRG_TEST_A','TRG_TEST_B');
TRIGGER_NAME STATUS
trg_test_a ENABLED
trg_test_b ENABLED

2. ALTER TRIGGER – Enable and disable a Trigger

2.1 Example to disable a trigger trg_test_a

ALTER TRIGGER trg_test_a DISABLE;

2.2 Check trigger status.

SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS where upper(TRIGGER_NAME) = 'TRG_TEST_A';
TRIGGER_NAME STATUS
TRG_TEST_A DISABLED
Note
Sometimes you will need to DISABLE trigger in some conditions like,

  1. An object that the trigger references is not available.
  2. You must perform a large data load and want it to proceed quickly without firing triggers.
  3. You are loading data into the table to which the trigger applies.

2.3 Example to enable a disabled trigger trg_test_a

ALTER TRIGGER trg_test_a ENABLE;

2.4 Check trigger status again.

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

3. ALTER TABLE – Enable and disable all Triggers

3.1 Example to disbale all triggers for a specific table test_data

ALTER TABLE test_data DISABLE ALL TRIGGERS;

3.2 Check triggers status.

SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS where upper(TRIGGER_NAME) IN ('TRG_TEST_A','TRG_TEST_B');
TRIGGER_NAME STATUS
TRG_TEST_A DISABLED
TRG_TEST_B DISABLED

3.3 Example to enable all triggers for a specific table test_data

ALTER TABLE test_data ENABLE ALL TRIGGERS;

3.4 Check triggers status again.

SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS where upper(TRIGGER_NAME) IN ('TRG_TEST_A','TRG_TEST_B');
TRIGGER_NAME STATUS
TRG_TEST_A ENABLED
TRG_TEST_B ENABLED

References

  1. ALTER TRIGGER examples
  2. Database administrator’s Guide:- Enabling and Disabling Triggers
  3. PL/SQL Triggers

上一篇: Oracle PL/SQL – Delete Trigger example
下一篇: Oracle PL/SQL Before INSERT Trigger example
 评论 ( What Do You Think )
名称
邮箱
网址
评论
验证
   
 

 


  • 微信公众号

  • 我的微信

站点声明:

1、一号门博客CMS,由Python, MySQL, Nginx, Wsgi 强力驱动

2、部分文章来源于互联网, 若有侵权, 联系邮箱:summer@yihaomen.com, 同时欢迎大家注册用户,主动发布文章.

3、鄂ICP备14001754号-3, 鄂公网安备 42280202422812号