Lopan Lopan - 4 years ago 256
SQL Question

Column depending on other table

I have a simple DB which has two tables, serie and season.
Serie has this structure:

create table serie(
name varchar2(30) not null,
num_seasons number(2,0),
launch date,
constraint pk_serie primary key(name)
);


Whereas season has this other structure:

create table season(
name_serie varchar2(30) not null,
num_season number(2,0) not null,
launch date not null,
end date,
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)
);


For example, for a serie with two seasons (num_seasons=2), it would have in season table two rows, num_season=1 and num_season=2.

I would like the num_seasons column in table serie to be a count of how many rows are in season table with the name of the serie. In fact, I want that column to depend in changes in the season table, if you insert a new season of a serie, increase the num_seasons value by 1.

Thank you for your help :)

Answer Source

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 season table:

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download