Oracle PL/SQL Tutorial/Trigger/Trigger and Transaction
Содержание
Autonomous triggers
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE order_tab(
2 order_id NUMBER(10) PRIMARY KEY, 3 order_date DATE NOT NULL, 4 total_qty NUMBER, 5 total_price NUMBER(15,2), 6 supp_id NUMBER(6) 7 );
Table created. SQL> SQL> CREATE TABLE order_tran_coming_in(
2 order_id NUMBER(10) NOT NULL, 3 order_date DATE NOT NULL, 4 tran_coming_in_date DATE NOT NULL, 5 success_flag VARCHAR2(1) DEFAULT "N" NOT NULL);
Table created. SQL> SQL> SQL> SQL> create or replace trigger bi_order_tab
2 before insert on order_tab for each row 3 declare 4 pragma autonomous_transaction; 5 begin 6 insert into order_tran_coming_in values (:NEW.order_id, 7 :NEW.order_date, 8 SYSDATE, 9 "N"); 10 commit; 11 end; 12 /
Trigger created. SQL> create or replace trigger ai_order_tab
2 after insert on order_tab for each row 3 declare 4 pragma autonomous_transaction; 5 begin 6 update order_tran_coming_in 7 set success_flag = "Y" 8 where order_id = :NEW.order_id; 9 commit; 10 end; 11 /
Trigger created. SQL> BEGIN
2 INSERT INTO order_tab VALUES(102,SYSDATE,NULL,NULL,1001); 3 INSERT INTO order_tab VALUES(103,SYSDATE,NULL,NULL,1001); 4 INSERT INTO order_tab VALUES(103,SYSDATE,NULL,NULL,1001); 5 EXCEPTION WHEN DUP_VAL_ON_INDEX THEN 6 ROLLBACK; 7 END; 8 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table order_tran_coming_in; Table dropped. SQL> SQL> drop table order_tab; Table dropped. SQL></source>
Call PRAGMA AUTONOMOUS_TRANSACTION procedure
<source lang="sql">
SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> CREATE OR REPLACE PROCEDURE update_salary (dept_in IN NUMBER)
2 IS 3 PRAGMA AUTONOMOUS_TRANSACTION; 4 5 CURSOR myemps IS 6 SELECT empno FROM emp 7 WHERE deptno = dept_in 8 FOR UPDATE NOWAIT; 9 10 BEGIN 11 FOR rec IN myemps 12 LOOP 13 UPDATE emp SET sal = sal * 2 WHERE empno = rec.empno; 14 END LOOP; 15 COMMIT; 16 END; 17 /
Procedure created. SQL> SQL> BEGIN
2 UPDATE emp SET sal = sal * 2; 3 update_salary (10); 4 END; 5 /
BEGIN
ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified ORA-06512: at "sqle.UPDATE_SALARY", line 6 ORA-06512: at "sqle.UPDATE_SALARY", line 11 ORA-06512: at line 3
SQL> SQL> drop table emp; Table dropped. SQL> SQL></source>
Commit in trigger
<source lang="sql">
SQL> SQL> CREATE TABLE employee_compensation (
2 company VARCHAR2(100), 3 NAME VARCHAR2(100), 4 compensation NUMBER);
Table created. SQL> SQL> CREATE TABLE employee_history (
2 NAME VARCHAR2(100), 3 description VARCHAR2(255), 4 occurred_on DATE);
Table created. SQL> SQL> CREATE OR REPLACE TRIGGER bef_ins_ceo_comp
2 BEFORE INSERT 3 ON employee_compensation 4 FOR EACH ROW 5 DECLARE 6 PRAGMA AUTONOMOUS_TRANSACTION; 7 BEGIN 8 INSERT INTO employee_history 9 VALUES (:NEW.NAME, "BEFORE INSERT", SYSDATE); 10 11 COMMIT; 12 END; 13 /
Trigger created. SQL> CREATE OR REPLACE TRIGGER aft_ins_ceo_comp
2 AFTER INSERT 3 ON employee_compensation 4 FOR EACH ROW 5 DECLARE 6 PRAGMA AUTONOMOUS_TRANSACTION; 7 BEGIN 8 IF :NEW.rupensation > 1000000000 9 THEN 10 RAISE VALUE_ERROR; 11 ELSE 12 INSERT INTO employee_history VALUES (:NEW.NAME, "AFTER INSERT", SYSDATE); 13 COMMIT; 14 END IF; 15 EXCEPTION 16 WHEN OTHERS 17 THEN 18 ROLLBACK; 19 RAISE; 20 END; 21 /
Trigger created. SQL> COLUMN name FORMAT a20 SQL> COLUMN description FORMAT a30 SQL> SQL> SELECT NAME, description
2 , TO_CHAR (occurred_on, "MM/DD/YYYY HH:MI:SS") occurred_on 3 FROM employee_history;
no rows selected SQL> SQL> BEGIN
2 INSERT INTO employee_compensation VALUES ("B", "J", 9100000);
3
4 INSERT INTO employee_compensation VALUES ("B", "A", 10700000);
5
6 INSERT INTO employee_compensation VALUES ("B", "Sally Bigdeal", 1000000001);
7
8 END;
9 /
BEGIN
ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at "sqle.AFT_INS_CEO_COMP", line 15 ORA-04088: error during execution of trigger "sqle.AFT_INS_CEO_COMP" ORA-06512: at line 6
SQL> SQL> SELECT NAME, description
2 , TO_CHAR (occurred_on, "MM/DD/YYYY HH:MI:SS") occurred_on 3 FROM employee_history 4 ORDER BY occurred_on;
NAME DESCRIPTION OCCURRED_ON
------------------------------ -------------------
J BEFORE INSERT 07/24/2008 08:03:16 J AFTER INSERT 07/24/2008 08:03:16 Sally Bigdeal BEFORE INSERT 07/24/2008 08:03:16 A AFTER INSERT 07/24/2008 08:03:16 A BEFORE INSERT 07/24/2008 08:03:16 SQL> SQL> SQL> DROP TABLE employee_compensation; Table dropped. SQL> SQL> DROP TABLE employee_history; Table dropped. SQL></source>
Mark trigger with PRAGMA AUTONOMOUS_TRANSACTION
<source lang="sql">
SQL> SQL> CREATE TABLE employee_compensation (
2 company VARCHAR2(100), 3 name VARCHAR2(100), 4 compensation NUMBER, 5 layoffs NUMBER);
Table created. SQL> SQL> SQL> CREATE TABLE employee_history (
2 name VARCHAR2(100), 3 description VARCHAR2(255), 4 occurred_on DATE);
Table created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE employee_audit (
2 name IN VARCHAR2, 3 description IN VARCHAR2, 4 occurred_on IN DATE 5 ) 6 IS 7 PRAGMA AUTONOMOUS_TRANSACTION; 8 BEGIN 9 INSERT INTO employee_history VALUES ( 10 employee_audit.name, 11 employee_audit.description, 12 employee_audit.occurred_on 13 ); 14 15 IF employee_audit.description LIKE "AFTER%" 16 THEN 17 RAISE VALUE_ERROR; 18 END IF; 19 20 COMMIT; 21 END; 22 /
Procedure created. SQL> CREATE OR REPLACE TRIGGER bef_ins_ceo_comp
2 BEFORE INSERT ON employee_compensation FOR EACH ROW 3 DECLARE 4 ok BOOLEAN := TRUE; 5 BEGIN 6 employee_audit ( 7 :new.name, "BEFORE INSERT", SYSDATE); 8 END; 9 /
Trigger created. SQL> SQL> CREATE OR REPLACE TRIGGER aft_ins_ceo_comp
2 AFTER INSERT ON employee_compensation FOR EACH ROW 3 DECLARE 4 ok BOOLEAN := FALSE; 5 BEGIN 6 employee_audit ( 7 :new.name, "AFTER INSERT", SYSDATE); 8 END; 9 /
Trigger created. SQL> SQL> COLUMN name FORMAT a20 SQL> COLUMN description FORMAT a30 SQL> SQL> SELECT name,
2 description, 3 TO_CHAR (occurred_on, "MM/DD/YYYY HH:MI:SS") occurred_on 4 FROM employee_history;
no rows selected SQL> SQL> BEGIN
2 INSERT INTO employee_compensation VALUES ("M", "J", 9100000, 2700);
3
4 INSERT INTO employee_compensation VALUES ("A", "H", 33200000, 3300);
5
6 INSERT INTO employee_compensation VALUES ("E", "G", 10700000, 20100);
7
8 ROLLBACK; -- I wish!
9 END;
10 /
BEGIN
ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at "sqle.EMPLOYEE_AUDIT", line 17 ORA-06512: at "sqle.AFT_INS_CEO_COMP", line 4 ORA-04088: error during execution of trigger "sqle.AFT_INS_CEO_COMP" ORA-06512: at line 2
SQL> SQL> SELECT name,
2 description, 3 TO_CHAR (occurred_on, "MM/DD/YYYY HH:MI:SS") occurred_on 4 FROM employee_history;
NAME DESCRIPTION OCCURRED_ON
------------------------------ -------------------
J BEFORE INSERT 07/24/2008 08:03:13 SQL> SQL> SQL> SQL> SQL> DROP TABLE employee_history; Table dropped. SQL> SQL> DROP TABLE employee_compensation; Table dropped. SQL></source>
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
<source lang="sql">
SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE fire_em_all
2 IS 3 PRAGMA AUTONOMOUS_TRANSACTION; 4 BEGIN 5 DELETE FROM emp; 6 7 COMMIT; 8 EXCEPTION 9 WHEN OTHERS 10 THEN 11 ROLLBACK; 12 END; 13 /
Procedure created. SQL> SQL> show errors No errors. SQL> SQL> DECLARE
2 num INTEGER;
3 BEGIN
4 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
5
6 SELECT COUNT (*)
7 INTO num
8 FROM emp;
9
10 DBMS_OUTPUT.put_line ("Before isolated AT delete " || num);
11 fire_em_all;
12
13 SELECT COUNT (*)
14 INTO num
15 FROM emp;
16
17 DBMS_OUTPUT.put_line ("After isolated AT delete " || num);
18 COMMIT; -- ROLLBACK;
19
20 SELECT COUNT (*)
21 INTO num
22 FROM emp;
23
24 DBMS_OUTPUT.put_line ("After MT commit " || num);
25 END;
26 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table emp; Table dropped. SQL> SQL></source>
Transaction with "pragma autonomous_transaction"
<source lang="sql">
SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> create or replace procedure child
2 as 3 pragma autonomous_transaction; 4 l_ename emp.ename%type; 5 begin 6 7 update emp set ename = "y" where ename = "BLAKE"; 8 rollback to Parent_Savepoint; 9 commit; 10 end; 11 /
Procedure created. SQL> SQL> create or replace procedure parent
2 as 3 l_ename emp.ename%type; 4 begin 5 savepoint Parent_Savepoint; 6 update emp set ename = "x" where ename = "KING"; 7 8 child; 9 rollback; 10 end; 11 /
Procedure created. SQL> SQL> exec parent BEGIN parent; END;
ERROR at line 1: ORA-01086: savepoint "PARENT_SAVEPOINT" never established ORA-06512: at "sqle.CHILD", line 8 ORA-06512: at "sqle.PARENT", line 8 ORA-06512: at line 1
SQL> SQL> SQL> drop table emp; Table dropped. SQL></source>
