cherioss cherioss - 7 months ago 13
SQL Question

trigger for delete row with reference to another deleted row in another table [solved]

[Solved, really bad idea delete table with trigger]

I need help with my trigger. I am doing trigger in Oracle for delete rows in first table with foreigner keys references to second table where is deleted row with primary key with value like foreign key in first table.

I have these tables:

CREATE TABLE Room (
id_room NUMBER(5) NOT NULL,
.
.
price VARCHAR(10) NOT NULL,

PRIMARY KEY(id_mistnosti),

);

CREATE TABLE item1 (
id_room NUMBER(5) NOT NULL,
.
.

FOREIGN KEY(id_room) REFERENCES Room
);

CREATE TABLE item2(
id_room NUMBER(5) NOT NULL,
.
.
FOREIGN KEY(id_room ) REFERENCES Room
);


I have table room, which has 2 items, so I need delete these 2 items when I delete their room.

Now I am trying delete only one item:

CREATE OR REPLACE TRIGGER removeRoomsItems
BEFORE DELETE ON Room
FOR EACH ROW
WHEN (:Room.id_mistnosti = :item1.id_mistnosti)
BEGIN
DELETE FROM item1;
END;
/


My SQLdeveloper writes that error is in clause WHEN.
I need some guide with deleting both of items.

Answer

expresion in trigger is bad. Trigger can looks like this:

CREATE OR REPLACE TRIGGER removeRoomsItems
BEFORE DELETE ON Room
FOR EACH ROW
BEGIN
  DELETE FROM item1 WHERE id_room = :old.id_room ;
END;

In pl/sql function don't use WHEN, but use IF,ELSE and END IF;.

But you don't need this trigger, use cascade FK, so u need create table this way:

CREATE TABLE item2(
  id_room  NUMBER(5) NOT NULL,
  .
  .
  FOREIGN KEY(id_room ) REFERENCES Room ON DELETE CASCADE;
);

Never use triggers like this, it's very bad way.