Question

关于SQL触发器的问题 ？同时删除多行，这些行插入到另一张表中？

USE [school]
GO
/****** 对象: Trigger [dbo].[classlist_delete] 脚本日期: 10/11/2011 15:49:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER [dbo].[classlist_delete]
ON [dbo].[classlist]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare
@ID int,
@StuID varchar(20),
@Student varchar(50),
@fid int,
@StuClass int,
@Teacher int,
@CTime int,
@Term int,
@Remark varchar(50),
@ispay int,
@absent int,
@cid int

--insert into delstudent(StuID,StuName,Sex,entime,School,FatherName,FaTel,MotherName,MoTel,fid) values(@StuID,@StuName,@Sex,@entime,@School,@FatherName,@FaTel,@MotherName,@MoTel,@id)
insert into delclasslist(StuID,Student,fid,StuClass,Teacher,CTime,Term,Remark,ispay,absent,cid) select StuID,Student,fid,StuClass,Teacher,CTime,Term,Remark,ispay,absent,id from deleted where fid=@id
END

Answer 1

同时删除多行，把这些删除的行插入到另一张表中还学要的什么条件么？
直接获取deleted内的删除数据插入到目标表中就好了吧？如下试试？
CREATE TRIGGER [dbo].[classlist_delete]
ON [dbo].[classlist]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF EXISTS(SELECT 1 FROM DELETED)--存在删除
BEGIN
BEGIN TRY
BEGIN TRAN --开始事务
--执行插入
insert into delclasslist(StuID,Student,fid,StuClass,Teacher,CTime,
Term,Remark,ispay,absent,cid)
select StuID,Student,fid,StuClass,Teacher,CTime,Term,Remark,ispay,absent,id
from deleted
COMMIT TRAN --提交事务
END TRY
BEGIN CATCH
--如果插入失败,则回滚事物
IF XACT_STATE()<>0
ROLLBACK TRAN
END CATCH
END

END
---------------------------------------------
MS-SQL SERVER2005及以上版本用output子句便可以实现在删除时便可以往另一张表中插入删除信息:如下例子：
--------------------1、INSERT+OUTPUT子句----------------------
--演示表变量
DECLARE @T TABLE(ID INT)
--在INSERT语句中使用OUTPUT子句
INSERT @T
OUTPUT inserted.ID
SELECT object_id
FROM sys.objects O

-----------------2、UPDATE+OUTPUT子句-----------
--更新：先删后改
UPDATE A
SET ID=O.object_id+2
OUTPUT O.name,deleted.ID AS ID_BEFORE_UPDATE,inserted.ID AS ID_AFTER_UPDATE
FROM @T A,sys.objects O
WHERE A.ID=O.object_id

--SELECT * FROM sys.objects
-----------------3、DELETE+OUTPUT子句-----------
--用于保存输出结果的表变量
DECLARE @RE TABLE(
ID INT,
NAME VARCHAR(100)
--NAME SYSNAME
)
--删除
DELETE A
OUTPUT DELETED.ID,'被删除的ObjectName:'+O.name
INTO @RE
FROM @T A,sys.objects O
WHERE A.ID=O.object_id
--显示结果
SELECT * FROM @RE

Answer 2

最明显的一处：@id没有赋值。。