Oracle PL/SQL Tutorial/Trigger/Disable Enable Trigger — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Версия 16:45, 26 мая 2010
Содержание
Alter trigger to disable it or enable it
<source lang="sql">
SQL> SQL> CREATE TABLE logit
2 ( event VARCHAR2(400) );
Table created. SQL> SQL> create or replace trigger tr_log_ddl
2 before create or drop on SCHEMA 3 begin 4 insert into logit values ("Object Name: "|| ora_dict_obj_name 5 || " Type: " || ora_dict_obj_type 6 || " Event: " || ora_sysevent 7 || " " || sysdate) ; 8 end ; 9 /
Trigger created. SQL> show errors No errors. SQL> SQL> create table dummy (col1 varchar2(10) ) ; Table created. SQL> SQL> drop table dummy ; Table dropped. SQL> SQL> Select * from LOGIT; EVENT
Object Name: DUMMY Type: TABLE Event: CREATE 24-JUL-08 Object Name: DUMMY Type: TABLE Event: DROP 24-JUL-08 SQL> SQL> alter trigger tr_log_ddl disable ; Trigger altered. SQL> SQL> create table dummy (col1 varchar2(10) ) ; Table created. SQL> SQL> select * from logit ; EVENT
Object Name: DUMMY Type: TABLE Event: CREATE 24-JUL-08 Object Name: DUMMY Type: TABLE Event: DROP 24-JUL-08 SQL> SQL> select object_name, status from user_objects where object_type = "TRIGGER" ; OBJECT_NAME
STATUS
HRC_ORG_SITE_TRIG INVALID CAPTURE_TRACE_FILES INVALID WHAT_COLUMN VALID
OBJECT_NAME
STATUS
LOGOFF_LOG_TRIGGER INVALID APPLICATION_USERS_IOI INVALID STUDENT_TRIG VALID
OBJECT_NAME
STATUS
TR_LOG_DDL VALID
7 rows selected. SQL> SQL> alter trigger tr_log_ddl enable ; Trigger altered. SQL> SQL> SQL> drop trigger tr_log_ddl; Trigger dropped. SQL> SQL> drop table logit; Table dropped. SQL> SQL> SQL> SQL></source>
Disable a trigger
You can stop a trigger from firing by disabling it using the ALTER TRIGGER statement.
<source lang="sql">
ALTER TRIGGER before_employee_salary_update DISABLE;</source>
Enable and disable triggers
<source lang="sql">
SQL> SQL> SQL> SET VERIFY OFF; SQL> SET SERVEROUTPUT ON; SQL> SQL> DECLARE
2 cur INTEGER; 3 done EXCEPTION; 4 cnt NUMBER := 0; 5 BEGIN 6 FOR user_trg IN (SELECT trigger_name FROM user_triggers) 7 LOOP 8 BEGIN 9 cnt := cnt + 1; 10 cur := DBMS_SQL.open_cursor; 11 12 DBMS_SQL.parse (cur,"ALTER TRIGGER " || user_trg.trigger_name || " ENABLE",DBMS_SQL.native); 13 DBMS_SQL.parse (cur,"ALTER TRIGGER " || user_trg.trigger_name || " DISABLE",DBMS_SQL.native); 14 EXCEPTION 15 WHEN OTHERS 16 THEN 17 DBMS_OUTPUT.put_line (SQLCODE || "-" || SQLERRM); 18 DBMS_SQL.close_cursor (cur); 19 END; 20 END LOOP; 21 END; 22 /
PL/SQL procedure successfully completed.</source>
Enable a trigger
<source lang="sql">
ALTER TRIGGER before_employee_salary_update ENABLE;</source>