Re: Trigger to prevent insert of multiple rows

vertis_at_xs1.xs4all.nl (vertis) wrote:

You can do the following:

create or replace package counter
as

   cnt number;
end;
/

create or replace trigger x_bi
before insert on dri_master.series_master begin

   count.cnt := 0;
end;
/

create or replace trigger x_bifer
before insert on dri_master.series_master for each row
begin

   if ( count.cnt > 0 ) then

       raise_application_error( .... );
   end if;
   count.cnt := count.cnt+1;
end;
/

>Rona Crystal (rcrystal_at_dri.mcgraw-hill.com) wrote:
 

>: I am a new user to Oracle. My project is to convert an existing Sybase
>: database. The trigger that I am trying to convert prevents multiple rows
>: from being inserted in a single insert statement. I have tried the
>: following but the implicit cursor SQL%ROWCOUNT seems to be NULL. According
>: to Oracle support the trigger is on the same level as the insert statement
>: so the implicit cursor from the insert is not available in the trigger.
 

>: CREATE OR REPLACE TRIGGER seriesmaster_chkmultrows_tr
>: AFTER INSERT ON dri_master.series_master
>: BEGIN
>: IF SQL%ROWCOUNT > 1 THEN
>: RAISE_APPLICATION_ERROR (-20004,'Can only insert one row in '||
>: 'series_master table');
>: END;
>: /
 

>: Does anybody have a suggestion on how I can implement this? This concept of
>: preventing multiple row inserts is used in many of the tables in this
>: system.
 

>: Feel free to email or respond here.

>You could write a procedure to do the insert and don't allow any user
>insert privilege on the table. I don't know a trigger solution...
 

>Teijo Doornkamp (doornkampt_at_vertis.nl)

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Wed Sep 06 1995 - 00:00:00 CEST