Insert trigger with refresh issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Insert trigger with refresh issue

Author

Topic

J
Starting Member

5 Posts

Posted - 2004-09-02 : 18:47:50

Here is my code for an Insert trigger - works like a dream - but - when an item is posted to the tbl the trigger is on and the item has not been posted to the history table the items entered into the inserted row change

ie. Enter
Identity ID 17
DiagID 19
Diag Code 11

tbl appears to "refresh" and suddenly the entered date looks like

Identity ID 91
DiagID 18
Diag Code 72

Ironically this record already exists - is not harmed in anyway. My data is updated in the trigger table and my history table is updated correctly. Run a new query and everything looks great.

Please help - no clue why this is happening.

DECLARE @VEID int, @DiagCode int, @DogID int, @Breed varchar(3), @Posted int, @DiagID int

Set @VEID= (SELECT vdd_VetEntryID FROM inserted)
Set @DiagCode = (SELECT vdd_DiagCode FROM inserted)
Set @DogID = (Select vdl_DogID FROM tblVetEntry Where vdl_VetEntryID = @VEID)
Set @Breed = (Select dog_Breed FROM tblDog Where dog_DogID = @DogID)
Set @DiagID = (Select vdd_DiagID FROM inserted)
Set @Posted = 0

print @VEID
print @DiagCode
print @DogID
print @Breed
print @DiagID

BEGIN
IF exists (Select * From thsVetDiagHist WHERE dh_DiagCode = @DiagCode and dh_DogID = @DogID)
SET @Posted = 1
ELSE
IF @DiagCode = 1030
IF exists (Select * From thsVetDiagHist WHERE dh_DiagCode = 6639 and dh_DogID = @DogID)
BEGIN
SET @Posted = 1
END
ELSE
IF @DiagCode = 5225
IF exists (Select * From thsVetDiagHist WHERE dh_DiagCode = 6639 and dh_DogID = @DogID)
BEGIN
SET @Posted = 1
END
ELSE
IF @DiagCode = 3940
IF exists (Select * From thsVetDiagHist WHERE dh_DiagCode = 6639 and dh_DogID = @DogID)
BEGIN
SET @Posted = 1
END
ELSE
IF @DiagCode = 8979
IF exists (Select * From thsVetDiagHist WHERE dh_DiagCode = 8301 and dh_DogID = @DogID)
BEGIN
SET @Posted = 1
END
ELSE
IF @DiagCode = 10030
IF exists (Select * From thsVetDiagHist WHERE dh_DiagCode = 8301 and dh_DogID = @DogID)
SET @Posted = 1
ELSE
IF @DiagCode = 986
IF exists (Select * From thsVetDiagHist WHERE dh_DiagCode = 26 and dh_DogID = @DogID)
BEGIN
SET @Posted = 1
END
ELSE
IF @DiagCode = 413
IF exists (Select * From thsVetDiagHist WHERE dh_DiagCode = 26 and dh_DogID = @DogID)
BEGIN
SET @Posted = 1
END
END

HERE
If @Posted = 0

BEGIN
If @DiagCode = 1030
INSERT INTO thsVetDiagHist (dh_DiagCode, dh_DogID, dh_DiagID) VALUES (6639, @DogID, @DiagID)
ELSE
IF @DiagCode = 5225
INSERT INTO thsVetDiagHist (dh_DiagCode, dh_DogID, dh_DiagID) VALUES (6639, @DogID, @DiagID)
ELSE
IF @DiagCode = 3940
INSERT INTO thsVetDiagHist (dh_DiagCode, dh_DogID, dh_DiagID) VALUES (6639, @DogID, @DiagID)
ELSE
IF @DiagCode = 8979
INSERT INTO thsVetDiagHist (dh_DiagCode, dh_DogID, dh_DiagID) VALUES (8301, @DogID, @DiagID)
ELSE
IF @DiagCode = 10030
INSERT INTO thsVetDiagHist (dh_DiagCode, dh_DogID, dh_DiagID) VALUES (8301, @DogID, @DiagID)
ELSE
IF @DiagCode = 986
INSERT INTO thsVetDiagHist (dh_DiagCode, dh_DogID, dh_DiagID) VALUES (26, @DogID, @DiagID)
ELSE
IF @DiagCode = 413
INSERT INTO thsVetDiagHist (dh_DiagCode, dh_DogID, dh_DiagID) VALUES (26, @DogID, @DiagID)
ELSE
INSERT INTO thsVetDiagHist (dh_DiagCode, dh_DogID, dh_DiagID) VALUES (@DiagCode, @DogID, @DiagID)
END

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-03 : 01:49:16

-----------
Ironically this record already exists - is not harmed in anyway. My data is updated in the trigger table and my history table is updated correctly. Run a new query and everything looks great.
------------

to clarify:
1. the trigger table is updated
2. history table is updated
3. the data on focus changes to an existing one

looks like the table is being sorted after insert

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-05 : 23:51:29

Please post your answers on the forum...
-----------------
Correct - on the table level its annoying but not an issue. However, on a
subform linked to a form - its very confusing for the user. They enter a
Diagnosis, go to enter the next diagnosis and their original entry appears to
have "changed." exit that record come back in and its the way they entered it.
Basically that is unacceptable. Is there anyway to prevent the resort? I am
using Access 2000 as the front end.

Thanks
-----------------

If it's on the front end, I guess you have bound controls.

If this is your design, then IMHO, you can set a marker so that after refresh or update, you go back to that record.

I guess the simplest is to unbound the controls. I don't usually bound the controls to a data object, as this somewhat restrict what I want to do.

hope this helps...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources