Oracle PL/SQL/System Tables Views/user triggers
Содержание
Check the status of the trigger
SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY,
3 fname VARCHAR2(50),
4 lname VARCHAR2(50)
5 );
Table created.
SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "E", "F");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z");
1 row created.
SQL>
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER emp_trig
2 BEFORE UPDATE OF fname
3 ON emp
4 FOR EACH ROW
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE("First Name "||:OLD.fname||" has change to "||:NEW.fname);
7 END;
8 /
Trigger created.
SQL>
SQL>
SQL> SELECT trigger_name, status FROM user_triggers;
TRIGGER_NAME STATUS
------------------------------ --------
EMP_TRIG ENABLED
INVENTORY_TRG DISABLED
DEMO_BIFER DISABLED
DEPT_SAL_TRG DISABLED
SYSTEMALTERUSER DISABLED
USAGE_START DISABLED
USAGE_STOP DISABLED
USER_LOGON_MODULE_CHECK DISABLED
8 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
If trigger exists, drop trigger
SQL>
SQL> BEGIN
2 FOR i IN (SELECT null FROM user_triggers WHERE trigger_name = "INSERTemp") LOOP
3 EXECUTE IMMEDIATE "DROP TRIGGER InsertBooksemp";
4 END LOOP;
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
Query user_triggers
SQL>
SQL>
SQL> select trigger_name, trigger_type,
2 triggering_event, trigger_body
3 from user_triggers
4 where rownum < 50;
TRIGGER_NAME TRIGGER_TYPE
------------------------------ ----------------
TRIGGERING_EVENT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TRIGGER_BODY
--------------------------------------------------------------------------------
AW_DROP_TRG AFTER EVENT
DROP
BEGIN
aw_drop_proc(ora_dict_obj_type, ora_dict_obj_name, ora_dict_obj_owner);
SQL>
Query user_triggers with trigger name
SQL>
SQL>
SQL> CREATE TABLE employees
2 ( employee_id number(10) not null,
3 last_name varchar2(50) not null,
4 email varchar2(30),
5 hire_date date,
6 job_id varchar2(30),
7 department_id number(10),
8 salary number(6),
9 manager_id number(6)
10 );
Table created.
SQL>
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
2 values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1004, "Kyte", "tkyte@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
2 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1008, "Oracle", "wvelasq@g.ru", SYSDATE, "DBA", 20000, 4, 1006);
1 row created.
SQL>
SQL>
SQL>
SQL> create or replace trigger MyTrigger
2 before insert or update
3 of department_id
4 on employees
5 referencing old as old_value
6 new as new_value
7 for each row
8 when ( new_value.department_id <> 80 )
9 begin
10 :new_value.rumission_pct := 0;
11 end;
12 /
Warning: Trigger created with compilation errors.
SQL>
SQL>
SQL> select trigger_type, triggering_event, when_clause, trigger_body
2 from user_triggers
3 where trigger_name = "MYTRIGGER";
TRIGGER_TYPE TRIGGERING_EVENT
---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------
WHEN_CLAUSE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TRIGGER_BODY
--------------------------------------------------------------------------------
BEFORE EACH ROW INSERT OR UPDATE
new_value.department_id <> 80
begin
:new_value.rumission_pct := 0;
end;
SQL>
SQL> drop table employees;
Table dropped.
SQL>
Restore trigger create statement from user_trigger table
SQL>
SQL>
SQL>
SQL>
SQL> select
2 "create or replace trigger "" || trigger_name || """ || chr(10)||
3 decode( substr( trigger_type, 1, 1 ),"A", "AFTER", "B", "BEFORE", "I", "INSTEAD OF" ) ||chr(10) ||triggering_event || chr(10) ||
4 "ON "" || table_owner || ""."" || table_name || """ || chr(10) ||
5 decode( instr( trigger_type, "EACH ROW" ), 0, null,"FOR EACH ROW" ) || chr(10) ,
6 trigger_body
7 from user_triggers
8 where trigger_name = upper("YourTriggerName")
9 and rownum < 50
10 /
no rows selected
SQL>
SQL> --