BEFORE INSERT TRIGGER and DELETE FROM
Date: 5 Nov 2002 17:56:54 -0800
Message-ID: <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
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 - 02:56:54 CET