|
I have Two Tables: Quantity Table & Purchase Table. When I insert data into Purchases Table the trigger should fire and update the ActualQuantity in the Quantity Table if the condition is satisfied. This means this value has been inserted in the Quantity table before and now we will only update the ActualQuantity field only, else if this condition are not satisfied means there are different values, at that time u can't Update the ActualQuantity Field because it hasn't been inserted before, so you have to Insert this values in Quantity Table. The problem is when I Insert 1 record it will accept, but If I insert more than one records in one Insert statement it will give me this error.
Please check the trigger and inform me where is the error ?
I Hope it's clear for you
(comments are locked)
|
|
Also if you rewrote the last bit without the local variables, it might be more clearer that you could be inserting dupe rows (I don't know whether you meant to do that or not....)
The joins could be duping up your base data
(comments are locked)
|
|
I like both Rob's and Kev's answer and have voted them up, but one thing they haven't actually said is that the inserted and deleted tables can contain multiple rows. You don't have your trigger fire once for every row that is inserted/updated/deleted - triggers fire once per operation and will represent all the rows in that operation. So you need to be using something like Kev's query, although it's likely that you might want to be doing some GROUP BY and aggregation operations in order to insert the minimum number of rows that you want. Another thing I'd like to point out which is present in your original code (and, by extension in Kev's code), is that this code
... is a time-bomb waiting to go off. If somebody adds a column to the table Cards.Quantity, or the columns get re-ordered in the future, or if somebody removes a column, then your INSERT operation will then fail. Now, while you know the columns it is obvious to you what is happening, but - in the future - you will have to try and remember what columns you were trying to INSERT into in order to fix it. Yes, I figured that the trigger behaviour was a given. Probably the key missing info for the asker though. +1
Oct 27 '09 at 08:27 AM
Rob Farley
There's a couple of seeder questions right there :)
Oct 27 '09 at 08:47 AM
Matt Whitfield ♦♦
Thanks all for your Solution , It has been solved , But still there's small error , incase im Inserting 3 records , one of them is inserted earlier , at that time it will Insert the other two including the one which inserted earlier even after updated. what shall I Do ?
Oct 27 '09 at 09:21 AM
Mohamed
Sorry I'm not clear on what you mean - can you maybe extend your question above to describe it in a bit more detail?
Oct 27 '09 at 09:30 AM
Matt Whitfield ♦♦
(comments are locked)
|
|
The problem is with the assignment queries down the end. What do you want to be put into Cards.Quantity when there are multiple rows inserted? Try turning that INSERT statement into one that uses SELECT instead of VALUES.
(comments are locked)
|
|
Dear Matt Whitfield , first Thanks for your help , I mean that if I Insert more than record in one insert statement into purchases table, the trigger will fire , then it will check the records, if it's there or not , then it will update the available records and it will go to the next stage that's Insert Statement (as u seen the trigger ) , the problem is after inserting the records which not available in the quantity table it will insert also the recently updated record which it's already inserted earlier and got update Hope it's clear Ok, in that case you want something like INSERT INTO destination (column1, column2) SELECT column1, column2 FROM inserted LEFT OUTER JOIN destination ON destination.key = inserted.key WHERE destination.key IS NULL
Oct 27 '09 at 11:06 AM
Matt Whitfield ♦♦
Dear Matt Whitfield I'm not able to get your answer , still I'm facing the problem , what do u mean by destination.Key , you mean the PrimaryKey More Explain Plz
Oct 27 '09 at 11:51 AM
Mohamed
(comments are locked)
|

