1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
|
create table parking
(
id int not null identity(1,1) primary key,
nom varchar(30) not null unique
);
create table emplacement
(
id int not null identity(1,1) primary key,
parking_id int not null references parking(id),
num int not null,
unique (parking_id, num)
);
go
create view v_parking (id, nom, nb_emplacements)
as
select p.id, p.nom, count(*)
from parking p
inner join emplacement e on e.parking_id = p.id
group by p.id, p.nom;
go
create trigger trg_v_parking_i
on v_parking
instead of insert
as
begin
insert into parking (nom)
select nom from inserted;
with cpt (num)
as
(
select 1
union all
select cpt.num + 1 from cpt where num < 10000
)
insert into emplacement (parking_id, num)
select p.id, c.num
from inserted i
inner join parking p on p.nom = i.nom
inner join cpt c on c.num <= i.nb_emplacements
OPTION (MAXRECURSION 9999);
end;
go |
Partager