CODEWITHSUNDEEP
sqloracle-databaseplsqltriggers
cherioss
cherioss

Reputation: 93

trigger for delete row with reference to another deleted row in another table

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.

Upvotes: 1

Views: 16308

Answers (2)

Michal Hože
Michal Hože

Reputation: 116

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.

Upvotes: 3

krokodilko
krokodilko

Reputation: 36087

You don't need any trigger in this case.
Just use ON DELETE CASCADE clause:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm

ON DELETE Clause

The ON DELETE clause lets you determine how Oracle Database automatically maintains referential integrity if you remove a referenced primary or unique key value. If you omit this clause, then Oracle does not allow you to delete referenced key values in the parent table that have dependent rows in the child table.

Specify CASCADE if you want Oracle to remove dependent foreign key values.

Specify SET NULL if you want Oracle to convert dependent foreign key values to NULL.

See this simple example:

CREATE TABLE Room (
   id_mistnosti int,
   id_room NUMBER(5) NOT NULL,
    price VARCHAR(10) NOT NULL,
   PRIMARY KEY(id_mistnosti)
 );

CREATE TABLE item1 (
  id_room NUMBER(5) NOT NULL,
  item_name varchar(100),
  FOREIGN KEY(id_room) REFERENCES Room ON DELETE CASCADE
);

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

insert into room values( 1, 1, 100 );
insert into room values( 2, 2, 200 );

insert into item1 values( 1, 'room 1' );
insert into item1 values( 1, 'room 11' );
insert into item1 values( 2, 'room 2' );
insert into item1 values( 2, 'room 22' );

insert into item2 values( 1, 'room 1' );
insert into item2 values( 1, 'room 11' );
insert into item2 values( 2, 'room 2' );
insert into item2 values( 2, 'room 22' );

commit;

and now:

SELECT * FROM item2;

  ID_ROOM ITEM_NAME
---------- ----------
         1 room 1    
         1 room 11   
         2 room 2    
         2 room 22

Delete the room and see how this delete affects items:

delete from room where id_mistnosti = 1;

select * from item2;

   ID_ROOM ITEM_NAME
---------- ----------
         2 room 2    
         2 room 22

Upvotes: 0

Related Questions