错误：对于此服务器版本，"("在此位置无效，需要标识符

Question

错误声明：

CALCULATE_LATE_EE_AND_TAX(：NEW.ACT_RET_DT_TIME，：NEW.RET_DT_TIME，：NEW.REG_NUM，：NEW.AMOUNT，totalLateFee，totalTax(

整个代码是：

CREATE TRIGGER GENERATE_BILLING
AFTER
UPDATE
ON BOOKING_DETAILS FOR EACH ROW BEGIN IF (
IFNULL(TO_CHAR(NEW.ACT_RET_DT_TIME), 'NULL') <> 'NULL'
AND NEW.BOOKING_STATUS = 'R'
) THEN BEGIN DECLARE lastBillId VARCHAR(45);DECLARE newBillId VARCHAR(45);DECLARE discountAmt DECIMAL(19, 4);DECLARE totalLateFee VARCHAR(4000);DECLARE totalTax DECIMAL(19, 4);DECLARE totalAmountBeforeDiscount DECIMAL(19, 4);DECLARE finalAmount DECIMAL(19, 4);
SELECT
BILL_ID INTO lastBillId
FROM
(
SELECT
BILL_ID,
ROWNUM AS RN
FROM
BILLING_DETAILS
)
WHERE
RN = (
SELECT
MAX(ROWNUM)
FROM
BILLING_DETAILS
);
SET
newBillId = CONCAT(
'BL',
IFNULL(TO_CHAR(TO_NUMBER(SUBSTR(lastBillId, 3)) + 1), '')
);CALCULATE_LATE_FEE_AND_TAX(
NEW.ACT_RET_DT_TIME,
NEW.RET_DT_TIME,
NEW.REG_NUM,
NEW.AMOUNT,
totalLateFee,
totalTax
);
SET
totalAmountBeforeDiscount = NEW.AMOUNT + totalLateFee + totalTax;CALCULATE_DISCOUNT_AMOUNT(
NEW.DL_NUM,
totalAmountBeforeDiscount,
NEW.DISCOUNT_CODE,
discountAmt
);
SET
finalAmount = totalAmountBeforeDiscount - discountAmt;
INSERT INTO
BILLING_DETAILS (
BILL_ID,
BILL_DATE,
BILL_STATUS,
DISCOUNT_AMOUNT,
TOTAL_AMOUNT,
TAX_AMOUNT,
BOOKING_ID,
TOTAL_LATE_FEE
)
VALUES
(
newBillId,
str_to_date(SYSDATE(), '%Y-%m-%d'),
'P',
discountAmt,
finalAmount,
totalTax,
NEW.BOOKING_ID,
totalLateFee
);
END;

Answer 1

您有许多错误，包括函数调用以及缺少end和缺少end if；

我不知道你的扳机应该做什么，但这个犹太税。

DROP TRIGGER IF EXISTS T;
DELIMITER $$
CREATE TRIGGER T AFTER UPDATE ON BOOKING_DETAILS 
FOR EACH ROW 
BEGIN 
IF(IFNULL(TO_CHAR(NEW.ACT_RET_DT_TIME), 'NULL') <> 'NULL' AND NEW.BOOKING_STATUS = 'R') THEN 
BEGIN 
DECLARE lastBillId VARCHAR(45);
DECLARE newBillId VARCHAR(45);
DECLARE discountAmt DECIMAL(19, 4);
DECLARE totalLateFee VARCHAR(4000);
DECLARE totalTax DECIMAL(19, 4);
DECLARE totalAmountBeforeDiscount DECIMAL(19, 4);
DECLARE finalAmount DECIMAL(19, 4);
SELECT BILL_ID INTO lastBillId
FROM
(SELECT BILL_ID,
ROWNUM AS RN
FROM BILLING_DETAILS
) s
WHERE
RN = (SELECT
MAX(ROWNUM)
FROM BILLING_DETAILS
);
SET newBillId = CONCAT('BL',IFNULL(TO_CHAR(TO_NUMBER(SUBSTR(lastBillId, 3)) + 1), ''));

select CALCULATE_LATE_FEE_AND_TAX(
NEW.ACT_RET_DT_TIME,
NEW.RET_DT_TIME,
NEW.REG_NUM,
NEW.AMOUNT,
totalLateFee,
totalTax
) into @a;

SET totalAmountBeforeDiscount = NEW.AMOUNT + totalLateFee + totalTax;

select CALCULATE_DISCOUNT_AMOUNT(
NEW.DL_NUM,
totalAmountBeforeDiscount,
NEW.DISCOUNT_CODE,
discountAmt
) into @a;

sET finalAmount = totalAmountBeforeDiscount - discountAmt;

INSERT INTO
BILLING_DETAILS (
BILL_ID,
BILL_DATE,
BILL_STATUS,
DISCOUNT_AMOUNT,
TOTAL_AMOUNT,
TAX_AMOUNT,
BOOKING_ID,
TOTAL_LATE_FEE
)
VALUES
(
newBillId,
str_to_date(sysdate(), '%Y-%m-%d'),
'P',
discountAmt,
finalAmount,
totalTax,
NEW.BOOKING_ID,
totalLateFee
);

end;
end if;
END $$
DELIMITER ;

BTW to_char是oracle而不是mysql，并且是supect sysdate也是。