- Oracleããªã¬ãŒã®æŠèŠ
- DMLããªã¬ãŒ
- ã·ã¹ãã ããªã¬ãŒ
- ããªã¬ãŒã®ã³ã³ãã€ã«
- ããªã¬ãŒäŸå€
- ããªã¬ãŒé åº
- ããªã¬ãŒã®æå¹å/ç¡å¹å
- ããªã¬ãŒæäœã®æš©å©
- æ å ±èŸæžã®ããªã¬ãŒ
ããªã¬ãŒã®æŠèŠ
ããªã¬ãŒã¯ãããŒã¿ããŒã¹ã«ä¿åãããååä»ãã®pl / sqlãããã¯ã§ãã
- èªåã§ããªã¬ãŒãåŒã³åºãããšã¯ã§ããŸãããããªã¬ãŒã¯åžžã«ç¹å®ã®ã€ãã³ãã§ã®ã¿èªåçã«èµ·åããŸãïŒæå¹ã«ãªã£ãŠããå ŽåïŒ
- ååž°ããªã¬ãŒãäœæããªãã§ãã ããã ã€ãŸããããšãã°ãæŽæ°åŸã®ããªã¬ãŒã§ãåãããŒãã«ã®æŽæ°ãå®è¡ãããŸãã ãã®å Žåãããªã¬ãŒã¯ã¡ã¢ãªããªããªããŸã§ååž°çã«èµ·åããŸãã
ããªã¬ãŒåé¡ïŒ
- DMLããªã¬ãŒïŒããŒãã«ãŸãã¯ãã¥ãŒããšïŒ
- ã·ã¹ãã ããªã¬ãŒïŒåç·ãŸãã¯ããŒã¿ããŒã¹ããšïŒ
- æ¡ä»¶ä»ãããªã¬ãŒïŒwhenæ¡ä»¶ãæã€ããªã¬ãŒïŒ
- ããªã¬ãŒã®ä»£ããã«ïŒäœæã³ãã³ãã®dmlãã¥ãŒããªã¬ãŒãŸãã¯ã·ã¹ãã ããªã¬ãŒïŒ
ããªã¬ãŒã䜿çšããçç±ïŒ
- ä»®æ³ãã£ãŒã«ãå€ãèªåçã«çæããã«ã¯
- ãã®ã³ã°çš
- çµ±èšãåéããã«ã¯
- ãã¥ãŒãdmlæäœã«é¢ä¿ããŠããå ŽåãããŒãã«ã®ããŒã¿ãå€æŽããã«ã¯
- ç¹å®ã®æéã«dmlæäœãé²ãã«ã¯
- ããŒãã«ã®äœææã«ç¢ºç«ãããèšè¿°çå¶çŽã§ã¯å®è£ ã§ããªãè€éãªããŒã¿æŽåæ§å¶çŽãå®è£ ããã«ã¯
- ããããçš®é¡ã®ç£æ»ãæŽçããã«ã¯
- ããŒã¿ããŒã¹å ã®æ å ±ãå€æŽããå Žåã®å¯ŸåŠæ¹æ³ã«ã€ããŠä»ã®ã¢ãžã¥ãŒã«ã«éç¥ããã«ã¯
- ããžãã¹ããžãã¯ãå®è£ ããã«ã¯
- ããŒã¿ããŒã¹ããŒãã«ã§ã«ã¹ã±ãŒãã¢ã¯ã·ã§ã³ãæŽçãããã
- ããŒã¿ããŒã¹ãŸãã¯ã¹ããŒãã®ã·ã¹ãã ã€ãã³ãã«å¿çããã«ã¯

plsql_trigger_sourceã¯ãã®ãããªæ§æäœã§ãã

æ§æsimple_dml_triggerãinstead_of_dml_triggerãcompound_dml_trigger ã system_triggerã¯ãèšäºã®å¯Ÿå¿ããã»ã¯ã·ã§ã³ã«èšèŒãããŠããŸãã
DMLããªã¬ãŒ
- DMLããªã¬ãŒã¯ãããŒãã«ãŸãã¯ãã¥ãŒã«å¯ŸããŠäœæãããã¬ã³ãŒãã®æ¿å ¥ãæŽæ°ããŸãã¯åé€æã«ããªã¬ãŒãããŸãã
- ããªã¬ãŒã¯ãããŒãã«ãå®çŸ©ãããŠããã¹ããŒããšã¯ç°ãªãã¹ããŒãã§äœæã§ããŸãã ãã®å Žåãããªã¬ãŒãå®è¡ãããšãã®çŸåšã®åè·¯ã¯ãããªã¬ãŒèªäœã®åè·¯ãšèŠãªãããŸãã
- MERGEæäœã¯ãè¡ã®æäœã«å¿ããŠãã¬ã³ãŒããå€æŽãæ¿å ¥ããŸãã¯åé€ããããªã¬ãŒãããªã¬ãŒããŸãã
- ããªã¬ãŒã¯ãã©ã³ã¶ã¯ã·ã§ã³ã®äžéšã§ãããããªã¬ãŒã®ãšã©ãŒã¯æäœãããŒã«ããã¯ããããªã¬ãŒã®ããŒãã«å€æŽã¯ãã©ã³ã¶ã¯ã·ã§ã³ã®äžéšã«ãªããŸãã
- ãã©ã³ã¶ã¯ã·ã§ã³ãããŒã«ããã¯ããããšãããªã¬ãŒã®å€æŽãããŒã«ããã¯ãããŸãã
- ããªã¬ãŒã§ã¯ãDDLããã³ãã©ã³ã¶ã¯ã·ã§ã³ç®¡çã¹ããŒãã¡ã³ãã¯çŠæ¢ãããŠããŸãïŒäŸå€ã¯ã¹ã¿ã³ãã¢ãã³ãã©ã³ã¶ã¯ã·ã§ã³ã§ãïŒã
Simple_dml_triggerã³ã³ã¹ãã©ã¯ãïŒ

