SQL-DELETE触发器练习
&练习一
如下所示三张表( student,grade,student_updata_before ):
student表
grade表
Student_update_before表
# 触发器需要实现一下要求:将学生被删除前的信息记录到student_updata_before 表中,并记录操作的用户ip和修改时间,并在INFOR字段备注操作属性为“删除学生数据”。
触发器如下(如有问题,欢迎指出):
1 CREATE TRIGGER De_stu 2 ON student 3 FOR DELETE 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 @Ubsid int,@O_sid int,@O_sname varchar(20),@O_sage int,@O_sgname varchar(20),@inf varchar(20),@test varchar(100); 13 SELECT @inf = USER_NAME(); 14 SELECT TOP 1 @O_sid=Sid,@O_sname=Sname,@O_sage=Sage,@O_sgname=Sg_name FROM deleted; 15 SET @test ='USER:'+ @inf +'|Delete Data:' + CONVERT(varchar,@O_sid) +' '+ @O_sname +' '+ CONVERT(varchar,@O_sage) +' '+ @O_sgname + ' 删除学生数据'; 16 IF (SELECT COUNT(1) FROM Student_update_before) = 0 17 SET @Ubsid = 1; 18 ELSE 19 SET @Ubsid = (SELECT MAX(Ubsid) FROM Student_update_before) + 1; 20 INSERT INTO Student_update_before VALUES(@Ubsid,@test,GETDATE()); 21 END 22 GO
# 触发器需要实现一下要求:删除班级信息时,将将学生表中班级为删除班级的所有学生信息插入到毕业学生信息表(his_student )中,然后将学生表中的学生删除。
*注意:his_student 表结构与student表一致,需提前创建
触发器如下(如有问题,欢迎指出):
1 CREATE TRIGGER De_grade 2 ON grade 3 FOR DELETE 4 AS 5 IF ( SELECT COUNT(1) FROM DELETED ) > 1 6 BEGIN 7 RAISERROR('每次只能删除一条数据!请重新选择!',16,10) 8 END 9 ELSE 10 BEGIN 11 insert INTO his_student SELECT * FROM student WHERE Sg_name = (SELECT TOP 1 Gname FROM deleted); 12 DELETE FROM student WHERE Sg_name = (SELECT TOP 1 Gname FROM deleted); 13 end
我只是一个快乐的搬运工,只是想自己汇总资料,方便查看
真正的博文请点击各标题就可以跳转过去;若侵犯到您的利益,请联系我,我立即删除!