CODEWITHSUNDEEP
sqloracle-databasetriggers
Enrichman
Enrichman

Reputation: 11337

Statement trigger in Oracle?

I'm studying for a DBMS exam and I was trying to do some triggers. No problem with "standard" row triggers but the problem came up with a statement trigger.

The exercise is to create a trigger that raise an exception if the sum of the values of a certain column is updated below a minimum value (30).

So the table is something like:

ID | VALUE
----------
 1 |  23
 2 |  11
 3 |  14

and the update will do something like

UPDATE TABLE SET VALUE = VALUE - 10

and this should throw an exception because the sum before is 48 and after the update would be 18.

I've tried with a row trigger but doing the select on the same table will throw a "mutating table" warning, so looking for this I came up that this should due to a poor trigger design.

Probably I should implement a statement trigger but seems that I'm not able to access the new_table or the old_table, and I don't know how to write them. That's lame because they seems quite simple but I couldn't find anything useful or any example.

Any help?

Thanks in advance!

EDIT:

and also the new_table/old_table references seems to exist in Oracle..

Upvotes: 2

Views: 503

Answers (2)

Mateus Schneiders
Mateus Schneiders

Reputation: 4903

Try something like this:

CREATE OR REPLACE TRIGGER trg_xyz
AFTER UPDATE ON tableX
DECLARE
  v_sum number;
BEGIN

  SELECT SUM(VALUE) into v_sum
  FROM tableX;

  IF v_sum < 30 THEN
    raise_application_error(-20001,"ERROR MESSAGE");
  END IF;
END;
/

Upvotes: 2

Dale M
Dale M

Reputation: 2473

I do not know Oracle syntax but I believe new_table is the same as INSERTED in SQL Server; if so something like this:

CREATE TRIGGER TableName_U
BEFORE UPDATE
IF (SELECT SUM(VALUE) FROM new_table) < 30 THEN
    BEGIN
        raise error syntax
        abort update syntax
    END

Upvotes: 0

Related Questions