Trigger: Restricting Inserts

From: <ebjorklu_at_my-deja.com>
Date: Tue, 14 Dec 1999 17:35:43 GMT
Message-ID: <835v5f$cka$1@nnrp1.deja.com>

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. Received on Tue Dec 14 1999 - 11:35:43 CST