if exists (SELECT...)
begin
     raiserror...
end

quote:

---

Originally posted by Sql_question

Hello, im new to sql and im struggeling with my trigger query.
There are 3 tables: -orders,
-parts and
-suppliers

I have to write an AFTER INSERT Trigger on the orders table to verify that the supplier and the part specified in an insert statement are in the same city. Then i have to reject all orders which do not have a supplier and part in the same city and print a message to the screen with the reason for rejecting the order. For valid orders, i have to print a message to the screen indicating that the order was valid.

I made this so far, but when i try to insert data in the orders table they all get rejected by the trigger. I've been working on this for quite a while now and im stuck. Hopefully i can get some answers here so i can continue with it.


CREATE TRIGGER tr_orders ON orders
AFTER INSERT
AS
SELECT suppliers.sname AS supplier, parts.pname AS Part, orders.qty AS quantity
FROM orders
INNER JOIN parts ON parts.pno = orders.pno
INNER JOIN suppliers ON suppliers.sno = orders.sno
WHERE suppliers.city != parts.city
BEGIN
RAISERROR ('Order Rejected, supplier and part in the same city ', 16, 1)
ROLLBACK TRAN
RETURN
END
GO

Let me modify your original query to the way TG suggested.

CREATE TRIGGER tr_orders ON orders  
AFTER INSERT  
AS
IF Exists(
SELECT 1 FROM Inserted I
INNER JOIN parts ON parts.pno = I.pno
INNER JOIN suppliers ON suppliers.sno = I.sno
WHERE suppliers.city <> parts.city)

    BEGIN
        RAISERROR ('Order Rejected, supplier and part in the same city ', 16, 1)
        ROLLBACK TRAN 
        RETURN
    END
GO

---

quote:

---

Originally posted by Sql_question

Thank you!

It's working and i think i understand what you wrote :)

---

 RAISERROR ('Order Rejected, supplier and part should be in the same city ', 16, 1)