Creating a Trigger in Oracle

oracle, sql, triggers

Solution

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)

Problem

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?

Original source