sql server 实验七 触发器.docVIP

实验七 触发器 编写一个触发器，根据发票明细表的内容，生成客户信息 create trigger customerd on dbo.invoice after insert as begin set nocount on declare @name char(16) declare @address char(40) declare @telephone char(20) declare @count int declare @id int select @name=name,@address=address,@telephone=telephone from instered select count(*) from customer where name=@name and address=@address and telephone=@telephone if @count=0 begin select @id=count(*) from customer insert into customer values (@name,@address,@telephone) end end 当提交定单时，库存表中的库存总量需减少相应的订货量 create trigger InsertClientOrder on dbo.orderinfo after insert as begin set nocount on declare @产品ID char(6) declare @订货量 int declare @当前库存 int select @产品ID=产品ID FROM INSERTED SELECT @订货量=订货量 from inserted select @当前库存=库存总量 from dbo.stockinfo if @订货量=@当前库存 update dbo.stockinfo set 库存总量=库存总量-@订货量 where 产品ID=@产品ID else print 库存不足 end现有student表和score表，这两个表之间具有逻辑上的主、外键关系，要求当删除或更新表student中的内容时，同时也删除或更新score表中对应的行。（即是一个删除、更新触发器） create trigger studentch on students for delete,update as declare @de1_count int declare @ins_count int declare @id int select @de1_count=count(*)from deleted select @ins_count=count(*)from instered if @ins_count=0 and @de1_count 0 begin delete from score where id in (select id from delected) end if update(id) begin update score set id=(select id from insered) where id =(select id from deleted) end return 4、创建“宽带业务”数据库，在其中实现如下的业务功能： 1）当向“宽带申请表”中录入数据时，自动增加“宽带信息报表”中对应记录的月申请量； 2）当宽带申请记录已经被受理，将该条数据从“宽带申请表”中删除，并将其自动录入到“申请备案记录表”中。 (1) Create trigger [dbo].[InsrtApplication] on [dbo].[宽带申请表] after insert as begin set nocount on declare @ApplicationDate datetime declare @AccMonth varchar(7) declare @Count int select @ApplicationDate =inserted.申请时间 from inserted set @AccMonth =CONVERT (varchar(7),@ApplicationDate,111) select @Count =COUNT(*) from 宽带信息报表 where 月份=@AccMonth if @Count 0 upda