触发器模板

--删除
if exists( select * from dbo.sysobjects where id = OBJECT_ID(N'[dbo].[trig_delete_Ap_CloseBill_extradefine]') and OBJECTPROPERTY(id,N'IsTrigger')=1)
    drop TRIGGER trig_delete_Ap_CloseBill_extradefine
go
CREATE TRIGGER trig_delete_Ap_CloseBill_extradefine
   ON  Ap_CloseBill_extradefine 
   AFTER delete
AS 
BEGIN
    --红字收款单扩展自定义项26，可以联查到源收款单
    if exists(select b.chdefine26 from Ap_CloseBill a inner join deleted b on a.iID = b.iID where a.cVouchType = '49' and  exists (select 1 from Ap_CloseBill c where c.cVouchID = b.chdefine26 and  c.cVouchType = '48'))
    begin
        --红字收款单扩展自定义项25，无法联查到蓝字收款单
        if exists(select  b.chdefine25 from Ap_CloseBill a inner join deleted b on a.iID = b.iID where a.cVouchType = '49' and not  exists (select 1 from Ap_CloseBill c where c.cVouchID = b.chdefine25 and  c.cVouchType = '48'))
        begin
            --回写来源收款单，累计转定金金额（表头自定义项16），可转定金余额（表头自定义项7）
            update Ap_CloseBill set cDefine16 =ISNULL(cdefine16,0)-b.iAmount, cDefine7 =ISNULL(cdefine7,0)+b.iAmount from Ap_CloseBill a 
            inner join ( select iAmount ,b.chdefine26 from Ap_CloseBill a inner join deleted b on a.iID = b.iID where cVouchType = '49') b on a.cVouchID = b.chdefine26 
            and    a.cVouchType='48'
        end
        else
        begin
            DECLARE @cvoucherid NVARCHAR(120)
            select @cvoucherid = b.chdefine25 from Ap_CloseBill a inner join deleted b on a.iID = b.iID where a.cVouchType = '49' and  exists (select 1 from Ap_CloseBill c where c.cVouchID = b.chdefine25 and  c.cVouchType = '48')
            DECLARE @msg NVARCHAR(50)
            SET @msg='请先删除蓝字收款单'+@cvoucherid
            RAISERROR(@msg,16,1)
            ROLLBACK 
        end
    end
End
go