SQL触发器问题

[vanDavid]

要实现的功能是：在A数据库生成一张结算单，包含4个表, Check,CheckGoods,CheckDetail,CheckVipAmout;
要同时在B数据库的同样的表（表结构也一样），生成数据，但是触发器只能在B数据库生成一个主表，Check,
其他3个表都不能生成

我把明细表CheckGoods的代码固定了，还是没有生效
INSERT INTO jeans20171211.dbo.CheckGoods SELECT CheckGoodsID,CheckID, '88888888','',
Quantity,UnitPrice,DiscountPrice,DisCount,BalancePrice,0,Status,'',NotTaxPrice,TaxRate,CostPrice,NULL,
'',Integral,'' FROM dbo.CheckGoods WHERE CheckID = 'KE17012180'
这一句，
但是下面这句 INSERT INTO TriggerTest(TriggerText)VALUES(@SQL) 是起作用的

触发器：
ALTER TRIGGER tgr_Check_Update
ON dbo.[Check]
FOR INSERT
AS
DECLARE @CheckID char(10) ,@Manual_ID VARCHAR(50),@SQL NVARCHAR(4000),
@CheckDate DATETIME,@Remark VARCHAR(200),
@Operator CHAR(10),@VIP_Card VARCHAR(50),@Status BIT, @IntegralAmount NUMERIC(18,2),
@Posted BIT,@PostedDate DATETIME,@PostUser CHAR(10),@input_date DATETIME


SELECT @CheckID = CheckID,@Manual_ID = Manual_ID,@Status = Status, @IntegralAmount = IntegralAmount,
@CheckDate = CheckDate,@Remark = Remark,@Operator = Operator,@VIP_Card = VIP_Card,
@Posted = Posted,@PostedDate = PostedDate
FROM INSERTED WHERE VIP_Card <> '' AND Customer_ID <> 'MVIPHX' AND Customer_ID <> 'JVIPHX'; --必须要有VIP卡号,防止循环插入

SELECT @Posted = Posted FROM dbo.[Check] WHERE CheckID = @CheckID
IF((@Posted = 1)AND(@checkId <> ''))
BEGIN
INSERT INTO jeans20171211.dbo.[Check]
( CheckID ,Warehouse_No ,Customer_ID ,ClassName , CheckDate ,
Remark ,Modi_Date ,Operator ,CheckQt ,PaymentWay ,PaymentWay1 ,
VIP_Card ,Class ,Type ,Status ,Posted ,PostedDate ,PostUser ,Money ,
Money1 ,Input_Date ,BuisnessManID ,Manual_ID ,CheckTime ,TotalMoney ,FormulaID ,
Stamp ,UsedStamp ,BuyType ,PaymentWay2 ,PaymentWay3 ,Money2 ,Money3 ,
MachineID ,IntegralAmount ,TokenCoilAmount ,DiscountAmount ,JiZhang ,JzOperator ,
JzDate ,BusinessType , Weather ,Temperature1 ,Temperature2 ,MarketCheckID ,
MarketVip ,MarketSPromoID ,CheckOrderID ,CostAmount ,IsAfterBalance ,
MarketVipCardType ,DegressionIntegral ,DegressionMoney ,EqualMoney
)
VALUES ( @CheckId , -- CheckID - char(10)
'' , -- Warehouse_No - varchar(50)
'MVIPHX' , -- Customer_ID - char(10)
'' , -- ClassName - char(2)
@CheckDate , -- CheckDate - datetime
@Remark , -- Remark - varchar(200)
@CheckDate , -- Modi_Date - datetime
@Operator , -- Operator - char(10)
0 , -- CheckQt - int
'' , -- PaymentWay - varchar(50)
'' , -- PaymentWay1 - varchar(50)
@VIP_Card , -- VIP_Card - varchar(50)
'' , -- Class - char(2)
0 , -- Type - smallint
@Status , -- Status - bit
@Posted , -- Posted - bit
@PostedDate , -- PostedDate - datetime
@Operator , -- PostUser - char(10)
NULL , -- Money - numeric
NULL , -- Money1 - numeric
@CheckDate , -- Input_Date - datetime
'' , -- BuisnessManID - varchar(10)
'MVIPHX', -- Manual_ID - varchar(50)
@CheckDate , -- CheckTime - datetime
NULL , -- TotalMoney - numeric
'' , -- FormulaID - char(10)
0 , -- Stamp - int
0 , -- UsedStamp - int
'' , -- BuyType - varchar(20)
'' , -- PaymentWay2 - char(10)
'' , -- PaymentWay3 - char(10)
NULL , -- Money2 - numeric
NULL , -- Money3 - numeric
'' , -- MachineID - varchar(20)
@IntegralAmount , -- IntegralAmount - numeric
NULL , -- TokenCoilAmount - numeric
NULL , -- DiscountAmount - numeric
0 , -- JiZhang - bit
'' , -- JzOperator - char(10)
NULL , -- JzDate - datetime
'' , -- BusinessType - varchar(20)
'' , -- Weather - varchar(20)
0 , -- Temperature1 - smallint
0 , -- Temperature2 - smallint
'' , -- MarketCheckID - varchar(50)
'' , -- MarketVip - varchar(50)
'' , -- MarketSPromoID - varchar(50)
'' , -- CheckOrderID - varchar(20)
NULL , -- CostAmount - numeric
NULL , -- IsAfterBalance - bit
'' , -- MarketVipCardType - varchar(50)
0 , -- DegressionIntegral - int
NULL , -- DegressionMoney - numeric
NULL -- EqualMoney - numeric
)

