如何使用带条件的触发器来防止表上的更新和删除

Question

需要防止从传输表中删除或更新表transfers_details
注意:表transfers_details与transfer有关系

代码:

CREATE OR REPLACE TRIGGER Preventer_TRIGGER
BEFORE DELETE OR UPDATE ON transfers_details
FOR EACH ROW
begin
if transfers.pointer_done=1 then
raise_application_error(-20001,'Records can not be delete OR update');
end;

更新表transfers_details或delete错误显示

Error starting at line 1 in command:
update transfers_details set fullname_sender='hani sulaiman' where id=1
Error at Command Line:1 Column:8
Error report:
SQL Error: ORA-04098: trigger 'HANI_128505.PREVENTER_TRIGGER' is invalid and failed re-validation
04098. 00000 -  "trigger '%s.%s' is invalid and failed re-validation"
*Cause:    A trigger was attempted to be retrieved for execution and was
found to be invalid.  This also means that compilation/authorization
failed for the trigger.
*Action:   Options are to resolve the compilation/authorization errors,
disable the trigger, or drop the trigger

Answer 1

您必须首先获取您感兴趣的值，然后验证它。

示例表:

SQL> select * from transfers;
ID_TRANSFERS POINTER_DONE
------------ ------------
92            1
SQL> select * from transfers_details;
ID_TRANSDET ID_TRANSFERS     AMOUNT
----------- ------------ ----------
5           92        532      --> ID_TRANSFERS = 92 references the TRANSFERS table

触发:

SQL> create or replace trigger preventer_trigger
2    before delete or update on transfers_details
3    for each row
4  declare
5    l_pointer_done transfers.pointer_done%type;
6  begin
7    select t.pointer_done
8      into l_pointer_done
9      from transfers t
10      where t.id_transfers = nvl(:new.id_transfers, :old.id_transfers);
11
12    if l_pointer_done = 1 then
13       raise_application_error(-20001, 'Records can not be deleted or updated');
14    end if;
15  end;
16  /
Trigger created.

测试:更新行不通:

SQL> update transfers_details set amount = 123 where id_transdet = 5;
update transfers_details set amount = 123 where id_transdet = 5
*
ERROR at line 1:
ORA-20001: Records can not be deleted or updated
ORA-06512: at "SCOTT.PREVENTER_TRIGGER", line 10
ORA-04088: error during execution of trigger 'SCOTT.PREVENTER_TRIGGER'

删除:

SQL> delete from transfers_details;
delete from transfers_details
*
ERROR at line 1:
ORA-20001: Records can not be deleted or updated
ORA-06512: at "SCOTT.PREVENTER_TRIGGER", line 10
ORA-04088: error during execution of trigger 'SCOTT.PREVENTER_TRIGGER'

但是，如果pointer_done不再是1,update和delete都起作用:

SQL> update transfers set pointer_done = 2;
1 row updated.
SQL> update transfers_details set amount = 123 where id_transdet = 5;
1 row updated.
SQL>