create trigger testtrigger on 销售表 for insert as begin --修改库存 update a set a.图书数量 = a.图书数量 - b.图书数量 from 库存表 a join inserted b on a.图书编号 = b.图书编号 --删除为0的记录 delete from 库存表 where 图书数量 = 0 and 图书编号 in (select 图书编号 from inserted) end
create trigger s on book_sales for insert as begin declare @book_id varchar(10),@qty int,@m int set @book_id=(select book_id from inserted) set @qty=(select qty from inserted) set @m=(select qty from book_store where book_id=@book_id) if(@qty=@m) begin delete from book_store where book_id=@book_id end else update book_store set qty=qty-@qty where book_id=@book_id end
book_id 书号 qty 数量
book_sales销售表 book_store 库存表
create trigger udt_booksalei on booksale for insert as declare @book_name varchar(8000) declare @book_qty int select @book_name = book_name , @book_qty = book_qty from inserted update book_stock set book_qty = bookqty - @book_qty where book_name = @book_name select @book_qty = book_qty from book_stock where book_name = @book_name if(@book_qty = 0) begin delete from book_stock where book_name = @book_name end
on 销售表 for insert
as
begin
--修改库存
update a
set a.图书数量 = a.图书数量 - b.图书数量
from 库存表 a join inserted b on a.图书编号 = b.图书编号
--删除为0的记录
delete from 库存表 where 图书数量 = 0 and 图书编号 in (select 图书编号 from inserted)
end
on book_sales
for insert
as
begin
declare @book_id varchar(10),@qty int,@m int
set @book_id=(select book_id from inserted)
set @qty=(select qty from inserted)
set @m=(select qty from book_store where book_id=@book_id)
if(@qty=@m)
begin
delete from book_store where book_id=@book_id
end
else
update book_store set qty=qty-@qty where book_id=@book_id
end
book_store 库存表
create trigger udt_booksalei
on booksale
for insert
as
declare @book_name varchar(8000)
declare @book_qty int
select @book_name = book_name , @book_qty = book_qty
from inserted update book_stock
set book_qty = bookqty - @book_qty
where book_name = @book_name select @book_qty = book_qty
from book_stock
where book_name = @book_name if(@book_qty = 0)
begin
delete from book_stock
where book_name = @book_name
end