我希望这个触发器在审计表中插入数据，并抛出错误.但这只会引发错误，但数据不会被插入

Question

这是检查实际工资是否大于最高工资的触发器。但它只是提出了错误，并没有将数据记录在审计表中。我希望这个触发器插入数据并引发错误。

Create or replace trigger trg_audit
BEFORE UPDATE ON lds_placement
for each row
BEGIN  
if :NEW.actual_salary>:new.max_salary then
insert into pradip_audit_table(audit_date, table_name, operation_type, 
primary_key_column, primary_key_value,
column_affected, max_salary, actual_salary, host, ip_adddress, terminal ) 
values (sysdate, 
'lds_placement',
'update', 
'plt_id', 
:OLD.placement_id,
'actual_salary', 
:new.max_salary, 
:NEW.actual_salary,
sys_context('USERENV', 'HOST'),
sys_context('USERENV', 'IP_ADDRESS', 15),
sys_context('USERENV', 'TERMINAL'));
raise_application_error(-20111, 'Salary cannot be more than maximum 
salary');
end if;
end;

Answer 1

对于这种情况，我建议在插入审计表的单独过程中使用自主事务。有文件https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/autonotransaction_pragma.htm

问题是，插入后不进行提交。你不能在触发器中这样做。我更喜欢让登录自主事务，这应该会有所帮助。

Answer 2

无论您在触发器中做什么，都将遵循整个事务的命运。如果插入数据，然后引发异常，则整个操作将回滚。

您需要在另一个事务中执行插入操作。oracle允许您定义在独立于调用程序的事务中运行的函数和过程。您甚至可以将这些过程定义为本地过程。

这就是您所需要的：

Create or replace trigger trg_audit  
AFTER UPDATE ON lds_placement
for each row
declare 
procedure WriteAuditLog is
pragma autonomous_transaction;
begin
insert into pradip_audit_table(
audit_date, table_name, operation_type,  primary_key_column, primary_key_value,
column_affected, max_salary, actual_salary, host, ip_adddress, terminal 
) 
values 
(
sysdate, 
'lds_placement',
'update', 
'plt_id', 
:OLD.placement_id,
'actual_salary', 
:new.max_salary, 
:NEW.actual_salary,
sys_context('USERENV', 'HOST'),
sys_context('USERENV', 'IP_ADDRESS', 15),
sys_context('USERENV', 'TERMINAL')
);
-- you can't leave a autonomous_transaction function without committing or rolling back:
--    if you leave the transaction open you get an error!
commit;                                                  
exception when others then
rollback;
raise;
end WriteAuditLog;
BEGIN  
if :NEW.actual_salary>:new.max_salary then
WriteAuditLog;
raise_application_error(-20111, 'Salary cannot be more than maximum  salary');
end if;
end;

请注意，我将触发器更改为在语句之后运行，而不是在语句之前运行。

1. 您的表上可能存在MULTIPLE before触发器
2. 每个BEFORE触发器都有机会更改将要写入的实际数据
3. 你不能保证你的触发器会是最后一个被执行的触发器

完全有可能还有另一个触发器将工资限制在最高限额，并插入一条不违反规则的记录：如果您的触发器在这个触发器之前运行，则会使第二个触发器无效。

另一件可能发生的事情是，在"之前"数据通过您的检查后，另一个触发器会运行，而第二个触发器可能会更改数据，例如，将工资翻倍，并写入违反您的检查的数据，而不管您的触发器应该做什么。

AFTER触发器不能更改数据，因为它已经被写入，所以这种检查和日志记录的正确位置是AFTER触发器。

请注意，如果您引发异常，即使您处于AFTER触发器中，更新命令也会回滚：不要让"AFTER"这个词误导您。

Answer 3

来自文档：

在大多数情况下，如果触发器运行引发异常的语句，并且异常未由异常处理程序处理，则数据库回滚触发器及其触发的效果陈述

Oracle在update语句之前创建一个隐式存储点，如果更新或触发导致异常，则事务将回滚到该存储点。解决方案是为插入创建一个单独的过程，将其标记为pragma AUTONOMOUSE_TRANSACTION，并在该过程中执行提交；那么当触发器失败时，您插入的数据将不会回滚。