触发器的简单学习

--这个语句只是执行主表的单条数据操作

CREATE TRIGGER tri_subject_delete
ON PE_KX_Subject FOR DELETE
AS
BEGIN
DECLARE @ID INT
DECLARE @ExaPaperId INT
SELECT @ID=(SELECT ID FROM DELETED)
SELECT @ExaPaperId=(SELECT ExaPaperId FROM PE_KX_Record pkep WHERE pkep.ID=@ID)
DELETE FROM PE_KX_Record WHERE ExaPaperId=@ExaPaperId
DELETE FROM PE_KX_ExaPaper WHERE SubjectId=@ID
DELETE FROM PE_KX_TestTitle WHERE SubjectId=@ID
END

--若想要触发器执行主表多条数据操作

CREATE TRIGGER tri_subject_delete
ON PE_KX_Subject FOR DELETE
AS
BEGIN
DELETE FROM PE_KX_Record WHERE ExaPaperId IN (SELECT ExaPaperId FROM PE_KX_Record pkep WHERE pkep.ID IN(SELECT ID FROM DELETED))
DELETE FROM PE_KX_ExaPaper WHERE SubjectId IN (SELECT ID FROM DELETED)
DELETE FROM PE_KX_TestTitle WHERE SubjectId IN (SELECT ID FROM DELETED)
END

 

 

原因是因为

DECLARE @ID INT
DECLARE @ExaPaperId INT
这类变量只能存储的是一个数值类型、字符串类型、时间等一维的数据结构类型，不能存储表格

比如说当你想要删除多条记录

如：

SELECT 1 AS ID UNION SELECT 2

 

你若想删除它 又不能定义一个变量接收它，只能用以下方法删除：

DELETE FROM TABLE1  WHERE ID IN (SELECT 1 AS ID UNION SELECT 2)