Oracle SQL Trigger

1
votes

I want to prevent the database from storing any values bigger than 20 into a table.

CREATE OR REPLACE TRIGGER Dont_Allow
AFTER INSERT ON Cities
FOR EACH ROW

WHEN (new.IDCity > 20)

BEGIN
   dbms_output.put_line('  Failed to insert ' || :new.IDCity);
   delete from orase where IDCity=:new.IDCity;
END;

While this does work in terms of not actually adding anything with an ID > 20, every time the trigger tries to do its magic, this shows up:

ORA-04091: table SYSTEM.ORASE is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.DONT_ALLOW", line 6
ORA-04088: error during execution of trigger 'SYSTEM.DONT_ALLOW'

What's a proper way of doing what I want?

EDIT:

I've decided to use a trigger for this:

After a new row is inserted into Employees, a trigger checks the new guy's salary and if it's above 21 units / hour, it takes 5% off management's bonus. Lame, but hey - I'm using a trigger to solve a problem I don't have: the outcome won't be pretty.

CREATE OR REPLACE TRIGGER Bite_Bonus
AFTER INSERT ON Employees
FOR EACH ROW

WHEN (new.HourSalary > 20)

BEGIN
   update Management set Bonus = Bonus - 5/100 * Bonus;
END;
3
sqloracletriggers
Shouldn't you be using a BEFORE update trigger for this, and raising an exception if the id is invalid? - Mat
See my answer for a proper solution for this. IF, however you do use a trigger, then AFTER is definitely wrong. You need a BEFORE INSERT or BEFORE UPDATE trigger there, and, as Mat said, raise an exception if necessary. - TC1
@Mat is there a NOP for oracle? how can I abort the statements from being executed from within the trigger? - Buffalo
@Buffalo: what is a NOP? - Mat
@Mat NO-OPERATION. If a condition is met inside my BEFORE INSERT trigger (new.IDCity > 20), I want to abort the insert. - Buffalo

3 Answers

9
votes

You shouldn't be using a TRIGGER for that, you should be using a CHECK, like CONSTRAINT city_id_below_20 CHECK (IDCity < 20). You can use ALTER TABLE ADD CONSTRAINT to put it on an existing table.

5
votes

As TC1 indicated, the proper way to enforce this sort of requirement is to use a constraint.

If you are forced to use the inferior approach because this is a school assignment, you most likely want to raise an exception in your trigger

CREATE OR REPLACE TRIGGER Dont_Allow
  BEFORE INSERT OR UPDATE ON Cities
  FOR EACH ROW
  WHEN (new.IDCity > 20)
BEGIN
  RAISE_APPLICATION_ERROR( -20001, 'IDCity cannot exceed 20 so rejecting invalid value: ' || :new.IDCity );
END;
4
votes

If you need to use a trigger for this, make it a BEFORE INSERT trigger, not an AFTER INSERT - you don't want that insert to happen at all. Trying to "undo" it after the fact is not a good approach.

To abort the insert, all you need to do is raise an exception within that trigger. Probably the best thing for this is to raise an application error.