#text #triggers #insert #add
Вопрос:
Я пишу триггер после вставки в таблицу заголовка заказа 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 = TRUE, добавьте текст с CRLF для КОММЕНТАРИЯ */
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' ;
/* Если бесплатный Frt для Depew, Нью-Йорк = TRUE, добавьте текст с CRLF для КОММЕНТАРИЯ */
IF (@FreeDepewFreight = 'Y')
SET @Comment = 'Free freight to Depew, NY ' CHAR(13) CHAR(10) @Comment;
/* Retrieve 21CPIFREIGHT Flag from the Customer Optional Table */
SELECT @CPIFreeOver2500 = CS.VALUE
FROM dbo.ARCUSO CS
WHERE CS.IDCUST = @CustomerID AND OPTFIELD = '21CPIFREIGHT' ;
/* If CPIFreeOver2500 Flag = TRUE, add text with CRLF to COMMENT */
IF (@CPIFreeOver2500 = 'Y')
SET @Comment = 'Free freight over $2,500 ' CHAR(13) CHAR(10) @Comment;
/* Retrieve 22CANCELBO Flag from the Customer Optional Table */
SELECT @CancelBackOrders = CS.VALUE
FROM dbo.ARCUSO CS
WHERE CS.IDCUST = @CustomerID AND OPTFIELD = '22CANCELBO' ;
/* If CancelBackOrders Flag = TRUE, add text with CRLF to COMMENT */
IF (@CancelBackOrders = 'Y')
SET @Comment = 'CANCEL B/O Items after shipping ' CHAR(13) CHAR(10) @Comment;
/* Retrieve 23PAY2INVC Flag from the Customer Optional Table */
SELECT @PaymentThenInvoice = CS.VALUE
FROM dbo.ARCUSO CS
WHERE CS.IDCUST = @CustomerID AND OPTFIELD = '23PAY2INVC' ;
/* If PaymentThenInvoice Flag = TRUE, add text with CRLF to COMMENT */
IF (@PaymentThenInvoice = 'Y')
SET @Comment = 'No Invoice until Paymt Rcvd ' CHAR(13) CHAR(10) @Comment;
/* Retrieve 24PAY2SHIP Flag from the Customer Optional Table */
SELECT @HoldLabelForPayment = CS.VALUE
FROM dbo.ARCUSO CS
WHERE CS.IDCUST = @CustomerID AND OPTFIELD = '24PAY2SHIP' ;
/* If HoldLabelForPayment Flag = TRUE, add text with CRLF to COMMENT */
IF (@HoldLabelForPayment = 'Y')
SET @Comment = 'Release Label after PYMT Rcvd ' CHAR(13) CHAR(10) @Comment;
/* Retrieve 25FRTPGM Flag from the Customer Optional Table */
SELECT @FreightProgram = CS.VALUE
FROM dbo.ARCUSO CS
WHERE CS.IDCUST = @CustomerID AND OPTFIELD = '25FRTPGM' ;
/* If Freight Program Flag = TRUE, add text with CRLF to COMMENT */
IF (@FreightProgram = 'Y')
SET @Comment = 'FREIGHT PROGRAM CUSTOMER ' CHAR(13) CHAR(10) @Comment;
/* Retrieve 26CSTCOMM Flag from the Customer Optional Table */
SELECT @CustComment = CS.VALUE
FROM dbo.ARCUSO CS
WHERE CS.IDCUST = @CustomerID AND OPTFIELD = '26CSTCOMM' ;
/* If Custom Customer Comment not Blank, add text with CRLF to COMMENT */
IF (@CustComment <> '')
SET @Comment = @CustComment CHAR(13) CHAR(10) @Comment;
/* Обновить Таблицу Заголовков Заказов */
UPDATE dbo.OEORDH
SET COMMENT = @Comment
WHERE ORDUNIQ = @OrdUniq;
END;