PL/SQL Trigger to check value before insert statement

Question

I am new to PL/SQL and I have an issue regarding a trigger I am trying to implement.

The triggers purpose is to check a monetary value before it's inserted into the table to see if someone made a mistake during inserting. If they have, they will be given a message stating the value is incorrect. The values are in the billions so for now I am just checking if the value entered is above 10000 or not.

The trigger I currently have is;

CREATE TRIGGER Check_Value
BEFORE INSERT OR UPDATE OF "Potential Annual Value By 2026" ON AIPOTENTIALVALUEFORHEALTHCARE
BEGIN
IF (NEW."Potential Annual Value By 2026" < 10000.00) THEN
DBMS_OUTPUT.put_line('Value typed was incorrect');
ELSIF (NEW."Potential Annual Value By 2026" >= 10000.00) THEN
INSERT INTO AIPOTENTIALVALUEFORHEALTHCARE VALUES(NEW.ValueID, NEW.ApplicationID, NEW."Application Name", NEW.KeyDriverForAdoptionID, NEW."KeyDriverDescription", NEW."Potential Annual Value By 2026");
END IF;
END;

This will not work due to an error:

PLS-00201: identifier NEW.'Potential Annual Value By 2026' must be declared

My guess is that I have set the trigger incorrectly and that it doesn't know which value to check when it runs the trigger. From some research, I tried to use .NEW to pass the values of the statement into the trigger however I am not sure if this is the correct implementation.

I had tried the method already posted;

CREATE TRIGGER Check_Value
BEFORE INSERT OR UPDATE OF "Potential Annual Value By 2026" ON AIPOTENTIALVALUEFORHEALTHCARE
BEGIN
  IF (:NEW."Potential Annual Value By 2026" < 10000.00) THEN
    DBMS_OUTPUT.put_line('Value typed was incorrect');
  ELSIF (:NEW."Potential Annual Value By 2026" >= 10000.00) THEN
    INSERT INTO AIPOTENTIALVALUEFORHEALTHCARE VALUES
      (:NEW.ValueID, :NEW.ApplicationID, :NEW."Application Name", 
       :NEW.KeyDriverForAdoptionID, :NEW."KeyDriverDescription", 
       :NEW."Potential Annual Value By 2026");
  END IF;
END;

and recieved a different error:

ORA-04082: NEW or OLD references not allowed in table level triggers
04082. 00000 -  "NEW or OLD references not allowed in table level triggers"
*Cause:    The trigger is accessing "new" or "old" values in a table trigger.
*Action:   Remove any new or old references.

If this error is stating I can't use NEW references in a table level trigger, how would I be able to verify the contents of the insert statement before it is committed?

And as an aside, you really, really, REALLY don't want to be nameing columns, or any other objects, in mixed case or with embedded spaces (UPDATE OF "Potential Annual Value By 2026"). Any time you find yourself having to enclose an object name in double-quotes, you need to think again. And again. And again. — EdStevens
– EdStevens, Commented Mar 17, 2021 at 19:46

Tony Andrews · Accepted Answer · 2021-03-17 17:59:05Z

1

You are missing colons before the NEW keywords and the FOR EACH ROW clause. Also you do not need to (and must not) re-issue the INSERT within the trigger, it will happen anyway (if no error is raised):

CREATE TRIGGER Check_Value
BEFORE INSERT OR UPDATE OF "Potential Annual Value By 2026" ON AIPOTENTIALVALUEFORHEALTHCARE
FOR EACH ROW
BEGIN
  IF (:NEW."Potential Annual Value By 2026" < 10000.00) THEN
    RAISE_APPLICATION_ERROR(-20001, 'Value typed was incorrect');
  END IF;
END;

I'm sure this is just a training example, but DBMS_OUTPUT.PUT_LINE is not a suitable method for raising errors to users as its output can only be seen when using develpper tools like SQL Developer. Use RAISE_APPLICATION_ERROR. Also it doesn't actually raise an exception, so it won't prevent the insert at all.

In fact, this check might be better done with a CHECK constraint - assuming the column value must never be under 10000:

ALTER TABLE AIPOTENTIALVALUEFORHEALTHCARE
   ADD CONSTRAINT AIPOTENTIALVALUEFORHEALTHCARE_CHK_VALUE
      CHECK ("Potential Annual Value By 2026" >= 10000);

edited Mar 17, 2021 at 17:59

answered Mar 17, 2021 at 17:32

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

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

4 Comments

JohnSortley24 Over a year ago

I should've mentioned that I have already tried this method and it did not work, I will edit the post with the errors received. Yes, this is just a small play with Oracle SQL Developer for my own interest however I will look into Raise_Application_Error.

Tony Andrews Over a year ago

@JohnSortley24 answer updated - you need FOR EACH ROW

JohnSortley24 Over a year ago

Thank you, I thought FOR EACH ROW was used to loop through every row in the table which is why I didn't include it. You're logic is much better for this and I will keep the insert statement in mind next time. If I add the check constraint to each field I want to validate, can I customize what error is thrown if the condition is not met? Theoretically I can name the constraint the error message right? Or is there a better way

Belayer Over a year ago

Using check constraint is way to go, when violated you will get an exception "ORA-02290: check constraint " followed by scheme name and constraint name. See fiddle

Collectives™ on Stack Overflow

PL/SQL Trigger to check value before insert statement

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related