CODEWITHSUNDEEP
sqloracle-databasetriggers
Buffalo
Buffalo

Reputation: 4042

Oracle SQL Trigger

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;

Upvotes: 1

Views: 1720

Answers (3)

Justin Cave
Justin Cave

Reputation: 231661

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;

Upvotes: 5

Mat
Mat

Reputation: 206699

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.

Upvotes: 4

TC1
TC1

Reputation: 1

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.

Upvotes: 9

Related Questions