CODEWITHSUNDEEP
oracle-databasetriggers
JS_Diver
JS_Diver

Reputation: 786

Trigger to prevent inserting 2 same values in one table

I have little problem with programming trigger for my dtb. I need to control 2 values in one 1 table. I have table called Concert and it has 2 foreign keys: 1 is the id of table Place. Second is not important for this I think.

Concert: id_concert, id_place<fk>, id_organizer<fk>, date, name, sponsor

Place: id_place, name, capacity, adress, town

What I want to eliminate is, that 2 concerts organized at same day cannot be on one place. So, I need to somehow control that user cannot insert the same date and same place for concert if there already concert with this values exists.

Thank you very much for your suggestions and sorry for bad english.

Upvotes: 0

Views: 442

Answers (1)

lefticus
lefticus

Reputation: 3416

You need to add a unique constraint on your Concert table that consists of the (id_place, date) pair. This would instruct the database engine to not allow more than one Concert in the same place at the same time.

For Oracle, information can be found here: http://www.techonthenet.com/oracle/unique.php

CREATE TABLE Concert
(   
 ... (filled in with your existing table definition)
 CONSTRAINT concert_place_unique UNIQUE (id_place, date)
);

or to alter an existing table:

ALTER TABLE Concert
add CONSTRAINT concert_place_unique UNIQUE (id_place, date);

Constraints are the proper way to handle this condition, not triggers. Constraints are database intrinsic and have no race conditions and prevent the data from being added in the first place.

Upvotes: 3

Related Questions