mercredi 3 décembre 2014

Prevent row from being inserted with a trigger


I'm trying to prevent a row being inserted if a certain condition is met using a trigger. I'm using a user defined exception to do this, however the row is still being inserted, despite the exception being thrown. I'm not sure why this is happening, I though when an exception was raised the transaction was rolled back and nothing would be inserted. The code is below.



create or replace TRIGGER
CHECK_OPTION
BEFORE INSERT
ON lot_options
FOR EACH ROW

DECLARE
v_allowed_stage NUMBER;
v_actual_completion_date DATE;
option_change_invalid EXCEPTION;
BEGIN
DBMS_OUTPUT.PUT_LINE('Starting the begin');
SELECT stage_stage INTO v_allowed_stage
FROM "TEAM3"."Option" o
WHERE o.option_number = :new.option_number;
DBMS_OUTPUT.PUT_LINE(v_allowed_stage);

DBMS_OUTPUT.PUT('Stage in a cell');

SELECT actual_completion INTO v_actual_completion_date
FROM progress
WHERE stage_stage = v_allowed_stage
AND Lot_lot_number = :new.lot_lot_number;
DBMS_OUTPUT.PUT_LINE('completion date in storage');


IF (v_actual_completion_date IS NOT NULL)
--DBMS_OUTPUT.PUT('condition holds true')
THEN RAISE option_change_invalid;
END IF;

EXCEPTION
WHEN option_change_invalid THEN
DBMS_OUTPUT.PUT_LINE('Invalid Option Change: Option change is not allowed in this stage of progress.');
END;




Aucun commentaire:

Enregistrer un commentaire