February 28, 2007 at 12:32 pm
I have the following trigger in my db:
CREATE
TRIGGER [trgValidateOrderTypeSubType]
ON [dbo].[ORDER_MASTER]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF ((select ORDER_TYPE_ID from inserted) IS NOT NULL AND (select ORDER_SUB_TYPE_ID from inserted) IS NOT NULL)
BEGIN
IF ( (SELECT COUNT(lstl.ID) FROM ORDER_SUB_TYPE_LOOKUP lstl, inserted WHERE lstl.ORDER_TYPE_ID = INSERTED.ORDER_TYPE_ID AND lstl.[ID] = inserted.ORDER_SUB_TYPE_ID) < 1)
BEGIN
ROLLBACK TRANSACTION
RAISERROR (N'Order Type and Order Sub Type do not match.',11,1)
END
END
END
When I try to run an Update statement against the ORDER_MASTER table which updates multiple rows, I get the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I imagine that what's causing this error is the statement:
IF ((select ORDER_TYPE_ID from inserted) IS NOT NULL AND (select ORDER_SUB_TYPE_ID from inserted) IS NOT NULL)
in the trigger. However, I don't know what to do about it. I only want the validation to occur if both fields are not null. I thought that the inserted table only contains one row at a time. Apparently, I am wrong. Any advice?
Thanks,
T
February 28, 2007 at 12:44 pm
Try not using the SELECT in the expresion, but rather set the values to a variable and then evaluate the variable.
February 28, 2007 at 1:08 pm
How would that help? Then I'd be assigning multiple values to a single variable.
February 28, 2007 at 1:56 pm
You are correct as to the code that is causing the problem. If you are inserting/updating multiple rows, the trigger will fire once after the update, not once per row. The inserted table will contain all of the inserted/updated rows. If you need to evaluate one row at a time, I would suggest using an aggregate function instead of a cursor. It looks like you want to rollback the transaction if any of the order types or sub types are NULL. Is this correct? What are the datatypes for ORDER_TYPE_ID and ORDER_SUB_TYPE_ID?
February 28, 2007 at 2:44 pm
Why not simply create a foreign key constraint and let the database engine handle it ?
If you absolutely must code it in a trigger, make it set-based to handle multiple rows:
CREATE TRIGGER [trgValidateOrderTypeSubType]
ON [dbo].[ORDER_MASTER]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON
If Exists (
Select *
From inserted As i
Where Not Exists (
Select *
From ORDER_SUB_TYPE_LOOKUP lstl
Where lstl.ORDER_TYPE_ID = i.ORDER_TYPE_ID
And lstl.[ID] = i.ORDER_SUB_TYPE_ID
)
)
Begin
ROLLBACK TRANSACTION
RAISERROR (N'Order Type and Order Sub Type do not match.',11,1)
End
END
March 1, 2007 at 7:37 am
Thank you - I think that should work. Here's my table structure, which hopefully explains why a simple foreign key isn't enough.
Order_Master - contains all order fields including order_type_id (int) and order_sub_type_id (int)
Order_type_lookup - ID (int), Descr (varchar) - a lookup table for order_type_id. there's a foreign key set up on Order_master which references this table
Order_Sub_Type_Lookup - ID (int), Descr (varchar), Order_type_id (int - foreign key from Order_type_lookup) - this table is a lookup for order_sub_types and a mapping table that maps order types to sub types. There's a foreign key set up on Order_master which references this table.
I need validation that checks that the order_type and sub_type (if they are not null) are, in fact, related in the Order_sub_type_lookup table. Since there can be order types without sub types (but not vice versa), I can't make the foreign key for order_Type_id look at the Order_sub_type_lookup table. Therefore, I need a trigger to validate that the 2 fields are related.
Thanks for your help!
March 1, 2007 at 8:09 am
PW provided a good trigger solution.
However, I want to point out that in your original trigger code, the first IF statement is not necessary. In the later query, you are matching both order_type_id and order_sub_type_id from the inserted table. If either of these values is null, that row from inserted won't match anything at all, which appears to be what you were trying to accomplish with the first IF statement.
March 1, 2007 at 8:18 am
Thanks, Stephanie. I realized that when I tried implementing the trigger. I think the sql I'm going to go with is:
RAISERROR (N'OrderType and Order Sub Type are not related.',11,1)
END
March 1, 2007 at 5:40 pm
Would not a foreign key contraint be easier ?
Assuming that ORDER_TYPE_ID and ORDER_SUB_TYPE_ID are the primary key of table ORDER_SUB_TYPE_LOOKUP:
Alter table ORDER_MASTER
add constraint ORDER_SUB_TYPE_LOOKUP_FK_ORDER_MASTER
foreign key (ORDER_TYPE_ID ,ORDER_SUB_TYPE_ID )
references ORDER_SUB_TYPE_LOOKUP
go
SQL = Scarcely Qualifies as a Language
March 2, 2007 at 7:32 am
No can do. Not all order types have a sub type so not all order types will be in the sub_type table. Therefore, I need the order_type foreign key to be on the ORDER_TYPE_LOOKUP and the order_sub_type foreign key to be on the ORDER_SUB_TYPE_LOOKUP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply