Re: Trigger to prevent insert of multiple rows

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) Received on Wed Sep 06 1995 - 00:00:00 CEST