Oracle - Trigger to check constraint before insert

Question

i would like to create a simple trigger to check a stored variable from a table.
if the value of the variable is '1', then approve the insertion
else if the value of the variable is '2', then prompt error message.

CREATE OR REPLACE TRIGGER approval 
BEFORE INSERT ON VIP
REFERENCING OLD AS MEMBER
FOR EACH ROW
DECLARE 
  CONDITION_CHECK NUMBER;
BEGIN
  SELECT CONDITION INTO CONDITION_CHECK FROM MEMBER; 
  IF CONDITION_CHECK = '2' THEN
    RAISE_APPLICATION_ERROR (-20000, ' UPGRADE DENIED!');
  END IF;
END;

But this trigger disable all the entries even when the condition value is '1'.

Vincent Malgrat · Accepted Answer · 2012-11-13 17:16:11Z

3

Use AFTER triggers to check conditions: the values can be modified by other BEFORE triggers. In AFTER triggers, you are guaranteed that the value won't be changed afterwards.

It seems you're missing a condition in your SELECT (if as I believe you're trying to get the value from another table):

CREATE OR REPLACE TRIGGER approval 
AFTER INSERT ON VIP
FOR EACH ROW
DECLARE 
  CONDITION_CHECK NUMBER;
BEGIN
  SELECT CONDITION 
    INTO CONDITION_CHECK 
    FROM MEMBER 
   WHERE member_id = :new.member_id; 
  IF CONDITION_CHECK = '2' THEN
    RAISE_APPLICATION_ERROR (-20000, 'UPGRADE DENIED!');
  END IF;
END;

edited Nov 13, 2012 at 17:16

answered Nov 13, 2012 at 17:02

Vincent Malgrat

67.9k9 gold badges122 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Justin Cave · Accepted Answer · 2012-11-13 16:58:10Z

You can simply reference the :new.condition in your trigger

CREATE OR REPLACE TRIGGER approval 
  BEFORE INSERT ON VIP
  FOR EACH ROW
BEGIN
  IF :new.condition = '2' THEN
    RAISE_APPLICATION_ERROR (-20000, ' UPGRADE DENIED!');
  END IF;
END;

It would, however, make little sense to put this sort of logic into a trigger. It would make far more sense to define a CHECK constraint on the column that ensures that the condition is one of an allowed set of values.

Dave Richardson · Accepted Answer · 2012-11-13 16:55:38Z

0

You are referencing the old data rather than the new - presumably that's not what you intended?

answered Nov 13, 2012 at 16:55

Dave Richardson

5,0057 gold badges36 silver badges49 bronze badges

2 Comments

BoltClock Over a year ago

Did you mean for this to be an answer? If so, you may want to make it look... more like an answer.

Dave Richardson Over a year ago

Thanks for that advice. I believe it is an answer, sufficient at least to tell him what the problem is - I don't feel I need to hold hands.

Collectives™ on Stack Overflow

Oracle - Trigger to check constraint before insert

3 Answers 3

Comments

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Related