Unexpected Trigger Behaviour

[jgoodman00]

I have the following trigger:

ALTER TRIGGER INS_UPD_TRG1
ON dbo.tblInspection
FOR UPDATE, INSERT
NOT FOR REPLICATION
AS

IF UPDATE (priority)
INSERT INTO tblEDiscoveries (RefNo, InspectionDate)
SELECT RefNo, InspectionDate
FROM inserted
WHERE priority = 'e'


Basically, if the user changes the priority field to 'e', the trigger fires & inserts a new record into the table [tblEDiscoveries]. For all other changes the trigger does not fire.

This works exactly as expected, only inserting a record when the user changes the priority column for a record to 'e'.

If I run a script such as the following in query analyzer it behaves as anticipated:
update tblInspection
set priority = 'e'
where refno = 'f8100'

However, if I attempt this change from within Access (the application front-end we use), I get the annoying 'Another user has edited this record' error.

Seeing as the users cannot directly open [tblEDiscoveries] (only through an sp), & the trigger only inserts into another table, I dont understand why this message is being executed.

Any suggestions?




James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[jgoodman00]

Have solved this by modifying the trigger slightly:

ALTER TRIGGER INS_UPD_TRG1
ON dbo.tblInspection
FOR UPDATE, INSERT
NOT FOR REPLICATION
AS
DECLARE @OldVal char(1), @NewVal char(1)

IF UPDATE (priority) -- We only want to capture updates to the priority column
BEGIN
SELECT @OldVal = (SELECT priority FROM deleted)
SELECT @NewVal = (SELECT priority FROM inserted)
If @NewVal <> 'e' --Priority not e
RETURN
IF @OldVal = @NewVal --Priority has not actually been changed
RETURN
ELSE --Looking good, insert the record
INSERT INTO tblEDiscoveries (RefNo, InspectionDate)
SELECT RefNo, InspectionDate
FROM inserted
PRINT 'Record inserted'
END



I think it might have been some kind of misunderstanding with between sql server & access, whereby if the priority column was modified to a value other than 'e' the trigger was firing but not executing the insert, rather than simply exiting the trigger.

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures