1. 关于SQL触发器的问题 同时删除多行，这些行插入到另一张表中
同时删除多行，把这些删除的行插入到另一张表中还学要的什么条件么？
直接获取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
2. SQL数据库 触发器实现多表同步插入
create TRIGGER [dbo].[tr_tb_erp_make_Pack_insert]
ON [dbo].[tb_erp_make_Pack]
FOR INSERT
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
declare @out_Flag int=0
declare @out_Rtn varchar(40)=''
declare @smsg nvarchar(400)
DECLARE @v_barCode VARCHAR(20)
DECLARE @v_cInvCode NVARCHAR(20)
DECLARE @v_packID INT
DECLARE @v_packDate DATETIME
DECLARE @v_userID INT
begin tran
DECLARE Cur CURSOR FOR
SELECT barCode,cInvCode,packID,packDate,userID FROM insertedOPEN Cur
FETCH NEXT FROM Cur INTO @v_barCode,@v_cInvCode,@v_packID,@v_packDate,@v_userIDWHILE @@FETCH_STATUS=0
BEGIN
SET @out_Flag = 0
SET @out_Rtn='添加成功!'
IF EXISTS(SELECT 1 FROM tb_erp_make_barCode WHERE barCode=@v_barCode)BEGIN
UPDATE dbo.tb_erp_make_barCode SET cInvCode=@v_cInvCode,packID=@v_packID,packDate=@v_packDate,vFlag=0 WHERE barCode=@v_barCodeIF @@error <> 0
BEGIN
SET @out_Flag=1
SET @out_Rtn='触发器修改(UPDATE)错误！'
SET @smsg='错误：'+@out_Rtn
ROLLBACK TRAN
RAISERROR (@smsg,11,1)
RETURN
END
END
ELSE
BEGIN
INSERT INTO dbo.tb_erp_make_barCode
(
barCode ,packID ,packDate ,cInvCode
)
VALUES (
@v_barCode ,@v_packID ,GETDATE() ,@v_cInvCode)
IF @@error <> 0
BEGIN
SET @out_Flag=2
SET @out_Rtn='触发器新增(INSERT)错误！'
SET @smsg='错误：'+@out_Rtn
ROLLBACK TRAN
RAISERROR (@smsg,11,1)
RETURN
END
END
FETCH NEXT FROM Cur INTO @v_barCode,@v_cInvCode,@v_packID,@v_packDate,@v_userIDEND
CLOSE Cur
DEALLOCATE Cur
COMMIT TRAN
RETURN