sql 触发器针对一张表数据写入另一张表的增删改

最新推荐文章于 2023-01-10 15:08:09 发布

weixin_30907523

最新推荐文章于 2023-01-10 15:08:09 发布

阅读量677

点赞数 1

文章标签：数据库

原文链接：http://www.cnblogs.com/yangpeng-jingjing/p/9811289.html

版权

本文深入探讨了SQL触发器的使用，展示了如何在不同情况下（插入、删除、更新）运用触发器来维护数据一致性。特别关注了在学生表操作时触发器如何同步更新班级人数的实现细节。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

ALTER TRIGGER [dbo].[tri_test2]
ON [dbo].[student]
for INSERT,DELETE,UPDATE
AS
BEGIN

if not exists (select * from deleted) --新增
insert student2(stu_id,stu_name,stu_sex,stu_birthday,class_id)
select stu_id,stu_name,stu_sex,stu_birthday,class_id from inserted
else if not exists(select * from inserted) --删除
delete student2 from deleted d where student2.stu_id=d.stu_id
else if (select count(*) from deleted)>0 and (select count(*) from inserted)>0 --更新
update student2 set stu_name=i.stu_name ,stu_sex=i.stu_sex from student2 b, inserted i,deleted d
where i.stu_id=d.stu_id and i.stu_id=d.stu_id

END

--------------------------------- 添加时修改另一表数据

ALTER TRIGGER [dbo].[stu_insert]
ON [dbo].[student]
for insert
AS
BEGIN

update class set class_num=class_num+1
where class_id=(select class_id from inserted)

END

转载于:https://www.cnblogs.com/yangpeng-jingjing/p/9811289.html