PLSQL trigger - update records based on select query

Question

I have three tables:

HouseMode:
mode_id (INT, PK)
switch (CHAR 1BYTE)

ModeDevices:
modedevice_id (INT, PK)
house_mode (INT, FK)
houseroomdevice (INT, FK)

HouseRoomDevices:
houseroomdevice_id (INT, PK)
switch (CHAR 1BYTE)

I would like to have a trigger which updates switches from HouseRoomDevices table after updating switch in HouseMode table.

My trigger:

CREATE OR REPLACE TRIGGER switch
BEFORE UPDATE
ON HouseMode
FOR EACH ROW
BEGIN
  UPDATE houseroomdevices
  SET switch = :NEW.switch
  WHERE EXISTS(SELECT houseroomdevice_id FROM houseroomdevices INNER JOIN modedevices ON houseroomdevice = houseroomdevice_id WHERE house_mode = :NEW.mode_id);
END;

But when I try to update record:

UPDATE HouseMode
SET switch = 1
WHERE mode_id = 1;

It updates all records from HouseRoomDevices table.

Believe it should be AFTER UPDATE ON HouseMode as per your statement — mahi_0707
– mahi_0707, Commented Jun 20, 2015 at 19:08

Pavel Gatnar · Accepted Answer · 2015-06-20 13:13:16Z

1

Your update statement is wrong, because your WHERE condition is always true. Use this one:

UPDATE houseroomdevices
SET switch = :NEW.switch
WHERE houseroomdevice_id IN  (SELECT houseroomdevice FROM modedevices WHERE house_mode = :NEW.mode_id);

edited Jun 20, 2015 at 13:13

answered Jun 20, 2015 at 12:58

Pavel Gatnar

4,0532 gold badges22 silver badges29 bronze badges

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

2 Comments

Rahul Over a year ago

Pavel, that's a wrong syntax. You will have to use a subquery to achieve that.

Rahul Over a year ago

Alright, that UPDATE .. JOIN syntax is not supported in Oracle. Moreover, I actually don't see any issue in OP's posted update query except that his ON clause is bit ODD and he supposed to use a AFTER trigger whereas he is using a BEFORE trigger.

Collectives™ on Stack Overflow

PLSQL trigger - update records based on select query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related