Oracle PL/SQL Tutorial/Trigger/Trigger and Transaction
Содержание
Autonomous triggers
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>
Call PRAGMA AUTONOMOUS_TRANSACTION procedure
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>
Commit in trigger
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>
Mark trigger with PRAGMA AUTONOMOUS_TRANSACTION
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>
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
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>
Transaction with "pragma autonomous_transaction"
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>