ããã§ã dml_event_clauseïŒ

referencing_clauseïŒ

trigger_edition_clauseïŒ

trigger_bodyïŒ

æ·»ä»ãªããžã§ã¯ãã«å¿ããŠã次ã®ããã«åé¡ãããŸãã
- ããŒãã«ã®äž
- ãã¥ãŒå ïŒããªã¬ãŒã®ä»£ããïŒ
èµ·åã€ãã³ãå¥ïŒ
- ã¬ã³ãŒãã®æ¿å ¥ïŒæ¿å ¥ïŒ
- ã¬ã³ãŒãã®æŽæ°ïŒæŽæ°ïŒ
- ã¬ã³ãŒããåé€ïŒåé€ïŒ
ã¹ã³ãŒãå¥ïŒ
- ããŒã ã¬ãã«ïŒã¹ããŒãã¡ã³ãã¬ãã«ã®ããªã¬ãŒïŒ
- è¡ã¬ãã«ã®ããªã¬ãŒ
- è€åããªã¬ãŒ
å¿çæéå¥ïŒ
- æäœåïŒåïŒ
- æäœåŸïŒåŸïŒ
ã¯ãã¹ãšãã£ã·ã§ã³ããªã¬ãŒ - ãšãã£ã·ã§ã³éã®çžäºäœçšã«äœ¿çšãããŸããããšãã°ãæ°ãããšãã£ã·ã§ã³ã«ãªããã£ãŒã«ãããä»ã®ãã£ãŒã«ãã«ããŒã¿ã転éããã³å€æããŸãã
ããªã¬ãŒãæ©èœããæäœã決å®ããæ¡ä»¶è¿°èªïŒ
è¿°èª | 説æ |
---|---|
æ¿å ¥ | æ¿å ¥æäœã§ããªã¬ãŒãèµ·åããå Žåã¯True |
æŽæ°äž | æŽæ°æäœã§ããªã¬ãŒãèµ·åããå Žåã¯True |
æŽæ°ïŒãåãïŒ | ç¹å®ã®ãã£ãŒã«ãã«åœ±é¿ããæŽæ°æäœã§ããªã¬ãŒãèµ·åããå Žåã¯True |
åé€äž | åé€æäœã§ããªã¬ãŒãèµ·åãããå Žåã¯True |
ãããã®è¿°èªã¯ãããŒã«åŒã䜿çšã§ããå Žæã§ããã°ã©ãã§ã䜿çšã§ããŸãã
äŸ
CREATE OR REPLACE TRIGGER t BEFORE INSERT OR UPDATE OF salary, department_id OR DELETE ON employees BEGIN CASE WHEN INSERTING THEN DBMS_OUTPUT.PUT_LINE('Inserting'); WHEN UPDATING('salary') THEN DBMS_OUTPUT.PUT_LINE('Updating salary'); WHEN UPDATING('department_id') THEN DBMS_OUTPUT.PUT_LINE('Updating department ID'); WHEN DELETING THEN DBMS_OUTPUT.PUT_LINE('Deleting'); END CASE; END;
æ¬äŒŒããŒã
å€æŽãããã¬ã³ãŒãã®ãã£ãŒã«ãã«ã¢ã¯ã»ã¹ããå€æŽåã®ãã£ãŒã«ãã®å€ãšå€æŽåŸã®ãã£ãŒã«ãã®å€ãååŸã§ããç䌌ã¬ã³ãŒãããããŸãã ãããã¯ãå€ããšã³ããªãšæ°ãããšã³ããªã§ãã åç §æ§é ã䜿çšããŠãååãå€æŽã§ããŸãã ãããã®ãšã³ããªã®æ§é ã¯ãtablenameïŒ rowtypeã§ãã ãããã®ã¬ã³ãŒãã¯ãè¡ã¬ãã«ããªã¬ãŒãŸãã¯è€åããªã¬ãŒïŒã¬ã³ãŒãã¬ãã«ã»ã¯ã·ã§ã³ãå«ãïŒã®ã¿ã«ãããŸãã
ããªã¬ãŒæäœ | OLD.column | NEWãã³ã©ã |
---|---|---|
æ¿å ¥ | ãã« | æ°ããäŸ¡å€ |
æŽæ°ãã | å€ãæå³ | æ°ããäŸ¡å€ |
åé€ãã | å€ãæå³ | ãã« |
å¶éïŒ
- ç䌌ã¬ã³ãŒãã§ã¯ãã¬ã³ãŒãå šäœã®ã¬ãã«ã®æäœã¯çŠæ¢ãããŠããŸãïŒïŒnew = null;ïŒ
- å€ãã¬ã³ãŒãã®ãã£ãŒã«ãã®å€ãå€æŽããããšã¯ã§ããŸãã
- åé€æã«ããªã¬ãŒãèµ·åããå Žåãæ°ããã¬ã³ãŒãã®ãã£ãŒã«ãã®å€ãå€æŽããããšã¯ã§ããŸãã
- åŸããªã¬ãŒã§ã¯ãæ°ããã¬ã³ãŒãã®ãã£ãŒã«ãã®å€ãå€æŽã§ããŸãã
dmlããªã¬ãŒã®ä»£ããã«
- ãããã¯ãã¥ãŒçšã«äœæãããDMLæäœãæ©èœã«çœ®ãæããŸãã
- æŽæ°å¯èœã§ãªããã¥ãŒã®æ¿å ¥/æŽæ°ãŸãã¯åé€æäœãèš±å¯ããŸãã
代ããã«dml_triggerã³ã³ã¹ãã©ã¯ãïŒ

