Error Msg 4145, Level 15, State 1, An expression of non-boolean type

0
votes

When I try and execute this statement I get this error message:

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'

I've received this error message before but I cannot figure out how to fix it this time. I even had someone who programs for a living look at this, and while he was exhausted and busy, he couldn't find an issue with my syntax. Help!

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

Okay, so I can execute this statement now:

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

But now I'm getting this error:

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

When I try to excecute this statement following the trigger:

UPDATE order_details
SET quantity=30
WHERE order_id=10044 AND product_id=7;
GO
2
sqlsql-serversyntaxerror-handlingboolean

2 Answers

1
votes

Your if statement has no comparison. Perhaps you mean something like this:

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

To see if any rows are returned.

1
votes

I think you need to reference your inserted rows, too, like here

CREATE TRIGGER tr_check_qty
3>    ON order_details
4>    FOR INSERT, UPDATE
5> AS
6>    IF EXISTS
7>       (
8>        SELECT 'True'
9>        FROM Inserted i
10>        JOIN products p
11>          ON i.ID = p.ID
          WHERE       quantity_in_stock >= units_on_order
12>       )
13>    BEGIN
14>        PRINT 'Insert/Update Not Allowed: quantity_in_stock less than units_on_order.'
15>        ROLLBACK TRAN
16> END