sql - 错误消息4145，级别15，状态1，非 boolean 类型的表达式

Question

当我尝试执行此语句时，出现以下错误消息:

Msg 4145, Level 15, State 1, Procedure tr_check_qty, Line 8
An expression of non-boolean type specified in a context where a condition is expected, near 'BEGIN'

我之前已经收到此错误消息，但是这次我不知道如何解决它。我什至有一个专门为这个程序编写程序的人，他虽然精疲力竭，却找不到我的语法问题。救命!

CREATE TRIGGER tr_check_qty
ON order_details
FOR INSERT, UPDATE
AS
   IF (SELECT quantity_in_stock
       FROM products
       WHERE quantity_in_stock >= units_on_order)
   BEGIN
      PRINT 'Insert/Update Not Allowed: quantity_in_stock less than units_on_order.'
      ROLLBACK TRANSACTION
   END;
GO

好的，现在我可以执行以下语句:

CREATE TRIGGER      tr_check_qty
ON                  order_details
FOR INSERT, UPDATE
AS
IF EXISTS           (   SELECT      COUNT(inserted.order_id)
                    FROM        inserted
                    INNER JOIN  products ON products.product_id=inserted.product_id
                    WHERE       inserted.quantity>products.quantity_in_stock)
BEGIN
PRINT 'Insert/Update Not Allowed: quantity_in_stock less than units_on_order.'
ROLLBACK TRANSACTION
END;
GO

但是现在我得到了这个错误:

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'quantity' to data type int.

当我尝试在触发器之后执行以下语句时:

UPDATE order_details
SET quantity=30
WHERE order_id=10044 AND product_id=7;
GO

Answer 1

您的if语句没有比较。也许您的意思是这样的:

IF   EXISTS (   SELECT      quantity_in_stock
                FROM        products
                WHERE       quantity_in_stock >= units_on_order )

查看是否返回任何行。