Scribd
Upload
9 views3 pages

SQL Table and Trigger Creation Guide

The document outlines the creation of several SQL tables including STATION, ACTIVITY, CLIENT, and STAY, along with their relationships and constraints. It also details the implementation of triggers for managing stay counts, validating stay dates, and adjusting activity prices. Additionally, it includes alterations to the STAY table and various error handling mechanisms for invalid data entries.

Uploaded by

aliismaili842
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
9 views3 pages

SQL Table and Trigger Creation Guide

The document outlines the creation of several SQL tables including STATION, ACTIVITY, CLIENT, and STAY, along with their relationships and constraints. It also details the implementation of triggers for managing stay counts, validating stay dates, and adjusting activity prices. Additionally, it includes alterations to the STAY table and various error handling mechanisms for invalid data entries.

Uploaded by

aliismaili842
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

create table STATION (

stationName varchar(15) primary key,capacity int, locationvarchar(20),


région varchar(20), price decimal)

create table ACTIVITY (stationName varchar(15),


descriptionAct varchar(50) ,
price decimal,
constraint pk primary key (stationName , descriptionAct),
constraint fk foreign key (stationName) references STATION (stationName) )

create table CLIENT (id int primary key, firstName varchar(15), lastname varchar(15), city
varchar(15), region varchar(15), balance decimal)

create table STAY (id int ,


station varchar(15), startdate date, nbPlaces int, constraint pk1
primary key (id, station, startdate),
constraint fk1 foreign key (station) references STATION (stationName), constraint fk2
foreign key (id) references Client (id))

1.

Alter table STATION add nb_Stay int default 0

2.
create trigger Trig_new_stay On STAY after insert
as
declare @stationNom varchar (15)
set @stationNom = (select top 1 station from inserted)
Begin update STATION
set nb_Stay = nb_Stay + 1 where stationNom = @stationNom
End
3.
create trigger Trig_delete_stay on STAY after delete
as
declare @station varchar(15)
set @station = (select top 1 station from deleted)
update STATION set nb_Stay = nb_Stay - 1 where stationNom =
@station

4.
create trigger Trig_update_stay on STAY after insert, delete, update
as
declare @stationNomI varchar(15), @stationNomD varchar(15) set
@stationNomI = (select top 1 station from inserted) set @stationNomD =
(select top 1 station from deleted)

if (
((select count(*) from inserted) >0 ) and ((select count(*) from deleted) >0))
Begin update STATION
set nb_Stay = nb_Stay -1 where stationNom = @stationNomD

update STATION
set nb_Stay = nb_Stay + 1 where stationNom = @stationNomI
end
else if ((select count(*) from inserted) >0)
Begin update STATION
set nb_Stay = nb_Stay + 1 where stationNom = @stationNomI
end else begin update STATION
set nb_Stay = nb_Stay -1 where stationNom = @stationNomD end

5.

table STAY add endDate date

6.

create Trigger trig6 on Stay instead of insert


as
declare @db date , @ed date
set @db = (select top 1 dateDébut from inserted) set @ed =
(select top 1 endDate from inserted) if (@db > @ed)
Begin
raiserror ('séjour invalide', 16, -1) end

OR
create Trigger trig6 on Stay after insert
as
declare @db date , @ed date
set @db = (select top 1 dateDébut from inserted) set @ed =
(select top 1 endDate from inserted)

if (@db > @ed)


Begin
raiserror ('séjour invalide', 16, -1)
rollback end

[Link] trigger trig 7 on activity after


insert
As
Begin
Declare @s varchar(20);
Set @s=select top 1 stationname from
inserted
If (@s in (select stationname from
activity)
Begin
raiseerror(“invalid activity”)
Rollback
End;
End;
[Link] trigger trig 8 on activity before
insert
as
begin
set [Link]=[Link]*1.1
end
OR
Create trigger trig 8 on activity after
insert
As
Begin
Update activity
Set price=price*1.1
End;

You might also like