- ããã¯åžžã«è¡ã¬ãã«ã®ããªã¬ãŒã§ãã
- å€ãæ¬äŒŒãšã³ããªãšæ°ããæ¬äŒŒãšã³ããªã«ã¢ã¯ã»ã¹ã§ããŸãããå€æŽããããšã¯ã§ããŸãã
- dmlæäœããã¥ãŒã«çœ®ãæããŸã
äŸ
CREATE OR REPLACE VIEW order_info AS SELECT c.customer_id, c.cust_last_name, c.cust_first_name, o.order_id, o.order_date, o.order_status FROM customers c, orders o WHERE c.customer_id = o.customer_id; CREATE OR REPLACE TRIGGER order_info_insert INSTEAD OF INSERT ON order_info DECLARE duplicate_info EXCEPTION; PRAGMA EXCEPTION_INIT (duplicate_info, -00001); BEGIN INSERT INTO customers (customer_id, cust_last_name, cust_first_name) VALUES ( :new.customer_id, :new.cust_last_name, :new.cust_first_name); INSERT INTO orders (order_id, order_date, customer_id) VALUES ( :new.order_id, :new.order_date, :new.customer_id); EXCEPTION WHEN duplicate_info THEN RAISE_APPLICATION_ERROR ( num=> -20107, msg=> 'Duplicate customer or order ID'); END order_info_insert;
ãã¥ãŒã®ãã¹ããããããŒãã«åã®ããªã¬ãŒã®ä»£ãã
ãã¹ããããããŒãã«ãã¥ãŒã®ããªã¬ãŒãäœæã§ããŸãã ãã®ãããªããªã¬ãŒã«ã¯ãè¿œå ã®ç䌌ã¬ã³ãŒãããããŸã-芪ã¯ããã¥ãŒã®ã¬ã³ãŒãå šäœãåç §ããŸãïŒå€ãç䌌ã¬ã³ãŒããšæ°ããç䌌ã¬ã³ãŒãã¯ããã¹ããããããŒãã«ã®ãšã³ããªã®ã¿ãåç §ããŸãïŒ
ãã®ãããªããªã¬ãŒã®äŸ
-- Create type of nested table element: CREATE OR REPLACE TYPE nte AUTHID DEFINER IS OBJECT ( emp_id NUMBER(6), lastname VARCHAR2(25), job VARCHAR2(10), sal NUMBER(8,2) ); / -- Created type of nested table: CREATE OR REPLACE TYPE emp_list_ IS TABLE OF nte; / -- Create view: CREATE OR REPLACE VIEW dept_view AS SELECT d.department_id, d.department_name, CAST (MULTISET (SELECT e.employee_id, e.last_name, e.job_id, e.salary FROM employees e WHERE e.department_id = d.department_id ) AS emp_list_ ) emplist FROM departments d; -- Create trigger: CREATE OR REPLACE TRIGGER dept_emplist_tr INSTEAD OF INSERT ON NESTED TABLE emplist OF dept_view REFERENCING NEW AS Employee PARENT AS Department FOR EACH ROW BEGIN -- Insert on nested table translates to insert on base table: INSERT INTO employees ( employee_id, last_name, email, hire_date, job_id, salary, department_id ) VALUES ( :Employee.emp_id, -- employee_id :Employee.lastname, -- last_name :Employee.lastname || '@company.com', -- email SYSDATE, -- hire_date :Employee.job, -- job_id :Employee.sal, -- salary :Department.department_id -- department_id ); END;
ããªã¬ãŒæ¿å
¥ã¹ããŒãã¡ã³ããããªã¬ãŒãã
INSERT INTO TABLE ( SELECT d.emplist FROM dept_view d WHERE department_id = 10 ) VALUES (1001, 'Glenn', 'AC_MGR', 10000);
è€åDMLããªã¬ãŒ
ããŒãžã§ã³11Gã§å°å ¥ããããããã®ããªã¬ãŒã«ã¯ããã¹ãŠã®ã¿ã€ãã®DMLããªã¬ãŒã®åŠçã1ã€ã®ãããã¯ã«å«ãŸããŠããŸãã
Compound_dml_triggerã³ã³ã¹ãã©ã¯ãïŒ

ããã§ã compound_trigger_blockïŒ

ã¿ã€ãã³ã°ãã€ã³ãã»ã¯ã·ã§ã³ïŒ

ã¿ã€ãã³ã°ãã€ã³ãïŒ

tps_bodyïŒ

