[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3495
  • Last Modified:

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
0
show_t
Asked:
show_t
1 Solution
 
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now