SET @SQL = ''
--INSERT INTO jeans20171211.dbo.CheckGoods SELECT CheckGoodsID,CheckID, '88888888','',
--Quantity,UnitPrice,DiscountPrice,DisCount,BalancePrice,0,Status,'',NotTaxPrice,TaxRate,CostPrice,NULL,
--'',Integral,'' FROM dbo.CheckGoods WHERE CheckID = ' + @CheckId + '
INSERT INTO jeans20171211.dbo.CheckGoods SELECT CheckGoodsID,CheckID, '88888888','',
Quantity,UnitPrice,DiscountPrice,DisCount,BalancePrice,0,Status,'',NotTaxPrice,TaxRate,CostPrice,NULL,
'',Integral,'' FROM dbo.CheckGoods WHERE CheckID = 'KE17012180'

INSERT INTO TriggerTest(TriggerText)VALUES(@SQL)

--EXEC(@SQL)--插入到jeans20171211数据库的CheckGoods

SET @SQL = ''
SET @SQL = 'INSERT INTO jeans20171211.dbo.CheckDetail
SELECT CheckGoodsID,''99'',''0'',ISNULL(S1,0)+ISNULL(S2,0)+ISNULL(S3,0)+ISNULL(S4,0)+ISNULL(S5,0) +ISNULL(S6,0)+ISNULL(S7,0)
+ISNULL(S8,0)+ISNULL(S9,0)+ISNULL(S10,0)+ISNULL(S11,0)+ISNULL(S12,0)+ISNULL(S13,0) +ISNULL(S14,0)+ISNULL(S15,0)+ISNULL(S16,0)
+ISNULL(S17,0)+ISNULL(S18,0)+ISNULL(S19,0)+ISNULL(S20,0)+ISNULL(S21,0)+ISNULL(S22,0),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
FROM dbo.CheckDetail WHERE LEFT(CheckGoodsID,10) = ''' + @CheckId + ''''

INSERT INTO TriggerTest(TriggerText)VALUES(@SQL)

EXEC(@SQL)--插入到CheckDetail

SET @SQL = ''
SET @SQL = 'INSERT INTO jeans20171211.dbo.CheckVipAmount
SELECT CheckID,VIP,Amount,ExtString FROM dbo.CheckVipAmount WHERE CheckID = ''' + @CheckId + ''''

INSERT INTO TriggerTest(TriggerText)VALUES(@SQL)

EXEC(@SQL)
END

[唐诗三百首]

如果程序没有报错, 应该是触发器里的程序逻辑问题.

[vanDavid]

为什么我把触发器的FOR INSERT 改成for Update主明细表都可以传过去了，这点我始终想不通，这应该是插入操作，为什么更新也会触发

[OwenZeng_DBA]

引用 2 楼 jimmy178292 的回复:

为什么我把触发器的FOR INSERT 改成for Update主明细表都可以传过去了，这点我始终想不通，这应该是插入操作，为什么更新也会触发

1.检查下环境吧，是测试环境吗，排查下有其他人使用这个表的干扰 2.在触发器里面加入日志 update 应该是不会触发器insert触发器的