2020-11-05

事务完整

 

create  database  sale
go
use   sale
go
create table goods
(
id  int primary  key   identity(1,1),
name   varchar(20)  not  null,
count  int   check(count>=0)
)

create  table  sale
(
id  int primary key identity(1,1),
gid  int references goods(id),
time datetime default(getdate()),
count int check(count>0)
)

insert   into  goods  values('宝马',10)
go

select * from goods

--购买一个减少一个

go
create trigger salegoods
on  sale for insert
as
declare  @gid int,@count int 
select @gid=gid,@count=COUNT from inserted
update  goods set count=count-@count where id=@gid
go

insert into sale values (1,getdate(),1);

--一个购买多个
go
create trigger saleupdate
on  sale for update
as
declare  @gid int,@count int 
select @gid=gid,@count=COUNT from inserted
declare  @gid2 int,@count2 int 
select @gid2=gid,@count2=COUNT from deleted
update  goods set count=count-(@count-@count2) where id=@gid
if(@@error<>0)
rollback  tran;
go

update  sale set  count=3  where  id=2

go

--删除

create   trigger   saledelete
on  sale  for   delete
as

rollback  tran;
go

delete   from  sale  where  id=1

go

select  * from  goods

select  *   from  sale