SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 CREATE AFTER INSERT TRIGGER
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sql_question
Starting Member

2 Posts

Posted - 02/22/2010 :  18:53:29  Show Profile  Reply with Quote
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

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/22/2010 :  22:30:56  Show Profile  Reply with Quote
Looks like you need to enclose your SELECT statement in a IF EXISTS() expression:

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


EDIT:
Also, you should INNER JOIN [inserted] so that you're only dealing with the row(s) inserted by the user for this transaction.

Be One with the Optimizer
TG

Edited by - TG on 02/22/2010 22:33:35
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 02/22/2010 :  22:42:00  Show Profile  Reply with Quote
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





PBUH
Go to Top of Page

Sql_question
Starting Member

2 Posts

Posted - 02/22/2010 :  23:25:38  Show Profile  Reply with Quote
Thank you!

It's working and i think i understand what you wrote :)
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 02/23/2010 :  00:47:48  Show Profile  Reply with Quote
quote:
Originally posted by Sql_question

Thank you!

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



You are welcome

PBUH
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/23/2010 :  09:26:22  Show Profile  Reply with Quote
i think message should read

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


for better clarity

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000