CODEWITHSUNDEEP
sqloracle-databasetriggersraiseerror
Kyle Goodwin
Kyle Goodwin

Reputation: 23

SQL Raise Application Error Trigger

This is throwing "Error: ORA-04082: NEW or OLD references not allowed in table level triggers"

I'm not sure where I'm going wrong. The error number shouldn't make a difference should it?

    CREATE OR REPLACE TRIGGER REJECTION 
BEFORE INSERT OR UPDATE ON TEA_PREFS_T 
DECLARE temp NUMBER;
BEGIN
  SELECT COUNT(*) INTO temp FROM tea_prefs_t WHERE person = :new.drinkerid;
  IF (temp >=10) THEN
    raise_application_error(-20101, 'ERROR: CANNOT INSERT MORE THAN 10');
    ROLLBACK;
  END IF;
END;

Upvotes: 1

Views: 3725

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

As the error suggests, you can only refer to the new and old pseudo-rows in a row-level trigger, not a table-level trigger, which fires once regardless of how many rows were affected by the statement. If you updated two rows with different drinker IDs, which value would the trigger use for its look-up?

To make it a row-level trigger, add FOR EACH ROW:

CREATE OR REPLACE TRIGGER REJECTION 
BEFORE INSERT OR UPDATE ON TEA_PREFS_T 
FOR EACH ROW
DECLARE
  temp NUMBER;
BEGIN
  SELECT COUNT(*) INTO temp FROM tea_prefs_t WHERE person = :new.drinkerid;
  IF (temp >=10) THEN
    raise_application_error(-20101, 'ERROR: CANNOT INSERT MORE THAN 10');
  END IF;
END;
/

You can't commit or rollback from within a trigger; it's up to the transaction doing the insert/update to decide whether to do that.

However, you also can't select from the same table you're inserting into/updating; you'll get a mutating table error from this, at least if you attempt to insert/update multiple rows at once.

Upvotes: 3

Related Questions