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