TRIGGER -2147467259 Key column information is insufficient or incorrect

Hello,
I'm using a Trigger :

ALTER TRIGGER trans_trig
ON dbo.Transactions
for UPDATE, INSERT
AS
BEGIN
      UPDATE Transactions
      SET balance = (select sum(deposit) - sum(payment) FROM Transactions t2 WHERE t2.TID <= Transactions.TID and t2.AccID = Transactions.AccID)
      
      FROM inserted i
      WHERE Transactions.AccID = i.AccID
      and transactions.TID >= i.TID
END

The trigger works just fine except that when i insert a new record i get this error message :

"-2147467259 Key column information is insufficient or incorrect.  Too many rows were affected by update"

does anyone have any ideas why and how i can get rid of it ?

Thanks
show_tAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

show_tAuthor Commented:
table structure is like so :

TID AccID Date           Payment   Deposit Type Balance Void LastUpdated
3    238    01/11/2004 0             10         D      0          0      09/08/2004 09:33:42

TID = PK

..
0
RichardCorrieCommented:
your trigger is in a sel loop...

You insert a record and then try to update it which inturn calls the trigger that calls the trigger ......

try changing your trigger to
...

ALTER TRIGGER trans_trig
ON dbo.Transactions
for UPDATE, INSERT
AS
-- Only run code if balance column is not being updated...
if not Update(balance)
BEGIN

     UPDATE Transactions
     SET balance = (select sum(deposit) - sum(payment) FROM Transactions t2 WHERE t2.TID <= Transactions.TID and t2.AccID = Transactions.AccID)
     
     FROM inserted i
     WHERE Transactions.AccID = i.AccID
     and transactions.TID >= i.TID
END


/Richard
0
HilaireCommented:
try

ALTER TRIGGER trans_trig
ON dbo.Transactions
for UPDATE, INSERT
AS
BEGIN
     UPDATE Transactions
     SET balance = (select sum(deposit) - sum(payment) FROM Transactions t2 WHERE t2.TID <= Transactions.TID and t2.AccID = Transactions.AccID)
     
     FROM inserted i
     WHERE Transactions.AccID = i.AccID
     and transactions.TID = i.TID  --> changed ">=" to "=" here
END
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

show_tAuthor Commented:
Thanks but i'm still getting the same error message..
0
show_tAuthor Commented:
Thanks Hilaire,

solution works !  I can't understand why your code edit worked though ?
Could you please explain ?

Many Thanks !
:-)
0
HilaireCommented:
Your code was trying to update several times the same record.
Let's say you insert two transactions (01 AND 02) for the same account(01). The inner join using ">=" would try to update the same record (Acct 01 transaction 02) twice in the same update.


I tend to think your code would be more robust as follows

ALTER TRIGGER trans_trig
ON dbo.Transactions
for UPDATE, INSERT
AS
BEGIN
     UPDATE Transactions
     SET balance = (select sum(deposit) - sum(payment) FROM Transactions t2 WHERE t2.TID <= Transactions.TID and t2.AccID = Transactions.AccID)
     WHERE AccID in (Select distinct AccID from inserted)
END
0
HilaireCommented:
Default SQL Server settings prevents SQL to fire triggers recursively.
setting RECURSIVE_TRIGGERS
database property to ON would require special precautions as suggested by Richard.

0
show_tAuthor Commented:
sorry one more thing, i forgot !!

i'm using your solution :

ALTER TRIGGER trans_trig
ON dbo.Transactions
for UPDATE, INSERT
AS
BEGIN
     UPDATE Transactions
     SET balance = (select sum(deposit) - sum(payment) FROM Transactions t2 WHERE t2.TID <= Transactions.TID and t2.AccID = Transactions.AccID)
     
     FROM inserted i
     WHERE Transactions.AccID = i.AccID
     and transactions.TID = i.TID  --> changed ">=" to "=" here
END


i'd like to add one more constraint to the Trigger.  I only want the trigger to edit Transactions that have the where clause
'void=0'  only.

One again,

Thank-you for your help
0
HilaireCommented:
ALTER TRIGGER trans_trig
ON dbo.Transactions
for UPDATE, INSERT
AS
BEGIN
     UPDATE Transactions
     SET balance = (select sum(deposit) - sum(payment) FROM Transactions t2 WHERE t2.TID <= Transactions.TID and t2.AccID = Transactions.AccID)
     
     FROM inserted i
     WHERE Transactions.AccID = i.AccID
     and transactions.TID = i.TID  --> changed ">=" to "=" here
     and transactions.void = 0
END

0
HilaireCommented:
or

ALTER TRIGGER trans_trig
ON dbo.Transactions
for UPDATE, INSERT
AS
BEGIN
     UPDATE Transactions
     SET balance = (select sum(deposit) - sum(payment) FROM Transactions t2 WHERE t2.TID <= Transactions.TID and t2.AccID = Transactions.AccID)
     WHERE AccID in (Select distinct AccID from inserted)
     AND void = 0
END
0
LowfatspreadCommented:
Update Transactions
 Set Balance = Balance + deposit - payment
From
(
Select Accid,tid,sum(deposit) as deposit,sum(payment) as payment
  From (
select i.accid,i.tid,i2.deposit,i2.payment
  from inserted as i
  inner join inserted as i2
    on i.accid=i2.accid
   and i.tid >= i2.tid
Union all
select d.accid,d.tid,(d2.deposit) * -1,(d2.payment) * -1
  from deleted as d
  inner join deleted as d2
    on d.accid=d2.accid
   and d.tid >= d2.tid
 ) as X
group by Accid,Tid
Having Sum(deposit) - Sum(payment) <> 0
) as Y
Inner Join Transactions as T
on t.accid = y.accid
and t.tid = Y.Tid
0
show_tAuthor Commented:
Thats what i thought but it doesn't seem to be working,
The trigger Balance result is still reflecting a  result that means it is still including a transaction that has Void=1
in the calculations...
0
HilaireCommented:

ALTER TRIGGER trans_trig
ON dbo.Transactions
for UPDATE, INSERT
AS
BEGIN
     UPDATE Transactions
     SET balance = (select sum(deposit) - sum(payment) FROM Transactions t2 WHERE t2.TID <= Transactions.TID and t2.AccID = Transactions.AccID and t2.void = 0)
     
     FROM inserted i
     WHERE Transactions.AccID = i.AccID
     and transactions.TID = i.TID  --> changed ">=" to "=" here
     and transactions.void = 0
END

or

ALTER TRIGGER trans_trig
ON dbo.Transactions
for UPDATE, INSERT
AS
BEGIN
     UPDATE Transactions
     SET balance = (select sum(deposit) - sum(payment) FROM Transactions t2 WHERE t2.TID <= Transactions.TID and t2.AccID = Transactions.AccID and t2.void = 0)
     WHERE AccID in (Select distinct AccID from inserted)
     AND void = 0
END


0
show_tAuthor Commented:
Excellent Hilaire,

much appreciated !
thanks also to richard and lowfatspread
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.