I have tried to whittle the situation down as much as possible. I have created an empty test DB, and in that DB I have created 2 tables, Table1 and Table2. Each has a Key ID field and 2 data fields, TT1ID, TT1Data1, TT1Data2 etc. Table2 has no other features; I have created the trigger only on Table1:
Create Trigger Testing on dbo.Table1
For Insert, Update
As
Declare @Data1 char(10)
Set @Data1 = (Select TT1Data1 from inserted)
insert into Table2 (TT2Data1) Values (@Data1)
I open (return all rows) both tables in Enterprise Manager. I enter a row in Table1 manually, then another, then another. When the tables start both empty, and therefore have ID fields that are in 'sync', there is no problem with the expected behavior of the trigger. The data entered into Table1 is inserted into Table2 OK.
Table1, however, has the very strange behavior of having all of its ID fields being '0, 0, 0, ...'. If I 'run' Table1, it will correct the ID fields as '1, 2, 3, ...' .
If I manually insert a row into Table2, so that the ID fields are no longer in sync, then manually insert a row into Table1, the Table hangs up, and I have to 'run' the Table again to get it to clear and fix itself.
This seems as basic as can be, and I know that we put all of the patches to SQL that we could find as recommended when we reinstalled SQL several weeks ago. It would help to know if someone is able/not able to recreate this problem on their system.
Thank You
Rollin Burr
Politicians and diapers have one thing in common. They should both be changed regularly, and for the same reason.