CREATE TRIGGER sss ON 表 FOR DELETE AS DECLARE @affect_count INT , @deleltedID INT
SELECT @affect_count = @@ROWCOUNT
--没有删除的数据 IF @affect_count = 0 RETURN --只有一条删除数据 IF @affect_count = 1 BEGIN SELECT @deleltedID = FItemID FROM DELETED INSERT INTO AAA(FTableName, FDeletedID) VALUES ('T_Organization' , @deleltedID) END ELSE --批量删除 BEGIN DECLARE deletedCuror CURSOR FOR SELECT FItemID FROM DELETED
OPEN deletedCuror FETCH NEXT FROM deletedCuror INTO @deleltedID
WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO aaa(FTableName, FDeletedID) VALUES ('T_Organization' , @deleltedID)
FETCH NEXT FROM deletedCuror INTO @deleltedID END
CLOSE deletedCuror DEALLOCATE deletedCuror END这想问一下,这里需要用游标吗?
CREATE TRIGGER sss ON 表 FOR DELETE AS 创建了这样的触发器。是不是删除一条记录就触发一次,如果批量删除,也只触发一次啊? ------------------------------------------------------- 是的.deleted表中存储删除的所有记录
游标可以简化为: CREATE TRIGGER sss ON 表 FOR DELETE AS if @@rowcount = 0 returnINSERT INTO aaa(FTableName, FDeletedID) select 'T_Organization' ,FItemID FROM DELETED
FOR DELETE
AS
DECLARE @affect_count INT , @deleltedID INT
SELECT @affect_count = @@ROWCOUNT
--没有删除的数据
IF @affect_count = 0
RETURN
--只有一条删除数据
IF @affect_count = 1
BEGIN
SELECT @deleltedID = FItemID FROM DELETED
INSERT INTO AAA(FTableName, FDeletedID)
VALUES ('T_Organization' , @deleltedID)
END
ELSE
--批量删除
BEGIN
DECLARE deletedCuror CURSOR FOR
SELECT FItemID FROM DELETED
OPEN deletedCuror
FETCH NEXT FROM deletedCuror
INTO @deleltedID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO aaa(FTableName, FDeletedID)
VALUES ('T_Organization' , @deleltedID)
FETCH NEXT FROM deletedCuror
INTO @deleltedID
END
CLOSE deletedCuror
DEALLOCATE deletedCuror
END这想问一下,这里需要用游标吗?
FOR DELETE
AS
创建了这样的触发器。是不是删除一条记录就触发一次,如果批量删除,也只触发一次啊?
-------------------------------------------------------
是的.deleted表中存储删除的所有记录
CREATE TRIGGER sss ON 表
FOR DELETE
AS
if @@rowcount = 0
returnINSERT INTO aaa(FTableName, FDeletedID)
select 'T_Organization' ,FItemID
FROM DELETED