Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    9

    Unanswered: Problem with trigger

    I am trying to do a multiple updates on a table with a trigger. In the trigger I am having problems specifying a single row at a time. Which is where this error comes from.

    ERROR: [Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when
    the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression.
    [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminiated.

    I understand what the error is, but I would like some help on how to rewritting the trigger so it supports multiple updates.

    Here is my code:

    CREATE TRIGGER [tri_updBilling] ON dbo.inventory
    FOR UPDATE
    AS
    BEGIN

    IF ((SELECT location_row FROM inserted) NOT IN (SELECT be.billex_row FROM dbo.billingExempt be WHERE be.billex_bin = (SELECT location_bin FROM inserted)))
    BEGIN
    IF EXISTS (SELECT billing_id FROM dbo.billing WHERE inventory_id = (SELECT inventory_id FROM inserted))
    BEGIN
    UPDATE dbo.Billing
    SET product_owner_id = (SELECT product_owner_id FROM inserted),
    pallet = (SELECT pallet FROM inserted),
    product_Id = (SELECT product_Id FROM inserted),
    quantity = (SELECT quantity FROM inserted),
    activeYN = 1, end_Date = NULL
    WHERE inventory_Id = (SELECT inventory_Id FROM inserted)
    END
    ELSE
    BEGIN
    INSERT dbo.Billing
    (inventory_id, product_owner_id, pallet, product_id, activeYN,start_Date, quantity, last_date_billed)
    SELECT inventory_Id, product_owner_id, pallet, product_id,1,GetDate(),quantity, GetDate()
    FROM inserted
    END
    END
    ELSE
    BEGIN
    IF EXISTS (SELECT billing_id FROM dbo.billing WHERE inventory_id = (SELECT inventory_id FROM inserted))
    BEGIN
    UPDATE dbo.Billing
    SET product_owner_id = (SELECT product_owner_id FROM inserted),
    pallet = (SELECT pallet FROM inserted),
    product_Id = (SELECT product_Id FROM inserted),
    quantity = (SELECT quantity FROM inserted),
    end_Date = GETDATE(), activeYN = 0
    WHERE inventory_id = (SELECT inventory_id FROM inserted)
    END
    END
    END

    Thanks for the help in advance.
    Last edited by shea@ccr.net; 11-21-03 at 14:39.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just a partial shot....

    You need to join to inserted...

    Code:
    CREATE TRIGGER [tri_updBilling] ON dbo.inventory 
    FOR UPDATE
    AS
    BEGIN
    
    	IF (SELECT * 
    	      FROM inserted 
    	     WHERE location_row NOT IN (SELECT be.billex_row 
    					  FROM dbo.billingExempt be 
    					 WHERE be.billex_bin IN 
    						(SELECT location_bin FROM inserted))
    	BEGIN
    		IF EXISTS (SELECT billing_id FROM dbo.billing 
    			    WHERE inventory_id IN (SELECT inventory_id FROM inserted))
    		BEGIN
    			UPDATE dbo.Billing
    			   SET  p roduct_owner_id = i.product_owner_id
    				, pallet 	  = i.pallet
    				, product_Id 	  = i.product_Id
    				, quantity 	  = i.quantity
    				, activeYN 	  = 1
    				, end_Date = NULL
    			  FROM Billinh b, inserted i 
    			 WHERE inventory_Id = i.inserted
    		END
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2003
    Posts
    48
    SQL is a set operation language,
    unless you use cursor, you can't iterate row by row.
    Following is a solution based on set operation.

    The structure of your trigger is

    if (condition1)
    if (condition 2)
    (update 1)
    else
    (insert 1)
    end if
    else
    if (condition 2)
    (update 3)
    end if
    end if

    As a guideline, you can rewrite your trigger as below

    (update 1) when (condition1) and (condition2)
    (insert 1) when (condition1) and (not condition2)
    (update 3) when (not condition1) and (condition2)

    1. if you can rewrite the condition1 to "EXISTS (....)", it's easy to combine the conditions into where clause
    2. combine Brett's response, "you need to join to inserted..." to resolve the insert error
    3. you can resolve error in update statement by using "Update ... From ... Where ... " and join "inserted" to update
    Last edited by shianmiin; 11-21-03 at 16:18.
    Shianmiin

  4. #4
    Join Date
    Nov 2003
    Posts
    9

    Thanks!!

    Thanks for the help guys!

    As I was waiting for a reponse I figured it out, but that is exactly how I did it. Thanks again for the help!

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Thanks!!

    Originally posted by shea@ccr.net
    Thanks for the help guys!

    As I was waiting for a reponse I figured it out, but that is exactly how I did it. Thanks again for the help!
    Hey,

    post the changes...like to have a look...

    And Congrats...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2003
    Posts
    9

    Here is the final result!!

    Here are my changes:

    CREATE TRIGGER [tri_updBilling] ON dbo.inventory
    AFTER UPDATE
    AS
    BEGIN
    IF EXISTS (SELECT billing_id FROM dbo.billing WHERE inventory_id IN (SELECT inventory_id FROM inserted))
    BEGIN
    UPDATE dbo.Billing
    SET product_owner_id = (SELECT inserted.product_owner_id
    FROM inserted
    WHERE inserted.inventory_id = dbo.Billing.inventory_id),
    pallet = (SELECT inserted.pallet
    FROM inserted
    WHERE inserted.inventory_id = dbo.Billing.inventory_id),
    product_Id = (SELECT inserted.product_Id
    FROM inserted
    WHERE inserted.inventory_id = dbo.Billing.inventory_id),
    quantity = (SELECT inserted.quantity
    FROM inserted
    WHERE inserted.inventory_id = dbo.Billing.inventory_id),
    activeYN = 1,
    end_Date = NULL,
    loc_exempt = 0
    WHERE dbo.Billing.inventory_Id IN (SELECT ins.inventory_Id
    FROM inserted ins
    WHERE ins.location_row NOT IN (SELECT be.billex_row
    FROM dbo.billingExempt be
    WHERE be.billex_bin = ins.location_bin))
    END
    ELSE
    BEGIN
    INSERT dbo.Billing
    (inventory_id, product_owner_id, pallet, product_id, activeYN,start_Date, quantity, last_date_billed)
    SELECT inventory_Id, product_owner_id, pallet, product_id,1,GetDate(),quantity, GetDate()
    FROM inserted
    WHERE inserted.location_row NOT IN (SELECT be.billex_row
    FROM dbo.billingExempt be
    WHERE be.billex_bin = inserted.location_bin) AND
    inserted.inventory_Id NOT IN (SELECT b.billing_id
    FROM dbo.billing b
    WHERE b.inventory_id = inserted.inventory_id)
    END
    UPDATE dbo.Billing
    SET product_owner_id = (SELECT inserted.product_owner_id
    FROM inserted
    WHERE inserted.inventory_id = dbo.Billing.inventory_id),
    pallet = (SELECT inserted.pallet
    FROM inserted
    WHERE inserted.inventory_id = dbo.Billing.inventory_id),
    product_Id = (SELECT inserted.product_Id
    FROM inserted
    WHERE inserted.inventory_id = dbo.Billing.inventory_id),
    quantity = (SELECT inserted.quantity
    FROM inserted
    WHERE inserted.inventory_id = dbo.Billing.inventory_id),
    activeYN = 0,
    end_Date = GETDATE(),
    loc_exempt = 1
    WHERE dbo.Billing.inventory_Id IN (SELECT ins.inventory_Id
    FROM inserted ins
    WHERE ins.location_row IN (SELECT be.billex_row
    FROM dbo.billingExempt be
    WHERE be.billex_bin = ins.location_bin))


    END

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •