I have a simple DB which has two tables, serie and season.
Serie has this structure:
create table serie(
name varchar2(30) not null,
constraint pk_serie primary key(name)
create table season(
name_serie varchar2(30) not null,
num_season number(2,0) not null,
launch date not null,
constraint pk_season primary key(name_serie,num_season),
constraint fk_season foreign key(name_serie) references serie(name),
constraint check_time check(launch<end)
The others answer are simply wrong since they are telling you to perform an insert using a select to check how many season exists only once (at insert time).
What it would happens on Update/Delete on season table? The answer is obvious, you will have all counter not aligned and the data will be unrielable.
For this purpose you have to modify the
serie table, in particular:
num_season NUMBER(2,0) DEFAULT 0
and create some TRIGGER on
Triggers are procedures that are stored in the database and are implicitly run, or fired, when something happens.
Traditionally, triggers supported the execution of a PL/SQL block when an INSERT, UPDATE, or DELETE occurred on a table or view. Triggers support system and other data events on DATABASE and SCHEMA. Oracle Database also supports the execution of PL/SQL or Java procedures.
CREATE TRIGGER incSeasonNum AFTER INSERT ON season FOR EACH ROW BEGIN UPDATE serie SET num_seasons = num_seasons + 1 WHERE name = NEW.name_serie; END
Another one in case for any rows deletion:
CREATE TRIGGER decSeasonNum AFTER DELETE ON season FOR EACH ROW BEGIN UPDATE serie SET num_seasons = num_seasons - 1 WHERE name = OLD.name_serie; END
And just to be sure to avoid strange update on serie's name or number:
CREATE TRIGGER incDecSeasonNum AFTER UPDATE ON season FOR EACH ROW BEGIN UPDATE serie SET num_seasons = num_seasons - 1 WHERE name = OLD.name_serie; UPDATE serie SET num_seasons = num_seasons + 1 WHERE name = NEW.name_serie; END
Hope this help.