Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Delete Trigger - Constrain Delete to 1 Row
Very good except for one thing. You never set reccnt back to 0. We want
them to be only able to delete one row per statement not one row per
session. Simple fix below.
Frank van Bortel wrote:
> not sure what version, so demo is on 9.2:
>
> hr_at_O920.CSDB01.CS.NL> create or replace package apex_var as
> 2 reccnt pls_integer;
> 3 procedure increase_reccnt;
> 4 procedure called_trigger;
> 5 end;
> 6 /
> Package created.
>
> hr_at_O920.CSDB01.CS.NL> create or replace package body apex_var as
> 2 procedure increase_reccnt as
> 3 begin
> 4 apex_var.reccnt := apex_var.reccnt + 1;
> 5 end increase_reccnt;
> 6 --
> 7 procedure called_trigger as
7a test_reccnt pls_integer := apex_var.reccnt;
> 8 begin
8a apex_var.reccnt := 0;
9 if test_reccnt > 1 then
> 10 raise_application_error (-20001,'Only 1 delete allowed');
> 11 end if;
> 12 end called_trigger;
> 13 --
> 14 end apex_var;
> 15 /
> Package body created.
>
> hr_at_O920.CSDB01.CS.NL> create or replace trigger bill
> 2 before delete on employees
> 3 for each row
> 4 begin
> 5 apex_var.increase_reccnt;
> 6 end;
> 7 /
> Trigger created.
>
> hr_at_O920.CSDB01.CS.NL> create or replace trigger bill_as
> 2 before delete on employees
> 3 begin
> 4 apex_var.called_trigger;
> 5 end;
> 6 /
> Trigger created.
>
> hr_at_O920.CSDB01.CS.NL> set serveroutput on
> hr_at_O920.CSDB01.CS.NL> select count(*) from employees;
>
> COUNT(*)
> ----------
> 107
>
> hr_at_O920.CSDB01.CS.NL> delete from employees where manager_id=124;
> delete from employees where manager_id=124
> *
> ERROR at line 1:
> ORA-20001: Only 1 delete allowed
> ORA-06512: at "HR.APEX_VAR", line 10
> ORA-06512: at "HR.BILL_AS", line 2
> ORA-04088: error during execution of trigger 'HR.BILL_AS'
>
>
> hr_at_O920.CSDB01.CS.NL> select count(*) from employees;
>
> COUNT(*)
> ----------
> 107
>
> I'll leave it up to you how you are going to catch the
> error
> --
> Regards,
> Frank van Bortel
Ken Denny Received on Tue Apr 12 2005 - 07:16:42 CDT