CODEWITHSUNDEEP
databaseoracle-databasetriggersconstraints
Robert
Robert

Reputation: 197

Triggers in Oracle to keep data integrity

I have got a database with stores and rental copies. Each rental copy is assigned to a store. I would like to create a trigger which, if you delete a certain store, it will assign all copies from that store to another , predefined store (cvr).

I have tried like this:

 CREATE OR REPLACE TRIGGER delete_trig
 BEFORE DELETE ON Store
 FOR EACH ROW
 BEGIN
 UPDATE RentalCopy SET cvr = 123456789
 WHERE cvr = :old.cvr;
 END;

I get an error which says I have violated a previously added constraint (which takes care of the foreign key between these 2 tables) , as it has found a child record. How can I fix it?

Table definitions for these tables: (relevant part)

RentalCopy:

   barcode INTEGER NOT NULL,
   CVR INTEGER NOT NULL,    
   PRIMARY KEY(barcode),
   CONSTRAINT fk_storeinfo
   FOREIGN KEY (CVR) REFERENCES Store

Store:

 CVR INTEGER NOT NULL,  
 store_name VARCHAR2(30) NOT NULL,                     
  PRIMARY KEY(CVR)

Upvotes: 2

Views: 188

Answers (1)

Arnab Bhagabati
Arnab Bhagabati

Reputation: 2715

Looking at your code, it seems like your table RentalCopy is connected to Store by a foreign key on the column(s) CVR of both the tables.

So, a CVR value in RentalCopy must have a corresponding cvr on the Store table, failing which you will get a foreign key error.

The error you are getting is possibly because you are trying to delete/update a cvr record from Store (because then the forign key for the table RentalCopy has no cvr as child in Store table), and not because of any trigger syntax error

Upvotes: 1

Related Questions