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
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
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.
answered Oct 27 '09 at 08:20 AM
Matt Whitfield ♦♦
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.
answered Oct 27 '09 at 07:23 AM
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
answered Oct 27 '09 at 10:56 AM