SQL trigger affecting a form (Access 97 SR2/SQL server 2000)

Topic

New Reply

Hi,

I have a single view form that allows use of a list of data i.e. adding and editing. The forms data source is an odbc link to a table stored on SQL server 2000.

There is a trigger on the table that writes to an audit table details of the event (user name, date, type of event etc).

Here’s the strange bit: when a record is added (after details entered, save button presses or tab changed), the record currently selected changes to a record seemingly random from the entire list as if you’ve just filtered for a completely different record! The users then have to search for the record they just added.

Without the trigger the form works fine, i.e. add a record and the form stays on the record added.

Has anyone seen this sort of thing before?

Its basically a trigger to say, “when something is added to table A, add a description to table B”. I don’t see why it should cause another record to display!

Any help would be much appreciated.

Reply | Quote

Replies

I have got a bit further.

From the following section of the trigger:

insert into tblUserAudit (TableName, UserName, [Action], [DateTime], numberofrecords)
select ‘TblEducationSourceTable’, suser_sname(), ‘INSERT’, getdate() , count(i.educationnumber)
from inserted i

Somehow the tbluserAudit primary key (ID) was being returned and that was being searched for in tbleducationsourcetable in that tables primary key field (educationnumber). Effectively, this meant whenever a record was added tbleducationsource.educationnumber = tbluseraudit.id was selected as the new record.

Removing the primary key on tbluseraudit stopped the searching problem as described above, but adding a record still manages to exclude from the recordset the record added and show a new record instead. Refreshing the recordset displays the added record and I can write a workround for this but i’d rather find out how to prevent it in the first place – any help would be much appreciated.

Reply | Quote

Going out on a limb: perhaps there is a clustered index on the SQL table, and the addition of a new record somehow confuses your app? When SQL Server uses an index, it finds the storage location of the rows needed by the query and extracts only the needed rows. The addition of a new record would force a reshuffle of the data structure — kind of a SQL based bait and switch.

OK, reading back over my “theory”, it’s waaaay out on a limb!! Good luck!

Reply | Quote

I think you might be right about the indexes. There are no clustered indexes, however, I duplicated the table to be best of my ability. I think I have all of the primary key/foreign key/indexes.

My table worked fine until I added the indexes, when I started removing the indexes it worked again after I removed the index on the with the rowguid stuff on it (sorry, dont know the tech term).

After celebrating too early, I tried removing that index on my original table………… and it still doesnt work properly!!

Im currently trying to document each table to see if there are any other differences other than number of records in the tables.

Reply | Quote

I think you’re actually having a fight with ODBC – unless you are using ODBC direct or pass-through queries to view your data from table A. I’m also curious why you are using something that looks like a rowguid – is it actually a SQL TimeStamp field? If that’s the case, you might want to look at using a Date/Time field instead and doing your own date and time capture using the GetDate function in SQL.

Reply | Quote

Thanks for replying Wendell.
The rowguid field is type uniqueidentifier, ‘Is RowGuid’ is set to yes and (newid()) is its default value.

There must be another difference between the two tables I have, I just need to find time to document each fully.
I might append all of the data in my orignal table to the new one, just in case it is something to do with the number of records.

The method used is a plain old ODBC link from access with a standard form. I think it does the same if I enter data from the link (i.e. into the ‘table’ in access). It doesnt happen if I enter data directly into the SQL table though so I think you might be right about ODBC having something to do with it.

I’ll post again when i’ve either found out more information or determined the difference between my two tables.

If anyone happens to have a Eurika moment, please let me know, thank you.

Reply | Quote

Is this a table that was upsized from Access and had at one point been replicated? I suspect at least some of your troubles are coming from the “RowGuid” field, especially if you are trying to put an index on it. We’ve seen all sorts of weird behavior when you try to use them as the primary key for a table using an ODBC connection to Access. What function does that field currently provide.?

Reply | Quote

I think it used to be replicated using that field as the table is used on more than one site, but isn’t any more, we use a different system.
Its definately not the primary key, but it was indexed. That didnt seem to make much difference when I took the index’s off. Ill try removing the default value as well, just in case.

I still havent got round to checking the differences in tables that do and dont work due to another task, but ill make a post as soon as i’ve checked it.

Reply | Quote

I nice lady from Microsoft support helped me out. Turns out the audit table was overwriting the inserted table created for the original record. By storing the original ID in a variable and creating a tmp table using the ID, the new inserted table was overwritten and the ID was back in @@IDENTITY in time to return it to the recordset which originally added a record! I think I explained that correctly.

Thanks for all your help trying to locate the solution though.

Reply | Quote