Problem with trigger not updating properly

[sewilj]

The situation I have is that our end user system does not always correctly update the sequence number within a names table. We often experience the following;

A user will add name details against the main case details screen within our system. This means that the name details are added to a table CPNAME which is linked to the case table CPCASE.
The name details are added and a sequence number for the names is increased by one, therefore

Name Sequence Name Type
Name A 1 K
Name B 2 K
Name C 3 K
Name D 3 K

The latest sequence number is updated into the case table (CPCASE) into fields Keeper, Payee or Driver dependent upon the Name Type entered - K = keeper, P = Payee and D = Driver.

As you can see the problem we experience it that occasionally the sequence number is not updated correctly. As our software supplyer is having difficulties resolving this issue I have tried to create a trigger that would correct the sequence number if incorect.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TRG_CPNAME_SEQUENCE]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[TRG_CPNAME_SEQUENCE]
GO
CREATE TRIGGER TRG_CPNAME_SEQUENCE
ON CPNAME
FOR INSERT
AS
DECLARE @SEQ float,
@DKIND char (1),
@DATE datetime,
@SERNO char(10),
@UserInits char (5),
@COUNT float
SET NOCOUNT ON

SELECT @SEQ = i.SEQ,
@DATE = i.DATE,
@DKIND = i.DKIND,
@SERNO = i.SERNO,
@UserInits = i.UserInits,
@Count = (select count(n.serno) from cpname n where n.serno = i.SERNO and n.dkind = i.DKIND)

FROM cpname n right outer join inserted i on i.serno = n.serno
join cpcase c on c.serno = n.serno

IF @SEQ <> @Count
BEGIN
UPDATE CPNAME SET SEQ = @Count
WHERE serno = @SERNO and
seq = @SEQ and
date = @DATE and
UserInits = @UserInits
END
Begin
if @Dkind = 'K'
update cpcase set keeper = @count + 1 where serno = @serno
else
if @Dkind = 'P'
update cpcase set Payee = @count + 1 where serno = @serno
else
if @Dkind = 'D'
update cpcase set Driver = @count + 1 where serno = @serno
END
GO

This trigger appears to work apart from when you insert identicle data to an existing row. At this point the trigger will update all matching rows within the database and therefore correct a perfectly fine row of data.

I would like to amend the trigger so that it will only update the record that has just been added and not any records that already exist (which are identicle).

I may be missing something completely obvious but as this is one of my first attempts of creating a script I am open to any advice.

Thanks!!!


Lewis
United Kingdom

 

[nigelrivett]

DOn't you have a PK on the table?
If so then you can use this to update the row added and it will give an error if the row is identical to another.

>> As our software supplyer is having difficulties resolving this issue

Consider getting a new software supplier - this should be trivial - and fairly fundamental.
If it's a muli-user issue then looks like they are getting the max id before the insert without locking the table.
You say &quot;often&quot; so sounds like it is a more basic mistake.

Wonder about there error handling and transaction control.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.