SQL 觸發程序(TRIGGER)－DUCK，Fearless！

----UPDATE 之後所做動作

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER [dbo].[ANF24_UPD] ON [dbo].[T0RXANF24]
AFTER UPDATE AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
---修改[燃料]
UPDATE dbo.T0RXANF2 SET T0RXANF2.ANF231=T0RXANF23.ANF231, T0RXANF2.ANF232=T0RXANF23.ANF232,T0RXANF2.ANF233=T0RXANF23.ANF233,T0RXANF2.ANF234=T0RXANF23.ANF234,T0RXANF2.ANF235=T0RXANF23.ANF235
FROM T0RXANF23,T0RXANF2
WHERE T0RXANF2.CoId=T0RXANF23.CoId and T0RXANF2.DpId=T0RXANF23.DpId AND T0RXANF2.YrId=T0RXANF23.YrId AND T0RXANF2.SsId=T0RXANF23.SsId AND T0RXANF2.AQL12=T0RXANF23.AQL12 AND T0RXANF2.MtId=T0RXANF23.MtId AND T0RXANF2.ANF231=T0RXANF23.ANF231
-- Insert statements for trigger here

END

----INSERT之後所做動作

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [dbo].[ANF24_INS] ON [dbo].[T0RXANF24]
AFTER INSERT AS

--設定sort
declare @no1 nchar(2)
declare @name1 nchar(10)
DECLARE sort1 CURSOR
FOR
SELECT distinct av FROM dbo.[T0RXANF24]
OPEN sort1
DECLARE @_s1 nvarchar(50)
DECLARE @_w1 nvarchar(50)
FETCH NEXT FROM sort1
into @_s1
WHILE(@@FETCH_STATUS=0) --4.跑迴圈(判斷有沒有值)
BEGIN
with [T0RXANF24]
as
(
SELECT *, row_number() over(order by id) as aa
FROM dbo.[T0RXANF24]
WHERE av=@_s1-- and coid=coid and dpid=dpid
)
update [T0RXANF24]
set sort=aa

