Thanks for the help. I was able to use this info and create the trigger
I wanted. Works...GREAT.... Now no one can accidentally wipe out the
database by incorrectly specifying the where clause on a delete.
Frank van Bortel wrote:
> Ken Denny wrote:
>
>>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.
>
>
> As the packaged variable(s) get initialized *per transaction*,
> there's no need:
>
> SQL> 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 15
> ORA-06512: at "HR.BILL_AS", line 2
> ORA-04088: error during execution of trigger 'HR.BILL_AS'
>
> +++ Expected behavior.
> +++ Now, according to you, this cannot happen, as the
> +++ reccnt variable still is over 1 (or else, we would not
> +++ have the error above, would we?):
>
> SQL> delete from employees where manager_id=124 and rownum=1;
> 1 row deleted.
>
> SQL> delete from employees where manager_id=124 and rownum=1;
> 1 row deleted.
>
> SQL> delete from employees where manager_id=124 and rownum=1;
> 1 row deleted.
>
> SQL> delete from employees where manager_id=124 and rownum=1;
> 1 row deleted.
>
> SQL> delete from employees where manager_id=124 and rownum=1;
> 1 row deleted.
>
> SQL> delete from employees where manager_id=124 and rownum=1;
> 1 row deleted.
>
> SQL> delete from employees where manager_id=124 and rownum=1;
> 1 row deleted.
>
> SQL> delete from employees where manager_id=124 and rownum=1;
> 1 row deleted.
>
> SQL> delete from employees where manager_id=124 and rownum=1;
> 0 rows deleted.
>
> +++ No more people left for this manager....
> +++ Salami tactics:
> +++ get 'em slice by slice, until you have the salami
--
Regards, Tel# (214) 480-1433 Fax# (214) 480-2356
Bill Bell Email = billb_at_ti.com
Received on Wed Apr 13 2005 - 14:19:58 CDT