- ãã®ãããªããªã¬ãŒã¯ãããŸããŸãªã€ãã³ãããã³ããŸããŸãªæç¹ïŒãªãã¬ãŒã¿ãŒãŸãã¯è¡ã¬ãã«ãã€ãã³ãã®æ¿å ¥/æŽæ°/åé€ãã€ãã³ãã®ååŸïŒã§æ©èœããŸãã
- ãªãã©ã€ã³ãã©ã³ã¶ã¯ã·ã§ã³ã«ããããšã¯ã§ããŸããã
äž»ã«äœ¿çšãããŸãïŒ
- ã³ã¬ã¯ã·ã§ã³å ã®è¡ãåéããŠå¥ã®ããŒãã«ã«æ¿å ¥ããå®æçã«ãã³ãã«ã«æ¿å ¥ããŸã
- å€æŽããŒãã«ãšã©ãŒãåé¿ãã
è€åããªã¬ãŒæ§é
ããªã¬ãŒãããªã¬ãŒããã¹ããŒãã¡ã³ãã®å®è¡äžã«ååšããå€æ°ãå«ãå ŽåããããŸãã
ãã®ãããªããªã¬ãŒã«ã¯ã次ã®ã»ã¯ã·ã§ã³ãå«ãŸããŸãã
- æã®å
- ã¹ããŒãã¡ã³ãã®åŸ
- åè¡ã®å
- åè¡ã®åŸ
ãããã®ããªã¬ãŒã«ã¯åæåã»ã¯ã·ã§ã³ã¯ãããŸãããããããã®ç®çã«ã¯beforeã¹ããŒãã¡ã³ãã»ã¯ã·ã§ã³ã䜿çšã§ããŸãã
ããªã¬ãŒã«beforeã¹ããŒãã¡ã³ãã»ã¯ã·ã§ã³ãafterã¹ããŒãã¡ã³ãã»ã¯ã·ã§ã³ããªããã¹ããŒãã¡ã³ããã¬ã³ãŒãã«åœ±é¿ããªãå Žåããã®ãããªããªã¬ãŒã¯æ©èœããŸããã
å¶éïŒ
- åŒã¬ãã«ã®ã»ã¯ã·ã§ã³ã®å€ããæ°ããããŸãã¯èŠªã®æ¬äŒŒãšã³ããªã«ã¯ã¢ã¯ã»ã¹ã§ããŸããïŒã¹ããŒãã¡ã³ãã®åãšã¹ããŒãã¡ã³ãã®åŸïŒ
- å€æŽã§ããã®ã¯ãåè¡ã»ã¯ã·ã§ã³ã®åã«ããæ°ããæ¬äŒŒã¬ã³ãŒããã£ãŒã«ãã®å€ã®ã¿ã§ã
- ããã»ã¯ã·ã§ã³ã§çæãããäŸå€ã¯ãå¥ã®ã»ã¯ã·ã§ã³ã§ã¯åŠçã§ããŸããã
- gotoã¹ããŒãã¡ã³ãã䜿çšããå Žåãåãã»ã¯ã·ã§ã³å ã®ã³ãŒããæãå¿ èŠããããŸãã
äŸ
create or replace trigger tr_table_test_compound for update or delete or insert on table_test compound trigger v_count pls_integer := 0; before statement is begin dbms_output.put_line ( 'before statement' ); end before statement; before each row is begin dbms_output.put_line ( 'before insert' ); end before each row; after each row is begin dbms_output.put_line ( 'after insert' ); v_count := v_count + 1; end after each row; after statement is begin dbms_output.put_line ( 'after statement' ); end after statement; end tr_table_test_compound;
DMLããªã¬ãŒãå®çŸ©ããããã®åºæ¬çãªèŠå
- æŽæ°-ããªã¬ãŒããå€æŽå¯èœãªãã£ãŒã«ãã®ãªã¹ããæå®ã§ããŸã
- SQLå®è¡æ®µéã§ããªã¬ãŒãéå§ããã«ãããããŒãšWhen ...ã®ãã¹ãŠã®æ¡ä»¶ããã§ãã¯ãããŸãã
- ãªãã¬ãŒã¿ãŒãçµã¿èŸŒã¿é¢æ°ã®ã¿ã䜿çšã§ããå Žå
- åãçš®é¡ã®è€æ°ã®ããªã¬ãŒãäœæã§ããŸããå®è¡é åºã¯ããã©ã«ãã§ã¯å®çŸ©ãããŠããŸããããFOLLOWS TRIGGER_FIRSTæ§æã䜿çšããŠèšå®ã§ããŸã
- äžææ§å¶çŽã¯ãã¬ã³ãŒããå€æŽããããšããã€ãŸãå®è¡åããªã¬ãŒã®å®è¡åŸã«ãã§ãã¯ãããŸã
- å€æ°å®£èšã»ã¯ã·ã§ã³ã¯ãåèªDECLAREã«ãã£ãŠå®çŸ©ãããŸã
- ããªã¬ãŒã®ã¡ã€ã³ãããã¯ã¯ãéåžžã®PL / SQLãããã¯ãšåãã«ãŒã«ã«åŸããŸã
DMLããªã¬ãŒã®å¶é
- DDLã¹ããŒãã¡ã³ããå®è¡ã§ããŸããïŒã¹ã¿ã³ãã¢ãã³ãã©ã³ã¶ã¯ã·ã§ã³ã®ã¿ïŒ
- ãã©ã³ã¶ã¯ã·ã§ã³å¶åŸ¡ã¹ããŒãã¡ã³ãã§ã«ãŒãã³ãå®è¡ã§ããŸãã
- SERIALLY_REUSABLEããã±ãŒãžãžã®ã¢ã¯ã»ã¹æš©ããããŸãã
- ãµã€ãºã¯32Kãè¶ ããããšã¯ã§ããŸãã
- ã¿ã€ãLONGããã³LONG RAWã®å€æ°ã¯å®£èšã§ããŸãã
çªç¶å€ç°ãšã©ãŒè¡šORA-04091
è¡ã¬ãã«ã®ããªã¬ãŒã§ã¿ãŒã²ããè¡šã®ããŒã¿ãååŸãŸãã¯å€æŽããããšãããšãOracleã¯ãããèš±å¯ããããšã©ãŒORA-04091ãã¹ããŒããŸãã
ãã®åé¡ãåé¿ããã«ã¯ã次ã®ææ³ã䜿çšããŸãã
- æäœã¬ãã«ããªã¬ãŒã䜿çšãã
- ããªã¬ãŒã®ãªãã©ã€ã³ãã©ã³ã¶ã¯ã·ã§ã³
- ãµãŒãããŒãã£ã®æ§é ïŒããã±ãŒãžã¬ãã«ã®ã³ã¬ã¯ã·ã§ã³ïŒã䜿çšãã
- ååç©ããªã¬ãŒã䜿çšãã
- ããªã¬ãŒããæ©èœãåé€ããŠã¢ã«ãŽãªãºã èªäœãå€æŽãã
ã·ã¹ãã ããªã¬ãŒ
System_triggerã³ã³ã¹ãã©ã¯ãïŒ

