| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Trigger: Restricting Inserts
ebjorklu_at_my-deja.com wrote:
>
> Thanks in advance for any info you can provide on
> this one.
>
> Is there any way within a BEFORE INSERT trigger to
> stop a row from being inserted without raising
> any errors? Here's our situation.
>
> We are using an INSERT from a SELECT statement
> like the following (greatly simplified for
> clarity's sake).
>
> INSERT INTO TABLE_A
> ( column1 )
> SELECT column1
> FROM TABLE_B;
>
> We have the following TRIGGER on TABLE_A.
>
> CREATE OR REPLACE TRIGGER
> SEC.TAIR_TRIGGER_1
> BEFORE INSERT
> ON SEC.TABLE_A
> FOR EACH ROW
> DECLARE
> ln_potential_total NUMBER;
> BEGIN
> ln_potential_total := NVL( :NEW.total_1, 0 ) +
> NVL(
> pkg_trigger_1.gn_current_total, 0 );
>
> /* Check if the row does not exceed our goal
> */
> IF ln_potential_total <= pkg_trigger_1.gn_goal
> THEN
> /* Insert the row and adjust the overall
> total */
> pkg_trigger_1.gn_current_total :=
> ln_potential_total;
> ELSE
> /* Mark this row to be deleted */
> :NEW.delete_flg := 'Y';
> /*
> || ??? - Can we replace the above line with
> something that will stop this
> || row from being inserted while still allowing
> the multirow INSERT from
> || SELECT statement to continue inserting rows?
> */
> END IF;
>
> RETURN;
> END TAIR_TRIGGER_1;
>
> We are currently doing a bulk delete (DELETE *
> from TABLE_A WHERE delete_flg = 'Y';) after the
> INSERT statement, but we are hoping to remove this
> and stop the rows from being inserted in the first
> place.
>
> Thanks again.
>
> -Eric
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Take a look at INSTEAD OF triggers.
HTH
--
"Some days you're the pigeon, and some days you're the statue." Received on Tue Dec 14 1999 - 08:07:03 CST
 |
 |