Re: BEFORE INSERT TRIGGER and DELETE FROM

You are trapping the exception. Let it go, re-raise it, or raise another. Also, if you raise the exception on the INSERT, you don't need the DELETE.

And, you are really painting yourself into a corner by using dbms_output.put_line() in a trigger. I hope that's only for debugging.

"Louie" <hlouiehlouie_at_hotmail.com> wrote in message news:eca7e663.0211051756.b636c68_at_posting.google.com...
> Please assist me with this. I'm trying to restrict an INSERT INTO to
> the products table based on if the new item's product_price makes the
> sum(product_price) exceed a certain amount. The trigger gets created
> properly but the INSERT will still insert the new record. What did I
> do wrong?
>
>
>
> CREATE OR REPLACE TRIGGER insert_product BEFORE INSERT ON products
> FOR EACH ROW
> DECLARE
> new_id NUMBER;
> total_price NUMBER;
> cannot_change EXCEPTION;
> BEGIN
> SELECT sum(product_price) + :NEW.product_price INTO total_price FROM
> products;
> IF (total_price > 160) THEN
> RAISE cannot_change;
> END IF;
> EXCEPTION WHEN cannot_change THEN
> DELETE FROM products WHERE product_id = :NEW.product_id;
> dbms_output.put_line('Cannot Insert Item');
> END insert_product;
> /
Received on Wed Nov 06 2002 - 15:49:19 CET