oracle oracle触发日志
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12805181/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
oracle trigger log
提问by user1732495
I have a trigger which is supposed to test whether an individual punching a time clock has punched their card within the last 5 minutes. If they have not their info should be left to go on to the table clk_data, otherwise a record should be made in the log table utl_logbook and the insert to clk_data should be aborted.
我有一个触发器，用于测试打卡时间的个人是否在过去 5 分钟内打了他们的卡。如果他们没有他们的信息，则应将其留在表 clk_data 中，否则应在日志表 utl_logbook 中进行记录，并应中止对 clk_data 的插入。
When I run a good punch the insert works as advertised. When it is a bad punch (within 5 minutes of the last punch) I get the following error:
当我打出一记好拳时，插入物会像宣传的那样工作。当它是一个糟糕的一拳（在最后一拳的 5 分钟内）时，我收到以下错误：
SQL Error: ORA-20101: Too Soon
ORA-06512: at "TRUTRACK.INSERT_CLK_DATA", line 14
ORA-04088: error during execution of trigger 'TRUTRACK.INSERT_CLK_DATA'
The transaction is stopped but I get no recording of it in utl_logbook.
事务已停止，但我没有在 utl_logbook 中记录它。
The trigger code:
create or replace
TRIGGER "INSERT_CLK_DATA"
BEFORE INSERT ON clk_data
FOR EACH row
BEGIN
DECLARE qty INTEGER := 0;
BEGIN
SELECT COUNT(*)
INTO qty
FROM clk_data
WHERE clk_time BETWEEN (:new.clk_time - 5/(24*60)) AND (:new.clk_time + 5/(24*60))
AND :new.payroll = clk_data.payroll;
IF qty > 0 THEN
INSERT INTO utl_logbook (time, source, message) VALUES (sysdate, 'INSERT_CLK_DATA', 'Clock punch within restricted window. Payroll ID:' || :new.payroll || ' Time: ' || :new.clk_time || ' Type: ' || :new.type);
RAISE_APPLICATION_ERROR(-20101, 'Too Soon');
END IF;
END;
END;
回答by Nick Krasnov
This is can be one of those rare cases when you can employ pragma autonomous_transaction. It will allow you commit without affecting your main transaction. Use it with caution. Find out more about autonomous transactionsand autonomous_transaction pragma
这可能是您可以使用pragma autonomous_transaction. 它将允许您在不影响主事务的情况下提交。请谨慎使用。了解更多关于自治事务和autonomous_transaction pragma
Here is an example:
下面是一个例子：
-- our error logging table
create table tb_log(
msg varchar2(123)
)
/
-- our working table
create table tb_table(
col11 number
)
/
-- procedure that is going to log errors
NK@XE> create or replace procedure log_error(p_msg in varchar2)
2 is
3 pragma autonomous_transaction;
4 begin
5 insert into tb_log(msg)
6 values(p_msg);
7 commit;
8 end;
NK@XE> /
Procedure created.
NK@XE> create or replace trigger tr_tb_table
2 before insert on tb_table
3 for each row
4 begin
5 if mod(:new.col1, 2) != 0
6 then
7 log_error('Error!');
8 raise_application_error(-20000, 'Error has ocurred!');
9 end if;
10 end;
11 /
Trigger created.
NK@XE> select * from tb_log;
no rows selected
NK@XE> select * from tb_table;
no rows selected
NK@XE> insert into tb_table(col1) values(1);
insert into tb_table(col1) values(1)
*
ERROR at line 1:
ORA-20000: Error has ocurred!
ORA-06512: at "NK.TR_TB_TABLE", line 5
ORA-04088: error during execution of trigger 'NK.TR_TB_TABLE'
NK@XE> select * from tb_log;
MSG
--------------------------------------------------------------------------------
Error!
回答by John D
@nicholas-krasnov provided the solution, just to elaborate a little on the reasoning - you are throwing an exception in the trigger body:
@nicholas-krasnov 提供了解决方案，只是为了详细说明推理 - 您在触发器主体中抛出异常：
RAISE_APPLICATION_ERROR(-20101, 'Too Soon');
which results in a rollback of the transaction. Full details of this can be found:
这会导致事务回滚。可以找到有关此的完整详细信息：
http://psoug.org/reference/exception_handling.html
http://psoug.org/reference/exception_handling.html
If the statement fails, Oracle rolls back to the savepoint. Normally, just the failed SQL statement is rolled back, not the whole transaction. However, if the statement raises an unhandled exception, the host environment determines what is rolled back.
如果语句失败，Oracle 将回滚到保存点。通常，只会回滚失败的 SQL 语句，而不是整个事务。但是，如果该语句引发未处理的异常，则主机环境将确定回滚的内容。
