oracle触发日志 [英] oracle trigger log
问题描述
我有一个触发器,用于测试在过去5分钟内是否有人在打一个时钟,是否已经在自己的卡片上打过孔.如果没有他们的信息,则应保留它们到表clk_data上,否则应在日志表utl_logbook中进行记录,并且应该中止对clk_data的插入.
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分钟内),我会收到以下错误:
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:
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'
交易已停止,但在utl_logbook中没有记录.
The transaction is stopped but I get no recording of it in 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;
推荐答案
这可能是您雇用pragma autonomous_transaction
的罕见情况之一.它将允许您提交而不影响您的主要交易.请谨慎使用.了解有关自主交易和 autonomous_transaction用法
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 transactions and autonomous_transaction pragma
这里是一个例子:
-- 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!
这篇关于oracle触发日志的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!