Oracle PL/SQL/System Tables Views/user triggers

Материал из SQL эксперт
Перейти к: навигация, поиск

Check the status of the trigger

   <source lang="sql">

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> 


 </source>


If trigger exists, drop trigger

   <source lang="sql">

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> 


 </source>


Query user_triggers

   <source lang="sql">

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> 


 </source>


Query user_triggers with trigger name

   <source lang="sql">

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> 


 </source>


Restore trigger create statement from user_trigger table

   <source lang="sql">

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> -- 


 </source>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL/System_Tables_Views/user_triggers&oldid=2145»