http://sqle.ru/index.php?action=history&feed=atom&title=Oracle_PL%2FSQL_Tutorial%2FTrigger%2FDisable_Enable_Trigger
Oracle PL/SQL Tutorial/Trigger/Disable Enable Trigger - История изменений
2026-07-15T12:11:53Z
История изменений этой страницы в вики
MediaWiki 1.30.0
http://sqle.ru/index.php?title=Oracle_PL/SQL_Tutorial/Trigger/Disable_Enable_Trigger&diff=3573&oldid=prev
в 13:45, 26 мая 2010
2010-05-26T13:45:46Z
<p></p>
<table class="diff diff-contentalign-left" data-mw="interface">
<tr style="vertical-align: top;" lang="ru">
<td colspan="1" style="background-color: white; color:black; text-align: center;">← Предыдущая</td>
<td colspan="1" style="background-color: white; color:black; text-align: center;">Версия 13:45, 26 мая 2010</td>
</tr><tr><td colspan="2" style="text-align: center;" lang="ru"><div class="mw-diff-empty">(нет различий)</div>
</td></tr></table>
http://sqle.ru/index.php?title=Oracle_PL/SQL_Tutorial/Trigger/Disable_Enable_Trigger&diff=3574&oldid=prev
Admin: 1 версия
2010-05-26T10:07:20Z
<p>1 версия</p>
<p><b>Новая страница</b></p><div>== Alter trigger to disable it or enable it==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
SQL><br />
SQL> CREATE TABLE logit<br />
2 ( event VARCHAR2(400) );<br />
Table created.<br />
SQL><br />
SQL> create or replace trigger tr_log_ddl<br />
2 before create or drop on SCHEMA<br />
3 begin<br />
4 insert into logit values ("Object Name: "|| ora_dict_obj_name<br />
5 || " Type: " || ora_dict_obj_type<br />
6 || " Event: " || ora_sysevent<br />
7 || " " || sysdate) ;<br />
8 end ;<br />
9 /<br />
Trigger created.<br />
SQL> show errors<br />
No errors.<br />
SQL><br />
SQL> create table dummy (col1 varchar2(10) ) ;<br />
Table created.<br />
SQL><br />
SQL> drop table dummy ;<br />
Table dropped.<br />
SQL><br />
SQL> Select * from LOGIT;<br />
EVENT<br />
--------------------------------------------------------------------------------<br />
Object Name: DUMMY Type: TABLE Event: CREATE 24-JUL-08<br />
Object Name: DUMMY Type: TABLE Event: DROP 24-JUL-08<br />
SQL><br />
SQL> alter trigger tr_log_ddl disable ;<br />
Trigger altered.<br />
SQL><br />
SQL> create table dummy (col1 varchar2(10) ) ;<br />
Table created.<br />
SQL><br />
SQL> select * from logit ;<br />
EVENT<br />
--------------------------------------------------------------------------------<br />
Object Name: DUMMY Type: TABLE Event: CREATE 24-JUL-08<br />
Object Name: DUMMY Type: TABLE Event: DROP 24-JUL-08<br />
SQL><br />
SQL> select object_name, status from user_objects where object_type = "TRIGGER" ;<br />
OBJECT_NAME<br />
--------------------------------------------------------------------------------<br />
STATUS<br />
-------<br />
HRC_ORG_SITE_TRIG<br />
INVALID<br />
CAPTURE_TRACE_FILES<br />
INVALID<br />
WHAT_COLUMN<br />
VALID<br />
<br />
OBJECT_NAME<br />
--------------------------------------------------------------------------------<br />
STATUS<br />
-------<br />
LOGOFF_LOG_TRIGGER<br />
INVALID<br />
APPLICATION_USERS_IOI<br />
INVALID<br />
STUDENT_TRIG<br />
VALID<br />
<br />
OBJECT_NAME<br />
--------------------------------------------------------------------------------<br />
STATUS<br />
-------<br />
TR_LOG_DDL<br />
VALID<br />
<br />
7 rows selected.<br />
SQL><br />
SQL> alter trigger tr_log_ddl enable ;<br />
Trigger altered.<br />
SQL><br />
SQL><br />
SQL> drop trigger tr_log_ddl;<br />
Trigger dropped.<br />
SQL><br />
SQL> drop table logit;<br />
Table dropped.<br />
SQL><br />
SQL><br />
SQL><br />
SQL></source><br />
<br />
<br />
<br />
== Disable a trigger==<br />
<br />
<p>You can stop a trigger from firing by disabling it using the ALTER TRIGGER statement.</p><br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
ALTER TRIGGER before_employee_salary_update DISABLE;</source><br />
<br />
<br />
<br />
== Enable and disable triggers==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
SQL><br />
SQL><br />
SQL> SET VERIFY OFF;<br />
SQL> SET SERVEROUTPUT ON;<br />
SQL><br />
SQL> DECLARE<br />
2 cur INTEGER;<br />
3 done EXCEPTION;<br />
4 cnt NUMBER := 0;<br />
5 BEGIN<br />
6 FOR user_trg IN (SELECT trigger_name FROM user_triggers)<br />
7 LOOP<br />
8 BEGIN<br />
9 cnt := cnt + 1;<br />
10 cur := DBMS_SQL.open_cursor;<br />
11<br />
12 DBMS_SQL.parse (cur,"ALTER TRIGGER " || user_trg.trigger_name || " ENABLE",DBMS_SQL.native);<br />
13 DBMS_SQL.parse (cur,"ALTER TRIGGER " || user_trg.trigger_name || " DISABLE",DBMS_SQL.native);<br />
14 EXCEPTION<br />
15 WHEN OTHERS<br />
16 THEN<br />
17 DBMS_OUTPUT.put_line (SQLCODE || "-" || SQLERRM);<br />
18 DBMS_SQL.close_cursor (cur);<br />
19 END;<br />
20 END LOOP;<br />
21 END;<br />
22 /<br />
PL/SQL procedure successfully completed.</source><br />
<br />
<br />
<br />
== Enable a trigger==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
ALTER TRIGGER before_employee_salary_update ENABLE;</source></div>
Admin