关于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

我有更好的答案

      

提问者采纳

检举| 2011-10-13 10:56

同时删除多行，把这些删除的行插入到另一张表中还学要的什么条件么？
直接获取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