SQL-INSERT触发器练习
&练习一
有这样的一个基础表A,字段包括:id、type、value、create_time,主要是记录某个类型的状态变化时间和值。在插入类型(type)为‘runtime’ 的数据时,根据前后变化的值,更新一条该type的前后两条时间组合的信息到另一张表B。
定义:value为布尔型,true-->false时,为停止时间,false-->true时,为运行时间
具体要求:表A插入一条runstate的数据时,需要在表B插入一条停止或运行的信息。
eg:
A表
B表
触发器如下(如有问题,欢迎指出):
1 CREATE TRIGGER RUN_STATE 2 ON A 3 FOR INSERT 4 AS 5 IF( (SELECT COUNT(1) FROM inserted) > 1 ) 6 BEGIN 7 RAISERROR('一次只能插入一条数据!',16,10); 8 rollback; 9 END 10 ELSE 11 BEGIN 12 IF( (SELECT TOP 1 type FROM inserted)='runtime' ) ----插入的数据是否为runtime状态信息 13 BEGIN 14 IF EXISTS ( SELECT 1 FROM B WHERE type = 'runtime') -----B表中是否已经创建了runtime信息表 15 BEGIN 16 DECLARE @Old_state varchar(10),@create_time smalldatetime; 17 SET @Old_state = (SELECT state FROM B WHERE type = 'runtime'); 18 SET @Create_time = (SELECT TOP 1 create_time FROM inserted); 19 if @Old_state = '运行' 20 BEGIN 21 update B SET end_time = @Create_time WHERE type = 'runtime'; 22 update B SET state = @Create_time WHERE type = '停止'; 23 END 24 ELSE 25 BEGIN 26 update B SET start_time = @Create_time WHERE type = 'runtime'; 27 update B SET state = @Create_time WHERE type = '运行'; 28 END 29 END 30 else 31 BEGIN 32 DECLARE @id int,@type varchar(20),@value bit; 33 SELECT TOP 1 @id=id,@type=type,@value=value,@create_time=create_time FROM inserted; 34 IF @value = 1 35 INSERT B (id,type,start_time,state) values(@id,'runtime',@create_time,'运行'); 36 ELSE 37 INSERT B (id,type,end_time,state) values(@id,'runtime',@create_time,'停止'); 38 END 39 END 40 END 41 GO
&练习二
需要用到的表如下(卷烟库存表【CI_list】,卷烟销售表【CS_list】):
--业务规则:库存金额 = 库存数量 * 库存单价
--业务规则:销售金额 = 销售数量 * 销售单价
eg1:强制执行业务规则,保证插入的数据中,库存金额 = 库存数量 * 库存单价,且库存金额不为空
触发器如下(如有问题,欢迎指出):
1 CREATE TRIGGER T_INSERT_CS 2 ON CS_list 3 FOR INSERT 4 AS 5 DECLARE @i_num int,@i_uprice money,@i_amount money; 6 IF ( (SELECT COUNT(*) FROM inserted)>1 ) 7 BEGIN 8 RAISERROR('一次只能插入一行数据',16,10); 9 ROLLBACK; 10 END 11 ELSE 12 BEGIN 13 SELECT TOP 1 @i_num=i_num,@i_uprice=i_uprice,@i_amount=i_amount FROM inserted; 14 IF ISNULL(@i_amount,1)=1 OR @i_amount != @i_num*@i_uprice 15 BEGIN 16 RAISERROR('插入的数据不符合业务规则!已根据规则修改数据',16,10); 17 UPDATE CS_list SET i_amount = @i_num*@i_uprice WHERE cs_brand IN (SELECT TOP 1 cs_brand FROM inserted) 18 END 19 END 20 GO 21 22 INSERT INTO CS_list values('红塔山新势力',2,30,60); 23 INSERT INTO CS_list (cs_brand,i_num,i_uprice) values('红1',2,20); 24 INSERT INTO CS_list values('云南映像',2,30,70); 25 GO
eg2:如果销售的卷烟品牌不存在库存或者库存为零,则返回错误;否则则自动减少[卷烟库存表]中对应品牌卷烟的库存数量和库存金额。并且满足业务规则
触发器如下(如有问题,欢迎指出):
1 CREATE TRIGGER T_INSERT_CI 2 ON CI_list 3 FOR INSERT 4 AS 5 IF ( (SELECT COUNT(*) FROM inserted)>1 ) 6 BEGIN 7 RAISERROR('一次只能插入一行数据',16,10); 8 ROLLBACK; 9 END 10 ELSE 11 BEGIN 12 DECLARE @CI_BRAND varchar(40),@CS_BRAND varchar(40); 13 DECLARE @s_num int,@s_uprice money,@s_amount money; 14 SELECT TOP 1 @CI_BRAND=ci_brand,@s_num=s_num,@s_uprice=s_uprice,@s_amount=s_amount FROM inserted; 15 IF not EXISTS (SELECT 1 FROM CS_list WHERE cs_brand=@CI_BRAND) 16 BEGIN17 RAISERROR('库存中不含当前要销售的卷烟',16,10); 18 ROLLBACK; 19 END 20 ELSE IF (SELECT i_num FROM CS_list WHERE cs_brand=@CI_BRAND) = 0 21 BEGIN 22 RAISERROR('库存中已经没有当前要销售的香烟',16,10); 23 ROLLBACK; 24 END 25 ELSE 26 BEGIN 27 IF ISNULL(@s_amount,1)=1 OR @s_amount != @s_num*@s_uprice 28 BEGIN 29 RAISERROR('插入的数据不符合业务规则!已根据规则修改数据',16,10); 30 UPDATE CI_list SET s_amount = @s_num*@s_uprice WHERE ci_brand = (SELECT TOP 1 ci_brand FROM inserted) 31 END 32 UPDATE CS_list SET i_num=i_num-@s_num WHERE cs_brand = (SELECT TOP 1 ci_brand FROM inserted); 33 UPDATE CS_list SET i_amount=i_num*i_uprice WHERE cs_brand = (SELECT TOP 1 ci_brand FROM inserted); 34 END 35 END
&练习三
如下所示三张表( student,grade,student_updata_before ):
student表
grade表
Student_update_before表
触发器需要实现一下要求:
-
如果学生所在的班级存在,则班级表学生数+1。
-
如果学生所在的班级不存在,则在班级表中新建班级,并将该学生加入到该班级中。
触发器如下(如有问题,欢迎指出):
1 CREATE TRIGGER Add_stu 2 ON student 3 FOR INSERT 4 AS 5 IF ( SELECT COUNT(1) FROM inserted ) > 1 6 BEGIN 7 RAISERROR('每次只能插入一条数据',16,10); 8 ROLLBACK; 9 END 10 ELSE 11 BEGIN 12 DECLARE @Sg_name varchar(30); 13 SELECT TOP 1 @Sg_name = Sg_name FROM inserted; 14 IF EXISTS ( SELECT 1 FROM grade WHERE Gname = @Sg_name ) 15 BEGIN 16 DECLARE @num1 int; 17 SET @num1 = (SELECT COUNT(*) FROM student WHERE Sg_name = @Sg_name); 18 UPDATE grade SET Gnum = @num1 WHERE Gname = @Sg_name; 19 END 20 ELSE 21 BEGIN 22 DECLARE @gid int; 23 IF (SELECT COUNT(1) FROM grade) = 0 24 SET @gid = 1; 25 ELSE 26 SET @gid = (SELECT MAX(Gid) FROM grade ) + 1; 27 declare @num int; 28 INSERT INTO grade VALUES(@gid,@Sg_name,1,''); 29 END 30 END 31 GO
&练习四
#准备工作:创建员工employee表、部门变动历史dept_history表,工资变动历史sal_history表
#1,创建员工employee表:
1 CREATE TABLE employee( 2 eid varchar(20) PRIMARY KEY, 3 ename varchar(20), 4 dept varchar(20), 5 salary int, 6 uptime date, 7 status int 8 ) 9 go
#2,创建部门变动历史dept_history表
CREATE TABLE dept_history( did int identity(1,1) primary key, eid varchar(20), olddept varchar(20), newdept varchar(20), uptime date, FOREIGN KEY(eid) REFERENCES employee(eid) ) go
#3,创建工资变动历史sal_history表
1 CREATE TABLE sal_history( 2 sid int identity(1,1) primary key, 3 eid varchar(20), 4 oldsal varchar(20), 5 newsal varchar(20), 6 uptime date, 7 FOREIGN KEY(eid) REFERENCES employee(eid) 8 ) 9 go
触发器实现功能:当新职工入职时,员工信息表将插入1条数据。同时,触发器在部门变动历史中增加1条记录,其中olddept值为null;在工资变动历史中增加1条记录,其中oldsal值为0。
触发器如下(如有问题,欢迎指出):
CREATE TRIGGER Add_em ON employee FOR INSERT AS IF ( SELECT COUNT(1) FROM inserted ) > 1 BEGIN RAISERROR('每次只能插入一条数据!请重新输入!',16,10) END ELSE BEGIN DECLARE @eid varchar(20),@dept varchar(20),@salary int; SELECT TOP 1 @eid = eid ,@dept = dept ,@salary = salary FROM inserted; INSERT INTO dept_history (eid,olddept,newdept,uptime) values(@eid,null,@dept,GETDATE()); INSERT INTO sal_history (eid,oldsal,newsal,uptime) values(@eid,0,@salary,GETDATE()); END
我只是一个快乐的搬运工,只是想自己汇总资料,方便查看
真正的博文请点击各标题就可以跳转过去;若侵犯到您的利益,请联系我,我立即删除!