如何防止使用条件的触发器在表上删除和删除表

Question

需要防止删除或更新表Transfers_details，当pointer_id == 1从传输表
注意：table transfers_details具有传输

代码的seleationship：

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;

nese更新表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 再，这两个更新和删除工作：

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>