ãã®ãããªããªã¬ãŒã¯ãã¹ããŒããŸãã¯ããŒã¿ããŒã¹å šäœã®ããããã«é©çšãããŸãã
ã·ã¹ãã ããªã¬ãŒãèµ·åããæç¹ã«ã¯ããã€ãã®ãªãã·ã§ã³ããããŸãã
- æäœãå®è¡ãããåïŒããªã¬ãŒãèµ·åããå ŽæïŒ
- æäœãå®äºããåŸïŒããªã¬ãŒãèµ·åããïŒ
- Createã¹ããŒãã¡ã³ããå®è¡ãã代ããã«
ã¹ããŒãããªã¬ãŒ
- åè·¯ã®ãŠãŒã¶ãŒææè ãã€ãã³ããèµ·åããïŒæäœãå®è¡ããïŒãšãã«åžžã«åäœãããã®ã€ãã³ãã§ããªã¬ãŒãèµ·åãããŸãã
- ä»ã®ãŠãŒã¶ãŒãäœæè ã®æš©éã§åŒã³åºãããããã·ãŒãžã£/é¢æ°ãèµ·åãããã®ããã·ãŒãžã£/é¢æ°ã§ã·ã¹ãã ããªã¬ãŒãäœæãããæäœãå®è¡ãããå Žåããã®ããªã¬ãŒã¯æ©èœããŸãã
ããªã¬ãŒã®äŸ
CREATE OR REPLACE TRIGGER drop_trigger BEFORE DROP ON hr.SCHEMA BEGIN RAISE_APPLICATION_ERROR ( num => -20000, msg => 'Cannot drop object'); END;
ããŒã¿ããŒã¹ããªã¬ãŒ
- ãã®ãããªããªã¬ãŒã¯ãããŒã¿ããŒã¹ãŠãŒã¶ãŒãããªã¬ãŒãäœæããã³ãã³ããå®è¡ãããšããªã¬ãŒãããŸãã
ããªã¬ãŒã®äŸ
CREATE OR REPLACE TRIGGER check_user AFTER LOGON ON DATABASE BEGIN check_user; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR (-20000, 'Unexpected error: '|| DBMS_Utility.Format_Error_Stack); END;
ããªã¬ãŒãäœæãã代ããã«
- ããã¯ãäœæã³ãã³ãã§èµ·åãããã®ã³ãã³ãã眮ãæããåè·¯å³ã¬ãã«ã®ããªã¬ãŒã§ãïŒã€ãŸããäœæã³ãã³ããå®è¡ãã代ããã«ãããªã¬ãŒã®æ¬äœãå®è¡ãããŸãïŒã
ããªã¬ãŒã®äŸ
CREATE OR REPLACE TRIGGER t INSTEAD OF CREATE ON SCHEMA BEGIN EXECUTE IMMEDIATE 'CREATE TABLE T (n NUMBER, m NUMBER)'; END;
ã·ã¹ãã ããªã¬ãŒå±æ§
å±æ§ | æ»ãå€ãšã¿ã€ã |
---|---|
ora_client_ip_address | Varchar2
ã¯ã©ã€ã¢ã³ãIPã¢ãã¬ã¹ äŸïŒ |
ora_database_name | Varchar2ïŒ50ïŒ
ããŒã¿ããŒã¹å äŸïŒ
|
ora_des_encrypted_paââssword | Varchar2
äœæãŸãã¯å€æŽãããDESæå·åãŠãŒã¶ãŒãã¹ã¯ãŒã äŸïŒ |
ora_dict_obj_name | Varchar2ïŒ30ïŒ
DDLæäœãå®è¡ããããªããžã§ã¯ãã®åå äŸïŒ |
ora_dict_obj_name_listïŒ
name_list OUT ora_name_list_t ïŒ | Pls_integer
ã³ãã³ãã«ãã£ãŠå€æŽããããªããžã§ã¯ãã®æ° Name_list-ã³ãã³ãã«ãã£ãŠå€æŽããããªããžã§ã¯ãã®ãªã¹ã äŸïŒ |
ora_dict_obj_owner | Varchar2ïŒ30ïŒ
DDLæäœã®ææè äŸïŒ |
ora_dict_obj_owner_listïŒ
owner_list OUT ora_name_list_t ïŒ | Pls_integer
ããŒã ã«ãã£ãŠå€æŽããããªããžã§ã¯ãã®ææè ã®æ° Owner_list-ã³ãã³ãã«ãã£ãŠå€æŽããããªããžã§ã¯ãã®ææè ã®ãªã¹ã äŸïŒ |
ora_dict_obj_type | Varchar2ïŒ20ïŒ
ddlæäœãå®è¡ããããªããžã§ã¯ãã®ã¿ã€ã äŸïŒ |
ora_granteeïŒ
user_list OUT ora_name_list_t ïŒ | Pls_integer
ä»äžæäœã«åå ããŠãããŠãŒã¶ãŒã®æ° User_list-ãããã®ãŠãŒã¶ãŒã®ãªã¹ã äŸïŒ |
ora_instance_num | æ°
ã€ã³ã¹ã¿ã³ã¹çªå· äŸïŒ |
ora_is_alter_columnïŒ
column_name IN VARCHAR2 ïŒ | ããŒã«å€
æå®ããããã£ãŒã«ããå€æŽæäœã«ãã£ãŠå€æŽãããå Žåã¯trueã ããã§ãªããã°åœ äŸïŒ |
ora_is_creating_nested_table | ããŒã«å€
çŸåšã®ã€ãã³ãããã¹ããããããŒãã«ã®äœæã§ããå Žåã¯trueã ããã§ãªããã°åœ äŸïŒ |
ora_is_drop_columnïŒ
column_name IN VARCHAR2 ïŒ | ããŒã«å€
æå®ããããã£ãŒã«ããåé€ãããå Žåã¯trueã ããã§ãªããã°åœ äŸïŒ |
ora_is_servererrorïŒ
error_number IN VARCHAR2 ïŒ | ããŒã«å€
error_numberçªå·ã§äŸå€ãã¹ããŒãããå Žåã¯trueã ããã§ãªããã°åœ äŸïŒ |
ora_login_user | Varchar2ïŒ30ïŒ
çŸåšã®ãŠãŒã¶ãŒå äŸïŒ |
ora_partition_pos | Pls_integer
ããŒãã«ãäœæããããã®ããªã¬ãŒã®ä»£ããã«ãããŒãã£ã·ã§ã³æ§é ãæ¿å ¥ã§ããã³ãã³ãã®SQLããã¹ãå ã®äœçœ® äŸïŒ |
ora_privilege_listïŒ
privilege_list OUT ora_name_list_t ïŒ | Pls_integer
ä»äžãŸãã¯åãæ¶ãæäœã«é¢ä¿ããç¹æš©ã®æ° Privilege_list-ãããã®ç¹æš©ã®ãªã¹ã äŸïŒ |
ora_revokeeïŒ
user_list OUT ora_name_list_t ïŒ | Pls_integer
åãæ¶ãæäœã«åå ããŠãããŠãŒã¶ãŒã®æ° User_list-ãããã®ãŠãŒã¶ãŒã®ãªã¹ã äŸïŒ |
ora_server_errorïŒ
PLS_INTEGERã®äœçœ® ïŒ | æ°
æå®ãããäœçœ®ã®ãšã©ãŒã¹ã¿ãã¯ã®ãšã©ãŒã³ãŒãã1ã¯ã¹ã¿ãã¯ã®å é äŸïŒ |
ora_server_error_depth | Pls_integer
ãšã©ãŒã¹ã¿ãã¯å ã®ãšã©ãŒã¡ãã»ãŒãžã®æ° äŸïŒ |
ora_server_error_msgïŒ
PLS_INTEGERã®äœçœ® ïŒ | Varchar2
æå®ãããå Žæã®ãšã©ãŒã¹ã¿ãã¯ã®ãšã©ãŒã¡ãã»ãŒãž äŸïŒ |
ora_server_error_num_paramsïŒ
PLS_INTEGERã®äœçœ® ïŒ | Pls_integer
æå®ããããšã©ãŒã¹ã¿ãã¯äœçœ®ã§çœ®ãæããããè¡ã®æ°ïŒïŒ s圢åŒã䜿çšïŒ äŸïŒ
|
ora_server_error_paramïŒ
PLS_INTEGERã®äœçœ®ã param IN PLS_INTEGER ïŒ | Varchar2
æå®ãããäœçœ®ã®ãšã©ãŒã¹ã¿ãã¯ã«ãããšã©ãŒã¡ãã»ãŒãžã®çœ®æããã¹ãïŒã¢ã«ãŠã³ã眮æããã¹ãã§è¿ããããã©ã¡ãŒã¿ãŒïŒ äŸïŒ |
ora_sql_txtïŒ
sql_text OUT ora_name_list_t ïŒ | Pls_integer
pl / sql sql_textã³ã¬ã¯ã·ã§ã³å ã®èŠçŽ ã®æ°ã sql_textãã©ã¡ãŒã¿ãŒèªäœã¯ãããªã¬ãŒãæ©èœããã³ãã³ãã®ããã¹ããè¿ããŸã äŸïŒ |
ora_sysevent | Varchar2ïŒ20ïŒ
ããªã¬ãŒãçºåããããŒã ã®åå äŸïŒ |
ora_with_grant_option | ããŒã«å€
ä»äžãªãã·ã§ã³ã§ç¹æš©ãä»äžãããŠããå Žåã¯trueã ããã§ãªãå Žåã¯falseã äŸïŒ |
ora_space_error_infoïŒ
error_number OUT NUMBERã error_type OUT VARCHAR2ã object_owner OUT VARCHAR2 table_space_name OUT VARCHAR2 object_name OUT VARCHAR2ã sub_object_name OUT VARCHAR2 ïŒ | ããŒã«å€
ã¹ããŒã¹äžè¶³ãåå ã§ãšã©ãŒãçºçããå Žåã¯trueã åºåãã©ã¡ãŒã¿ãŒã§ã¯ããªããžã§ã¯ãã«é¢ããæ å ±ã äŸïŒ |
ã·ã¹ãã ããªã¬ãŒã€ãã³ã
ã€ãã³ã | 説æ | å©çšå¯èœãªå±æ§ |
---|---|---|
èµ·ååŸ | ããŒã¿ããŒã¹ãèµ·åãããšãã DBã¬ãã«ã§ã®ã¿çºçããŸãã ãšã©ãŒãçºçãããšãã·ã¹ãã ãã°ã«æžã蟌ã¿ãŸãã | ora_sysevent
ora_login_user ora_instance_num ora_database_name |
ã·ã£ããããŠã³ããåã« | ãµãŒããŒãã·ã£ããããŠã³ããã»ã¹ãéå§ããåã DBã¬ãã«ã§ã®ã¿çºçããŸãã ãšã©ãŒæã«ã¯ãã·ã¹ãã ãã°ã«æžã蟌ã¿ãŸãã | ora_sysevent
ora_login_user ora_instance_num ora_database_name |
DB_ROLE_CHANGEã®åŸ | ããŒã«ãã¹ã¿ã³ãã€ãããã©ã€ããªã«ããŸãã¯ãã©ã€ããªããã¹ã¿ã³ãã€ã«å€æŽããåŸã«åããŠããŒã¿ããŒã¹ãèµ·åãããšãã
Data Guardæ§æã§ã®ã¿äœ¿çšãããDBã¬ãã«ã§ã®ã¿çºçããŸã | ora_sysevent
ora_login_user ora_instance_num ora_database_name |
ãµãŒããŒãšã©ãŒåŸ | ãšã©ãŒãçºçããå ŽåïŒæ¡ä»¶ãããå Žåãæ¡ä»¶ã§æå®ããããšã©ãŒã§ã®ã¿æ©èœããŸãïŒã ããªã¬ãŒã®æ¬äœã§ãšã©ãŒãçºçãããšãããèªäœãååž°çã«åŒã³åºããŸããã | ora_sysevent
ora_login_user ora_instance_num ora_database_name ora_server_error ora_is_servererror ora_space_error_info |
å€æŽå
å€æŽåŸ | alterã³ãã³ãã§ãªããžã§ã¯ããå€æŽãããå Žå | ora_sysevent
ora_login_user ora_instance_num ora_database_name ora_dict_obj_type ora_dict_obj_name ora_dict_obj_owner ora_des_encrypted_paââssword ïŒALTER USERã€ãã³ãã®å ŽåïŒ ora_is_alter_column ïŒALTER TABLEã€ãã³ãã®å ŽåïŒ ora_is_drop_column ïŒALTER TABLEã€ãã³ãã®å ŽåïŒ |
ããããããåã«
ããããåŸ | ãªããžã§ã¯ããåé€ãããšã | ora_sysevent
ora_login_user ora_instance_num ora_database_name ora_dict_obj_type ora_dict_obj_name ora_dict_obj_owner |
åæããåã«
åæåŸ | åæã³ãã³ããããªã¬ãŒããããšã | ora_sysevent
ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner |
é¢é£çµ±èšã®åã«
ã¢ãœã·ãšã€ãçµ±èšåŸ | é¢é£çµ±èšã³ãã³ããå®è¡ãããšã | ora_sysevent
ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_dict_obj_name_list ora_dict_obj_owner_list |
審æ»å
ç£æ»åŸ NOAUDITã®åã« ããŒãã£ããåŸ | auditãŸãã¯noauditãå®è¡ããå Žå | ora_sysevent
ora_login_user ora_instance_num ora_database_name |
ã³ã¡ã³ãããåã«
ã³ã¡ã³ãåŸ | ãªããžã§ã¯ãã«ã³ã¡ã³ããè¿œå ãããšã | ora_sysevent
ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner |
äœæå
äœæåŸ | ãªããžã§ã¯ããäœæãããšã | ora_sysevent
ora_login_user ora_instance_num ora_database_name ora_dict_obj_type ora_dict_obj_name ora_dict_obj_owner ora_is_creating_nested_table ïŒCREATE TABLEã€ãã³ãã®å ŽåïŒ |
DDLã®åã«
DDLåŸ | ããŒã¿ããŒã¹ã®å€æŽãå¶åŸ¡ãã¡ã€ã«ã®äœæãããŒã¿ããŒã¹ã®äœæãé€ããã»ãšãã©ã®DDLã³ãã³ãã§æ©èœããŸãã | ora_sysevent
ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner |
çµ±èšãåé¢ããåã«
çµ±èšåé¢åŸ | disassociate statisticsã³ãã³ããå®è¡ãããšã | ora_sysevent
ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_dict_obj_name_list ora_dict_obj_owner_list |
ä»äžå
ä»äžåŸ | grantã³ãã³ããçºè¡ãããšã | ora_sysevent
ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_grantee ora_with_grant_option ora_privilege_list |
ãã°ãªãå | ãŠãŒã¶ãŒãåæããåã«æ©èœããåç·ã¬ãã«ãŸãã¯ããŒã¿ããŒã¹ã§çºçããŸã | ora_sysevent
ora_login_user ora_instance_num ora_database_name |
ãã°ãªã³åŸ | ãŠãŒã¶ãŒãããŒã¿ããŒã¹ãžã®æ¥ç¶ãæ£åžžã«ç¢ºç«ããåŸã«æ©èœããŸãã ãšã©ãŒãçºçãããšããŠãŒã¶ãŒã¯ãšã³ããªãæåŠãããŸãã SYSã«ã¯ç¡å¹ã§ãã | ora_sysevent
ora_login_user ora_instance_num ora_database_name ora_client_ip_address |
ååå€æŽå
ååå€æŽåŸ | renameã³ãã³ããå®è¡ãããšã | ora_sysevent
ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_owner ora_dict_obj_type |
åãæ¶ãåã«
åãæ¶ãåŸ | åãæ¶ãã³ãã³ããå®è¡ãããšã | ora_sysevent
ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_revokee ora_privilege_list |
äžæåŸ | ãµãŒããŒãšã©ãŒïŒã¡ã¢ãªäžè¶³ïŒãåå ã§sqlã³ãã³ããäžæãããå Žåã«æ©èœããŸãã
ãã®å Žåãããªã¬ãŒã¯æ¡ä»¶ãå€æŽããŠã³ãã³ãã®å®è¡ãåéããå¿ èŠããããŸãïŒ | ora_sysevent
ora_login_user ora_instance_num ora_database_name ora_server_error ora_is_servererror ora_space_error_info |
åãæšãŠãåã«
åãæšãŠåŸ | truncateã³ãã³ããå®è¡ãããšã | ora_sysevent
ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner |
ããªã¬ãŒã®ã³ã³ãã€ã«
ããªã¬ãŒã®äœæã³ãã³ãäžã«ãšã©ãŒãçºçããå Žåãããªã¬ãŒã¯äœæãããŸãããç¡å¹ãªç¶æ ã«ãªããŸãã ãã®å Žåããã®ãããªããªã¬ãŒããã³ã°ããŠãããªããžã§ã¯ãã«å¯ŸããŠæäœïŒããªã¬ãŒãããªã¬ãŒããå¿ èŠãããæäœïŒãå®è¡ããããšãããšããã¹ãŠå€±æããŸãã ããã¯æ¬¡ã®å Žåã«ã¯é©çšãããŸããã
- ããªã¬ãŒã¯ç¡å¹ãªç¶æ ã§äœæãããŸãïŒãŸãã¯ãã®ãããªç¶æ ã«ãªããŸãïŒ
- ããŒã¿ããŒã¹ããªã¬ãŒã€ãã³ãã§ã®èµ·ååŸ
- ããªã¬ãŒã€ãã³ãã¯ãããŒã¿ããŒã¹ãžã®ãã°ãªã³åŸãŸãã¯ã¹ããŒããžã®ãã°ãªã³åŸã§ãããã·ã¹ãã ãŠãŒã¶ãŒãšããŠãã°ã€ã³ãè©Šè¡ãããŸãã
ããªã¬ãŒãåã³ã³ãã€ã«ããã«ã¯ãalter triggerã³ãã³ãã䜿çšããŸãã
ããªã¬ãŒäŸå€
ããªã¬ãŒã§äŸå€ãçºçããå Žåãæäœå šäœãããŒã«ããã¯ãããŸãïŒããªã¬ãŒå ã§è¡ãããå€æŽãå«ãïŒã ããã®äŸå€ïŒ
- ããªã¬ãŒã€ãã³ããããŒã¿ããŒã¹ã®èµ·ååŸãŸãã¯ããŒã¿ããŒã¹ã®ã·ã£ããããŠã³åã®å Žå
- ããªã¬ãŒã€ãã³ããããŒã¿ããŒã¹ãžã®ãã°ãªã³åŸã§ããããŠãŒã¶ãŒãããŒã¿ããŒã¹ããªã¬ãŒç®¡çæš©éãæã£ãŠããå Žå
- ã¹ããŒããžã®ãã°ãªã³åŸã®ããªã¬ãŒã€ãã³ãã§ããŠãŒã¶ãŒãã¹ããŒããææããŠãããããŸãã¯ä»»æã®ããªã¬ãŒãå€æŽããç¹æš©ãæã£ãŠããå Žå
ããªã¬ãŒé åº
Trigger_ordering_clauseã³ã³ã¹ãã©ã¯ãïŒ

