那我要在触发器里处理事务,怎么个处理法,难道他自动处理的?? 还有如果遇到exception时怎么写SQL语句啊?比如exception when others then
试试PRAGMA AUTONOMOUS_TRANSACTION; ------------------------------------------------------- CREATE OR REPLACE trigger trig BEFORE INSERT ON logon_tbl FOR EACH ROW PRAGMA AUTONOMOUS_TRANSACTION; declare tv number(10); BEGIN select count(*) into tv from logon_tbl where to_char(logon_date,'yymmdd')=to_char(sysdate,'yymmdd'); select sysdate into :new.logon_date from dual; IF tv>0 THEN select 'A'||to_char(sysdate,'yymmdd')||to_char((tv+1),'fm000') into :new.username from dual; ELSE SELECT 'A'||to_char(sysdate,'yymmdd')||to_char(1,'fm000') INTO :new.username FROM dual; END IF; commit; exception when others then rollback; END;
请参考 Advantages of Autonomous Transactions Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. So, you can log events, increment retry counters, and so on, even if the main transaction rolls back. More important, autonomous transactions help you build modular, reusable software components. For example, stored procedures can start and finish autonomous transactions on their own. A calling application need not know about a procedure's autonomous operations, and the procedure need not know about the application's transaction context. That makes autonomous transactions less error-prone than regular transactions and easier to use. Furthermore, autonomous transactions have all the functionality of regular transactions. They allow parallel queries, distributed processing, and all the transaction control statements including SET TRANSACTION. Defining Autonomous Transactions To define autonomous transactions, you use the pragma (compiler directive) AUTONOMOUS_TRANSACTION. The pragma instructs the PL/SQL compiler to a routine as autonomous (independent). In this context, the term routine includes top-level (not nested) anonymous PL/SQL blocks local, stand-alone, and packaged functions and procedures methods of a SQL object type database triggers You can code the pragma anywhere in the declarative section of a routine. But, for readability, code the pragma at the top of the section. The syntax follows: PRAGMA AUTONOMOUS_TRANSACTION; In the following example, you a packaged function as autonomous: CREATE PACKAGE banking AS ... FUNCTION balance (acct_id INTEGER) RETURN REAL; END banking;CREATE PACKAGE BODY banking AS ... FUNCTION balance (acct_id INTEGER) RETURN REAL IS PRAGMA AUTONOMOUS_TRANSACTION; my_bal REAL; BEGIN ... END; END banking; Restriction: You cannot use the pragma to all subprograms in a package (or all methods in an object type) as autonomous. Only individual routines can be ed autonomous. For example, the following pragma is illegal: CREATE PACKAGE banking AS PRAGMA AUTONOMOUS_TRANSACTION; -- illegal ... FUNCTION balance (acct_id INTEGER) RETURN REAL; END banking; In the next example, you a stand-alone procedure as autonomous: CREATE PROCEDURE close_account (acct_id INTEGER, OUT balance) AS PRAGMA AUTONOMOUS_TRANSACTION; my_bal REAL; BEGIN ... END; In the following example, you a PL/SQL block as autonomous: DECLARE PRAGMA AUTONOMOUS_TRANSACTION; my_empno NUMBER(4); BEGIN ... END; Restriction: You cannot a nested PL/SQL block as autonomous. In the example below, you a database trigger as autonomous. Unlike regular triggers, autonomous triggers can contain transaction control statements such as COMMIT and ROLLBACK. CREATE TRIGGER parts_trigger BEFORE INSERT ON parts FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO parts_log VALUES(:new.pnum, :new.pname); COMMIT; -- allowed only in autonomous triggers END;
特别注意以上的 Restriction: You cannot a nested PL/SQL block as autonomous. In the example below, you a database trigger as autonomous. Unlike regular triggers, autonomous triggers can contain transaction control statements such as COMMIT and ROLLBACK. CREATE TRIGGER parts_trigger BEFORE INSERT ON parts FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO parts_log VALUES(:new.pnum, :new.pname); COMMIT; -- allowed only in autonomous triggers END;
PRAGMA AUTONOMOUS_TRANSACTION 只是保证触发器的内部事务的成功或失败,如果可能,你可以把你的触发器修改成一个pl/sql块来测试事务的正确性 见biti_rainy(biti_rainy)老兄的一段测试代码。PRAGMA AUTONOMOUS_TRANSACTION; 你在这里之前插入源表 然后在下面这块代码里面处理远程表 失败就回退 但回退不影响前面的源表的操作,难道有什么问题?如下,把匿名pl/sql块换做triggerSQL> select * from test1;A B ---------- -------------------- 1 1 3 3 2 2SQL> SQL> CREATE or replace PROCEDURE insert_test 2 as 3 PRAGMA AUTONOMOUS_TRANSACTION; 4 BEGIN 5 insert into test1 values(6,6); 6 rollback; 7 END; 8 /过程已创建。SQL> begin 2 insert into test1 values(5,5); 3 insert_test; 4 commit; 5 end; 6 /PL/SQL 过程已成功完成。SQL> select * from test1;A B ---------- -------------------- 1 1 3 3 2 2 5 5SQL>
触发器是随着触发自己的事务或sql语句的提交而提交,不用担心它不提交的
Autonomous Transactions在8以下是不支持的.
哦谢谢,帮我看看这个函数错在那里阿,该了半天了。。 create or replace function func_lock( r_type varchar2, newid number) return boolean is res boolean;BEGINres:=true;savepoint a; update id_tbl set id=id+1 where to_char(l_date,'yymmdd')=to_char(sysdate,'yymmdd'); dbms_output.put_line(to_char(sqlcode)||'first'); dbms_output.put_line(sqlerrm); if sqlcode=0 then if sql%rowcount=0 then INSERT INTO id_tbl VALUES(1,r_type,sysdate); dbms_output.put_line(to_char(sqlcode)||'second'); dbms_output.put_line(sqlerrm); IF SQLCode <> 0 THEN ROLLBACK to a; res:=false; else newid:=1; END IF; ELSE SELECT id INTO newid FROM id_tbl WHERE to_char(l_date,'yymmdd')=to_char(sysdate,'yymmdd'); dbms_output.put_line(to_char(newid)); res:=true; dbms_output.put_line(to_char(sqlcode)||'third'); dbms_output.put_line(sqlerrm); END IF; ELSE ROLLBACK to a; res:=false; END IF; return res; end func_lock;
还有如果遇到exception时怎么写SQL语句啊?比如exception when others then
-------------------------------------------------------
CREATE OR REPLACE trigger trig
BEFORE INSERT ON logon_tbl FOR EACH ROW
PRAGMA AUTONOMOUS_TRANSACTION;
declare tv number(10);
BEGIN
select count(*) into tv from logon_tbl where to_char(logon_date,'yymmdd')=to_char(sysdate,'yymmdd');
select sysdate into :new.logon_date from dual;
IF tv>0 THEN
select 'A'||to_char(sysdate,'yymmdd')||to_char((tv+1),'fm000') into :new.username from dual;
ELSE
SELECT 'A'||to_char(sysdate,'yymmdd')||to_char(1,'fm000') INTO :new.username FROM dual;
END IF;
commit;
exception
when others then
rollback;
END;
Advantages of Autonomous Transactions
Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. So, you can log events, increment retry counters, and so on, even if the main transaction rolls back. More important, autonomous transactions help you build modular, reusable software components. For example, stored procedures can start and finish autonomous transactions on their own. A calling application need not know about a procedure's autonomous operations, and the procedure need not know about the application's transaction context. That makes autonomous transactions less error-prone than regular transactions and easier to use. Furthermore, autonomous transactions have all the functionality of regular transactions. They allow parallel queries, distributed processing, and all the transaction control statements including SET TRANSACTION. Defining Autonomous Transactions
To define autonomous transactions, you use the pragma (compiler directive) AUTONOMOUS_TRANSACTION. The pragma instructs the PL/SQL compiler to a routine as autonomous (independent). In this context, the term routine includes top-level (not nested) anonymous PL/SQL blocks local, stand-alone, and packaged functions and procedures methods of a SQL object type database triggers You can code the pragma anywhere in the declarative section of a routine. But, for readability, code the pragma at the top of the section. The syntax follows: PRAGMA AUTONOMOUS_TRANSACTION;
In the following example, you a packaged function as autonomous: CREATE PACKAGE banking AS
...
FUNCTION balance (acct_id INTEGER) RETURN REAL;
END banking;CREATE PACKAGE BODY banking AS
...
FUNCTION balance (acct_id INTEGER) RETURN REAL IS
PRAGMA AUTONOMOUS_TRANSACTION;
my_bal REAL;
BEGIN
...
END;
END banking;
Restriction: You cannot use the pragma to all subprograms in a package (or all methods in an object type) as autonomous. Only individual routines can be ed autonomous. For example, the following pragma is illegal: CREATE PACKAGE banking AS
PRAGMA AUTONOMOUS_TRANSACTION; -- illegal
...
FUNCTION balance (acct_id INTEGER) RETURN REAL;
END banking;
In the next example, you a stand-alone procedure as autonomous: CREATE PROCEDURE close_account (acct_id INTEGER, OUT balance) AS
PRAGMA AUTONOMOUS_TRANSACTION;
my_bal REAL;
BEGIN
...
END;
In the following example, you a PL/SQL block as autonomous: DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
my_empno NUMBER(4);
BEGIN
...
END;
Restriction: You cannot a nested PL/SQL block as autonomous. In the example below, you a database trigger as autonomous. Unlike regular triggers, autonomous triggers can contain transaction control statements such as COMMIT and ROLLBACK. CREATE TRIGGER parts_trigger
BEFORE INSERT ON parts FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO parts_log VALUES(:new.pnum, :new.pname);
COMMIT; -- allowed only in autonomous triggers
END;
Restriction: You cannot a nested PL/SQL block as autonomous. In the example below, you a database trigger as autonomous. Unlike regular triggers, autonomous triggers can contain transaction control statements such as COMMIT and ROLLBACK. CREATE TRIGGER parts_trigger
BEFORE INSERT ON parts FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO parts_log VALUES(:new.pnum, :new.pname);
COMMIT; -- allowed only in autonomous triggers
END;
只是保证触发器的内部事务的成功或失败,如果可能,你可以把你的触发器修改成一个pl/sql块来测试事务的正确性
见biti_rainy(biti_rainy)老兄的一段测试代码。PRAGMA AUTONOMOUS_TRANSACTION;
你在这里之前插入源表
然后在下面这块代码里面处理远程表
失败就回退
但回退不影响前面的源表的操作,难道有什么问题?如下,把匿名pl/sql块换做triggerSQL> select * from test1;A B
---------- --------------------
1 1
3 3
2 2SQL>
SQL> CREATE or replace PROCEDURE insert_test
2 as
3 PRAGMA AUTONOMOUS_TRANSACTION;
4 BEGIN
5 insert into test1 values(6,6);
6 rollback;
7 END;
8 /过程已创建。SQL> begin
2 insert into test1 values(5,5);
3 insert_test;
4 commit;
5 end;
6 /PL/SQL 过程已成功完成。SQL> select * from test1;A B
---------- --------------------
1 1
3 3
2 2
5 5SQL>
create or replace function func_lock(
r_type varchar2,
newid number)
return boolean is
res boolean;BEGINres:=true;savepoint a;
update id_tbl set id=id+1
where to_char(l_date,'yymmdd')=to_char(sysdate,'yymmdd');
dbms_output.put_line(to_char(sqlcode)||'first');
dbms_output.put_line(sqlerrm);
if sqlcode=0 then
if sql%rowcount=0 then
INSERT INTO id_tbl VALUES(1,r_type,sysdate);
dbms_output.put_line(to_char(sqlcode)||'second');
dbms_output.put_line(sqlerrm);
IF SQLCode <> 0 THEN
ROLLBACK to a;
res:=false;
else
newid:=1;
END IF;
ELSE
SELECT id INTO newid FROM id_tbl WHERE to_char(l_date,'yymmdd')=to_char(sysdate,'yymmdd');
dbms_output.put_line(to_char(newid));
res:=true;
dbms_output.put_line(to_char(sqlcode)||'third');
dbms_output.put_line(sqlerrm);
END IF;
ELSE
ROLLBACK to a;
res:=false;
END IF;
return res;
end func_lock;