Creating a Trigger in Oracle

Question

I am trying to create a trigger in Oracle SQLPlus. The trigger deals with two tables: users{id, name, status} offerings{id, title, price, userid, status};

I would like that when the user table is updated an the status of a entry is change to 2 that all offerings that the user has made will be changed to i (for inactive)

CREATE OR REPLACE TRIGGER update_offering_status
BEFORE UPDATE ON users
WHEN (new.status = 2)
FOR EACH ROW
DECLARE
Userid INTEGER;
BEGIN
USERID :=  :old.userid;
    UPDATE offering
    SET status = 'i'
    WHERE userid = old.userid;
END;

I am getting the error ORA-04077: WHEN clause cannot be used with table level triggers. But I am not sure how to do it without a when clause?

Ed Gibbs · Accepted Answer · 2013-12-06 19:55:37Z

5

That's happening because your WHEN clause is in the wrong place. Put it after the FOR EACH ROW and you should be all set:

CREATE OR REPLACE TRIGGER update_offering_status
BEFORE UPDATE ON users
FOR EACH ROW
WHEN (new.status = 2)

answered Dec 6, 2013 at 19:55

Ed Gibbs

26.4k4 gold badges50 silver badges75 bronze badges

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

Comments

Konstantin · Accepted Answer · 2013-12-06 19:55:05Z

1

You should be able to put it into IF & END IF instead of WHEN

answered Dec 6, 2013 at 19:55

Konstantin

3,30418 silver badges21 bronze badges

Collectives™ on Stack Overflow

Creating a Trigger in Oracle

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related