TRIGGER DE MAJ

create database exercice

-- creation de latable article
create table article(
numart int primary key,
stock int)
-- creation de latable ligne commande

 create table lgcommande(
numlgcmd int primary key,
lgart int foreign key references article,
 quantite int) 
---------------------------------------------------------------------
-- Ce trigger va mettre a jour les stocks des articles qui sont suivi en lgcommande

create trigger majarticle
on lgcommande
after delete,insert ,update
as
begin
declare @s int,@lgart int,@q int
select @lgart =lgart,@q=quantite from deleted
update article
set stock=stock+@q where @lgart=numart
set @q=0
select @lgart =lgart,@q=quantite from inserted
select @s=stock from article where @lgart=numart
if @s< @q 
begin
print 'desole stock n est pas disponible commadez une quantite<= '+convert(varchar,@s)
rollback transaction
end
else begin
update article
set stock=stock-@q where @lgart=numart
end
end
-----------------------------------------------------------------------------
/* Exemple de l'execution du trigger */
select * from lgcommande
select* from article

insert into article values(1,100)
insert into lgcommande values(65,1,26)

select * from lgcommande
select* from article

delete from article where numart=1
select * from lgcommande
select* from article

delete lgcommande where numlgcmd=65

select * from lgcommande
select* from article

update lgcommande
set quantite=11