Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Delete Trigger - Constrain Delete to 1 Row

Re: Oracle Delete Trigger - Constrain Delete to 1 Row

From: Ken Denny <ken_at_kendenny.com>
Date: 12 Apr 2005 05:16:42 -0700
Message-ID: <1113308202.176600.293520@g14g2000cwa.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US