1

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;
Improve this question
7
  • 1
    Shouldn't you be using a BEFORE update trigger for this, and raising an exception if the id is invalid? Commented Jul 23, 2011 at 17:11
  • 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. Commented Jul 23, 2011 at 17:18
  • @Mat is there a NOP for oracle? how can I abort the statements from being executed from within the trigger? Commented Jul 23, 2011 at 18:06
  • @Buffalo: what is a NOP? Commented Jul 23, 2011 at 18:14
  • @Mat NO-OPERATION. If a condition is met inside my BEFORE INSERT trigger (new.IDCity > 20), I want to abort the insert. Commented Jul 23, 2011 at 18:31

3 Answers 3

Reset to default
9

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.

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

2 Comments

this is for an assignment. I must use some kind of trigger; this came to mind first.
Even then, you'd definitely want a BEFORE to check for that. As I said, that's really not what triggers are used for though, and if I'd be your teacher I'd put a quite crappy grade for a use-case such as that... A common thing to use them for (at least one that I've seen in a ton of business apps) is auditing changes to a table. Pretty sure you can find other examples on google if you need them.
5

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;
Improve this answer

1 Comment

I amended the trigger to fire on UPDATE as well.
4

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.