- ãŸãããã¹ãŠã®åã®ã¹ããŒãã¡ã³ãããªã¬ãŒ
- 次ã«ãããªã¬ãŒã®åè¡ã®åã®ãã¹ãŠ
- çµå±ãåè¡ããªã¬ãŒã®åŸ
- ãããŠæåŸã«ãããªã¬ãŒã¹ããŒãã¡ã³ãã®åŸã®ãã¹ãŠ
åæã«èµ·åããããªã¬ãŒã®å®è¡é åºãæ瀺çã«æå®ããã«ã¯ïŒãã®é åºã¯ããã©ã«ãã§ã¯å®çŸ©ãããŠããªãããïŒã次ã䜿çšããæ§é ãå è¡ãããŸãã
ããªã¬ãŒã®æå¹å/ç¡å¹å
ããã¯ãããšãã°ã倧éã®æ å ±ãããŒãã«ã«èªã¿èŸŒãããã«å¿ èŠã«ãªãå ŽåããããŸãã
次ã®ã³ãã³ãã䜿çšããŠãããªã¬ãŒãæå¹/ç¡å¹ã«ã§ããŸãã
ALTER TRIGGER [schema.]trigger_name { ENABLE | DISABLE };
ããŒãã«ã®ãã¹ãŠã®ããªã¬ãŒãäžåºŠã«æå¹/ç¡å¹ã«ããã«ã¯ïŒ
ALTER TABLE table_name { ENABLE | DISABLE } ALL TRIGGERS;
ããªã¬ãŒãå€æŽããã«ã¯ãããªã¬ãŒã®äœæãŸãã¯çœ®æã³ãã³ãã䜿çšããããæåã«ããããããªã¬ãŒãåé€ããŠããããªã¬ãŒãåäœæããŸãã
ããªã¬ãŒã®å€æŽæäœã§ã¯ãããªã¬ãŒã®æå¹å/ç¡å¹åãã³ã³ãã€ã«ããŸãã¯ååã®å€æŽã®ã¿ãå¯èœã§ãã
ããªã¬ãŒã®ã³ã³ãã€ã«ïŒ
alter trigger TRIGGER_NAME compile;
ããªã¬ãŒæäœã®æš©å©
ããªã¬ãŒã䜿çšããã«ã¯ãã¹ããŒã å ã§ãã£ãŠããããªã¬ãŒã®äœææš©éãå¿ èŠã§ããããã«ãããäœæãå€æŽãåé€ã®æš©éãä»äžãããŸãã
grant create trigger to USER;
ä»ã®ãã¹ãŠã®ã¹ããŒã ã§ããªã¬ãŒãæäœããã«ã¯ã* anyããªã¬ãŒæš©éãå¿ èŠã§ãã äœæãå€æŽãåé€ã®æš©å©ã¯åå¥ã«ä»äžãããããšã«æ³šæããŠãã ããã
grant create any trigger to USER; grant alter any trigger to USER; grant drop any trigger to USER;
DATABASEã¬ãã«ã§ã·ã¹ãã ããªã¬ãŒãæäœããã«ã¯ãADMINISTER DATABASE TRIGGERæš©éãå¿ èŠã§ãã
grant ADMINISTER DATABASE TRIGGER to USER;
ããªã¬ãŒæ å ±ãå«ãããŒã¿ã®èŸæžïŒ
- dba_triggers-ããªã¬ãŒæ å ±
- dba_source-ããªã¬ãŒæ¬äœã³ãŒã
- dba_objects-ããªã¬ãŒã®æå¹æ§
ãã®èšäºã®å·çã«åºã¥ããè¬çŸ©ã®ãããªé²ç»ïŒ
ãªã©ã¯ã«ã®ãããã¯ã«é¢ããä»ã®å€ãã®ãããªã¯ããã®ãã£ã³ãã«ã§èŠã€ããããšãã§ããŸãïŒ www.youtube.com/c/MoscowDevelopmentTeam
ãã®ä»ã®Oracleã®èšäº
Oracleã®ã³ã¬ã¯ã·ã§ã³ã«ã€ããŠ