FETCH NEXT from sort1
into @_s1
END
CLOSE sort1 --5.關閉與Table關連
DEALLOCATE sort1
---END sort

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if(SELECT Count(T0RXANF2.MtId) FROM INSERTED,T0RXANF2 WHERE T0RXANF2.CoId=INSERTED.CoId and T0RXANF2.DpId=INSERTED.DpId AND T0RXANF2.YrId=INSERTED.YrId AND T0RXANF2.SsId=INSERTED.SsId AND T0RXANF2.AQL12=INSERTED.AQL12 AND T0RXANF2.MtId=INSERTED.MtId AND T0RXANF2.ANF241=INSERTED.ANF241 AND T0RXANF2.sort=INSERTED.sort) >0
BEGIN
UPDATE dbo.T0RXANF2 SET T0RXANF2.ANF241=T0RXANF24.ANF241, T0RXANF2.ANF242=T0RXANF24.ANF242, T0RXANF2.ANF243=T0RXANF24.ANF243, T0RXANF2.ANF244=T0RXANF24.ANF244
FROM T0RXANF24,T0RXANF2
WHERE T0RXANF2.CoId=T0RXANF24.CoId and T0RXANF2.DpId=T0RXANF24.DpId AND T0RXANF2.PsId=T0RXANF24.PsId AND T0RXANF2.YrId=T0RXANF24.YrId AND T0RXANF2.SsId=T0RXANF24.SsId AND T0RXANF2.AQL12=T0RXANF24.AQL12 AND T0RXANF2.MtId=T0RXANF24.MtId AND T0RXANF2.ANF241=T0RXANF24.ANF241
END
ELSE
BEGIN
---將[原料]填入值
INSERT INTO dbo.T0RXANF2 (CoId, DpId, PsId,YrId, SsId, AQL12, MtId, ANF241, ANF242, ANF243,ANF244 ) SELECT CoId, DpId, PsId,YrId, SsId, AQL12, MtId, ANF241, ANF242, ANF243,ANF244 FROM INSERTED
---將[操作時數]填入值
if(SELECT Count(T0RXANF22.MtId) FROM INSERTED,T0RXANF22 WHERE T0RXANF22.CoId=INSERTED.CoId and T0RXANF22.DpId=INSERTED.DpId AND T0RXANF22.YrId=INSERTED.YrId AND T0RXANF22.SsId=INSERTED.SsId AND T0RXANF22.AQL12=INSERTED.AQL12 AND T0RXANF22.MtId=INSERTED.MtId )>0
BEGIN
UPDATE dbo.T0RXANF2 SET T0RXANF2.ANF221=T0RXANF22.ANF221
FROM T0RXANF22,T0RXANF2
WHERE T0RXANF2.CoId=T0RXANF22.CoId and T0RXANF2.DpId=T0RXANF22.DpId AND T0RXANF2.PsId=T0RXANF22.PsId AND T0RXANF2.YrId=T0RXANF22.YrId AND T0RXANF2.SsId=T0RXANF22.SsId AND T0RXANF2.AQL12=T0RXANF22.AQL12 AND T0RXANF2.MtId=T0RXANF22.MtId
END
---將[燃料]填入值
if(SELECT Count(T0RXANF23.MtId) FROM T0RXANF2,T0RXANF23 WHERE T0RXANF23.CoId=T0RXANF2.CoId and T0RXANF23.DpId=T0RXANF2.DpId AND T0RXANF23.YrId=T0RXANF2.YrId AND T0RXANF23.SsId=T0RXANF2.SsId AND T0RXANF23.AQL12=T0RXANF2.AQL12 AND T0RXANF23.MtId=T0RXANF2.MtId AND T0RXANF23.sort=T0RXANF2.sort)>0
BEGIN
UPDATE dbo.T0RXANF2 SET T0RXANF2.ANF231=T0RXANF23.ANF231, T0RXANF2.ANF232=T0RXANF23.ANF232, T0RXANF2.ANF233=T0RXANF23.ANF233, T0RXANF2.ANF234=T0RXANF23.ANF234, T0RXANF2.ANF235=T0RXANF23.ANF235
FROM T0RXANF23,T0RXANF2
WHERE T0RXANF2.CoId=T0RXANF23.CoId and T0RXANF2.DpId=T0RXANF23.DpId AND T0RXANF2.PsId=T0RXANF23.PsId AND T0RXANF2.YrId=T0RXANF23.YrId AND T0RXANF2.SsId=T0RXANF23.SsId AND T0RXANF2.AQL12=T0RXANF23.AQL12 AND T0RXANF2.MtId=T0RXANF23.MtId AND T0RXANF2.sort=T0RXANF23.sort
END
---將[產品]填入值
if(SELECT Count(T0RXANF25.MtId) FROM T0RXANF2,T0RXANF25 WHERE T0RXANF25.CoId=T0RXANF2.CoId and T0RXANF25.DpId=T0RXANF2.DpId AND T0RXANF25.YrId=T0RXANF2.YrId AND T0RXANF25.SsId=T0RXANF2.SsId AND T0RXANF25.AQL12=T0RXANF2.AQL12 AND T0RXANF25.MtId=T0RXANF2.MtId AND T0RXANF25.sort=T0RXANF2.sort)>0
BEGIN
UPDATE dbo.T0RXANF2 SET T0RXANF2.ANF251=T0RXANF25.ANF251, T0RXANF2.ANF252=T0RXANF25.ANF252, T0RXANF2.ANF253=T0RXANF25.ANF253, T0RXANF2.ANF254=T0RXANF25.ANF254
FROM T0RXANF25,T0RXANF2
WHERE T0RXANF2.CoId=T0RXANF25.CoId and T0RXANF2.DpId=T0RXANF25.DpId AND T0RXANF2.PsId=T0RXANF25.PsId AND T0RXANF2.YrId=T0RXANF25.YrId AND T0RXANF2.SsId=T0RXANF25.SsId AND T0RXANF2.AQL12=T0RXANF25.AQL12 AND T0RXANF2.MtId=T0RXANF25.MtId
END
-- Insert statements for trigger here
END
END

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

----DELETE之後所做動作

ALTER TRIGGER [dbo].[ANF23_DEL] ON [dbo].[T0RXANF23]
AFTER DELETE AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if(SELECT Count(T0RXANF2.MtId) FROM DELETED,T0RXANF2 WHERE T0RXANF2.CoId=DELETED.CoId and T0RXANF2.DpId=DELETED.DpId AND T0RXANF2.YrId=DELETED.YrId AND T0RXANF2.SsId=DELETED.SsId AND T0RXANF2.AQL12=DELETED.AQL12 AND T0RXANF2.MtId=DELETED.MtId AND T0RXANF2.ANF231=DELETED.ANF231 )>0
BEGIN
UPDATE dbo.T0RXANF2 SET T0RXANF2.ANF231=NULL,T0RXANF2.ANF232=NULL, T0RXANF2.ANF233=NULL, T0RXANF2.ANF234=NULL, T0RXANF2.ANF235=NULL
FROM T0RXANF2,DELETED
WHERE DELETED.CoId=T0RXANF2.CoId and DELETED.DpId=T0RXANF2.DpId AND DELETED.PsId=T0RXANF2.PsId AND DELETED.YrId=T0RXANF2.YrId AND DELETED.SsId=T0RXANF2.SsId AND DELETED.AQL12=T0RXANF2.AQL12 AND DELETED.MtId=T0RXANF2.MtId AND DELETED.ANF231 =T0RXANF2.ANF231
END
ELSE
BEGIN
DELETE dbo.T0RXANF2
SELECT CoId, DpId, PsId,YrId, SsId, AQL12, MtId, ANF231, ANF232,ANF233, ANF234,ANF235 FROM DELETED
END
-- Insert statements for trigger here
END

Trigger