July 6, 2006 at 3:10 am
We are having problems with updates to a table occasionally not being stored.
I have put the following trigger on the table in question (Proposal table) to try to see what is happening:
/*****************************************************************/
CREATE TRIGGER ProposalCC on Proposal for update as
declare
@AgreementNumber int,
@QuoteNumber int,
@Count int
select @Count=Count(*) from Inserted
insert CC values (GetDate(), 0, 'Trigger', 'Count=' + cast(@Count as varchar(20)))
select @AgreementNumber=IsNull(AgreementNumber,0) from Inserted
select @QuoteNumber=IsNull(QuoteNumber,0) from Inserted
insert CC values (GetDate(), 0, 'Trigger', 'AgreementNumber ' + cast(@AgreementNumber as char(20)) + 'QuoteNumber ' + cast(@QuoteNumber as char(20)))
/*****************************************************************/
There are several other update triggers on this table but this one is set to fire last.
In the cases where the updates happen correctly everything appears in the CC table as I would expect.
When the updates are not stored, in the CC table I can see that @Count has a value of zero. How can this possibly be ? The trigger only fires on an update so there must be at least one row in the inserted table.
Also, when this happens the last field in the second row written to the CC table is blank. I would expect to see 'AgreementNumber 0 QuoteNumber 0'. I used IsNull to make sure @AgreementNumber and @QuoteNumber have valid integer values.
This is a very serious problem for us since updates are being lost and we have no way of tracking them.
July 6, 2006 at 3:45 am
Is it possible that one of the other triggers on the table is removing the updated data?
John
July 6, 2006 at 6:10 am
We found out what is happaning here If you issue an update statement which matches no rows then the triggers will still fire but the inserted table will be empty
eg:
update Proposal set QuoteNumber=1 where AgreementNumber=12345
If AgreementNumber 12345 exists then the inserted table will contain one row as expected even if the existing value of QuoteNumber=1.
If AgreementNumber 12345 does not exist then the triggers still fire and the inserted table will be empty.
So, for us, its back to Sql Profiler !
July 6, 2006 at 7:43 am
You are only fetching first updated record. What if a user updated two or more records at the same time?
Try this code for trigger
CREATE TRIGGER ProposalCC on Proposal for update as
declare
@Count int
select @Count = Count(*) from Inserted
insert CC values (GetDate(), 0, 'Trigger', 'Count=' + cast(@Count as varchar(20)))
insert CC
select GetDate(),
0,
'Trigger',
'AgreementNumber ' + str(isnull(AgreementNumber, 0), 20) + 'QuoteNumber ' + str(isnull(QuoteNumber, 0), 20))
from inserted
N 56°04'39.16"
E 12°55'05.25"
July 7, 2006 at 2:30 am
Where this is happening the count of rows in the inserted table is zero so I don't think the user is updating multiple rows.
Using Sql Profiler I have managed to catch the sql submitted to SqlServer by the client and I suspect that the data in the where clause does not match the data in SqlSqrver.
The client submits is update like this (rec is the primary key):
update proposal set
field1=10,
field2=20
field3=30
where
rec=123456 and
field1 is null and
field2 is null and
field3 = 0
I suspect the values in the where clause do not match the original values retreived in the databse. How I am going to find out if this is the case I don't know yet !
July 7, 2006 at 3:39 am
CREATE TRIGGER ProposalCC ON Proposal FOR UPDATE AS
DECLARE @Count INT
SELECT @Count = COUNT(*)
FROM INSERTED
IF @Count > 0
BEGIN
INSERT CC
VALUES (
GETDDATE(),
0,
'Trigger',
'Count=' + CAST(@Count AS VARCHAR(11))
)
INSERT CC
SELECT GETDATE(),
0,
'Trigger',
'AgreementNumber ' + STR(ISNULL(AgreementNumber, 0), 20) + 'QuoteNumber ' + STR(ISNULL(QuoteNumber, 0), 20))
FROM INSERTED
END
ELSE
INSERT CC
VALUES (
GETDDATE(),
0,
'Trigger',
'Count=0 <UPDATE OF NO USE>'
)
N 56°04'39.16"
E 12°55'05.25"
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply