CODEWITHSUNDEEP
sqloracle-databasetriggersoracle-sqldeveloper
MmM ...
MmM ...

Reputation: 131

Oracle - How to use trigger "after insert" between 2 tables?

I create database. I have tables, which are connected.

book: id_book, title, author, ..., subsidiary(foreign key to table subsidiary)

borrowing: id_borr, visitor, ... book(foreign key to table book)

subsidiary: id_subs, city, capacity, ...

For now i have:

create table subsidiary
(
   id_subs              smallint                       primary key,
   city                 varchar(50),
   capacity             integer
);

create table book
(
   id_book              integer                       primary key,
   title                varchar(1000),
   author               varchar(100),
   subsidiary           smallint                      null,
   FOREIGN KEY (subsidiary) REFERENCES subsidiary(id_subs)
);

create table borrowing
(
   id_borr              integer                       primary key,
   book                 integer                       null,
   visitor              varchar(100),
   FOREIGN KEY (book) REFERENCES book(id_book)
);

I would like to behaviour like: Book has got a subsidiary. But after insert of borrowing(of some book), book change subsidiary to NULL.

I found only this example(and similar): http://www.techonthenet.com/oracle/triggers/after_insert.php , but i am not still sure about that.

Very thank you.

Upvotes: 0

Views: 803

Answers (1)

San
San

Reputation: 4538

Assuming that you do not want any operation on subsidiary table, you can write a trigger as follows.

CREATE OR REPLACE TRIGGER trg_borrowing 
  AFTER INSERT ON borrowing
 FOR EACH ROW
BEGIN
  UPDATE book
     SET subsidiary = NULL
   WHERE id_book = :NEW.book;
END;
/

Upvotes: 1

Related Questions