sql-server - 删除触发器不起作用,而添加触发器正常工作?
问题描述
当添加触发器正常工作时,删除触发器不起作用。
它应该像这样工作:“当我删除 score 表中 subject_id = 1 且 score > 8 的记录时,exellentEng 将在类表中为-1”我不知道为什么。帮我
ALTER TRIGGER [dbo].[deleteExellentEnglishStudent]
ON [dbo].[dbo_score]
AFTER DELETE AS
BEGIN
UPDATE x SET
exellent_Eng -=1
FROM dbo.dbo_class x
JOIN dbo.dbo_student s ON s.class_id = x.class_id
JOIN dbo.dbo_score c ON c.student_id = s.student_id
WHERE (c.subject_id = 1 AND c.score >= 8)
and c.score_id in (SELECT Deleted.score_id FROM Deleted)
END
ALTER TRIGGER [dbo].[addExellentEnglishStudent] ON [dbo].[dbo_score]
AFTER INSERT AS
BEGIN
UPDATE x SET
exellent_Eng += 1
FROM dbo.dbo_class x
JOIN dbo.dbo_student s ON s.class_id = x.class_id
JOIN dbo.dbo_score c ON c.student_id = s.student_id
WHERE (c.subject_id = 1 AND c.score >= 8)
AND c.score_id IN
(select Inserted.score_id from Inserted)
END
解决方案
该行将不存在于dbo_score
表中,因为它是一个after
触发器,因此该行已被删除。而是Deleted
直接使用伪表,例如
ALTER TRIGGER [dbo].[deleteExellentEnglishStudent]
ON [dbo].[dbo_score]
AFTER DELETE AS
BEGIN
UPDATE x SET
exellent_Eng -=1
FROM dbo.dbo_class x
JOIN dbo.dbo_student s ON s.class_id = x.class_id
JOIN dbo.Deleted c ON c.student_id = s.student_id
WHERE (c.subject_id = 1 AND c.score >= 8)
END
注意:正如我在您的另一个问题上所说,如果它们每次都绝对计算值,而不是处理差异,您可能会发现编写和维护这些触发器更容易。然后您可以使用相同的逻辑进行插入、更新和删除,例如:
ALTER TRIGGER [dbo].[deleteExellentEnglishStudent]
ON [dbo].[dbo_score]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
UPDATE x SET
exellent_Eng = (select count(*) from dbo.dbo_score where c.subject_id = 1 AND c.score >= 8)
FROM dbo.dbo_class x
JOIN dbo.dbo_student s ON s.class_id = x.class_id
where S.studentid in (select student_id from Inserted union all select student_id from Deleted)
END
推荐阅读
- javascript - encodeURI 和 decodeUri 有什么区别
- excel - 爬取依赖填充下拉列表
- c# - Microsoft.SqlServer.Types 与 .NET 标准不兼容
- java - 问题与 3D 数组使绘制函数行为奇怪
- docker - 尝试连接到 unix:///var/run/docker.sock 的 Docker 守护进程套接字时权限被拒绝
- c++ - 为什么缩小转换不能防止这个不正确类型的 map.insert() 失败?
- sql-server - 基于可选数据的过滤
- linux - 在用户使用 GNOME 登录之前启动应用程序
- python - 19.0.1 更新后使用 pip 命令出错
- ios - 如果您的应用程序播放视频并暂停背景音频,您如何在完成后让背景音频恢复?