text - 插入触发器失败后。为什么?
问题描述
我正在针对订单头表 OEORDH 编写插入后触发器,该表需要根据表 ARCUSO 中的一系列其他可选客户值添加到注释字段。我做错了什么?
CREATE TRIGGER [dbo].[tr_OEORDH_AfterInsertUpdate]
ON [dbo].[OEORDH]
AFTER INSERT AS
BEGIN
SET NOCOUNT ON;
DECLARE @Comment AS CHAR(250);
DECLARE @OrdUniq as dec(9);
DECLARE @ShipComplete as CHAR(1);
DECLARE @FreeDepewFreight as CHAR(1);
DECLARE @CPIFreeOver2500 as CHAR(1);
DECLARE @CancelBackOrders as CHAR(1);
DECLARE @PaymentThenInvoice as CHAR(1);
DECLARE @HoldLabelForPayment as CHAR(1);
DECLARE @FreightProgram as CHAR(1);
DECLARE @CustComment as CHAR(60);
DECLARE @CustomerID AS CHAR(12);
/* 从插入的新订单标题行中检索关键数据值 */
SELECT
@Comment = COMMENT,
@OrdUniq = ORDUNIQ,
@CustomerID=CUSTOMER
FROM
inserted;
/* 从客户可选表中检索 19SHIPCMPLT 标志 */
SELECT @ShipComplete = CS.VALUE
FROM dbo.ARCUSO CS
WHERE CS.IDCUST = @CustomerID AND OPTFIELD = '19SHIPCMPLT' ;
/* 如果 ShipComplete Flag = TRUE,则将带有 CRLF 的文本添加到 COMMENT */
IF (@ShipComplete = 'Y')
SET @Comment = 'SHIP COMPLETE ' + CHAR(13)+CHAR(10) + @Comment;
/* 从客户可选表中检索 20FREEDEPEW 标志 */
SELECT @FreeDepewFreight = CS.VALUE
FROM dbo.ARCUSO CS
WHERE CS.IDCUST = @CustomerID AND OPTFIELD = '20FREEDEPEW' ;
/* 如果 Free Frt 到 Depew,NY = TRUE,则将带有 CRLF 的文本添加到 COMMENT */
IF (@FreeDepewFreight = 'Y')
SET @Comment = 'Free freight to Depew, NY ' + CHAR(13)+CHAR(10) + @Comment;
/* 从客户可选表中检索 21CPIFREIGHT 标志 */
SELECT @CPIFreeOver2500 = CS.VALUE
FROM dbo.ARCUSO CS
WHERE CS.IDCUST = @CustomerID AND OPTFIELD = '21CPIFREIGHT' ;
/* 如果 CPIFreeOver2500 Flag = TRUE,则将带有 CRLF 的文本添加到 COMMENT */
IF (@CPIFreeOver2500 = 'Y')
SET @Comment = 'Free freight over $2,500 ' + CHAR(13)+CHAR(10) + @Comment;
/* 从客户可选表中检索 22CANCELBO 标志 */
SELECT @CancelBackOrders = CS.VALUE
FROM dbo.ARCUSO CS
WHERE CS.IDCUST = @CustomerID AND OPTFIELD = '22CANCELBO' ;
/* 如果 CancelBackOrders Flag = TRUE,将带有 CRLF 的文本添加到 COMMENT */
IF (@CancelBackOrders = 'Y')
SET @Comment = 'CANCEL B/O Items after shipping ' + CHAR(13)+CHAR(10) + @Comment;
/* 从客户可选表中检索 23PAY2INVC 标志 */
SELECT @PaymentThenInvoice = CS.VALUE
FROM dbo.ARCUSO CS
WHERE CS.IDCUST = @CustomerID AND OPTFIELD = '23PAY2INVC' ;
/* 如果 PaymentThenInvoice Flag = TRUE,将带有 CRLF 的文本添加到 COMMENT */
IF (@PaymentThenInvoice = 'Y')
SET @Comment = 'No Invoice until Paymt Rcvd ' + CHAR(13)+CHAR(10) + @Comment;
/* 从客户可选表中检索 24PAY2SHIP 标志 */
SELECT @HoldLabelForPayment = CS.VALUE
FROM dbo.ARCUSO CS
WHERE CS.IDCUST = @CustomerID AND OPTFIELD = '24PAY2SHIP' ;
/* 如果 HoldLabelForPayment Flag = TRUE,将带有 CRLF 的文本添加到 COMMENT */
IF (@HoldLabelForPayment = 'Y')
SET @Comment = 'Release Label after PYMT Rcvd ' + CHAR(13)+CHAR(10) + @Comment;
/* 从客户可选表中检索 25FRTPGM 标志 */
SELECT @FreightProgram = CS.VALUE
FROM dbo.ARCUSO CS
WHERE CS.IDCUST = @CustomerID AND OPTFIELD = '25FRTPGM' ;
/* 如果货运程序标志 = TRUE,则将带有 CRLF 的文本添加到 COMMENT */
IF (@FreightProgram = 'Y')
SET @Comment = 'FREIGHT PROGRAM CUSTOMER ' + CHAR(13)+CHAR(10) + @Comment;
/* 从客户可选表中检索 26CSTCOMM 标志 */
SELECT @CustComment = CS.VALUE
FROM dbo.ARCUSO CS
WHERE CS.IDCUST = @CustomerID AND OPTFIELD = '26CSTCOMM' ;
/* 如果自定义客户评论不是空白,则将带有 CRLF 的文本添加到 COMMENT */
IF (@CustComment <> '')
SET @Comment = @CustComment + CHAR(13)+CHAR(10) + @Comment;
/* 更新订单头表 */
UPDATE dbo.OEORDH
SET COMMENT = @Comment
WHERE ORDUNIQ = @OrdUniq;
END;
解决方案
推荐阅读
- html - div-box 两侧的内容和前元素
- javascript - javascript FileAttachment 未定义
- sql-server - 当时间在给定的分钟间隔内连续时对记录进行分组
- java - 连续子数组。有比 O(n^2) 更好的解决方案吗?
- javascript - Uncaught (in promise) SyntaxError: Unexpected end of JSON input on my console
- excel - 使用 VBA 基于可变工作表编号创建多个数据透视表
- reactjs - 如何将我在反应功能组件中获取和映射的数据传递给反应模式?
- flutter - ckeckbox 不会改变状态
- mysql - 在 ubuntu 中使用 mysql 时出错访问被拒绝
- php - 您可以使用 catch 语句来发出警告吗:PHP