SQL Trigger Causing Weird Behaviour in Access Front End...

[JonV72]

Hi all,

Am looking for advice regarding a very odd problem..

Our booking system is an Access 2003 front end on a SQL Server 2000 (I know..!) back-end.

Our finance department have asked us to implement an auditing solution to enable them to track changes in key tables. I've implemented this using a combination of INSERT, UPDATE and DELETE triggers which write details of changed records to audit tables (with unique IDs for each change).

This is all working fine for most of the tables which are being audited but following the changes I'm experiencing problems with one particular table with regard to the access front end.

This relates to a subform of the main booking form which has an embedded button to create a new record in one of the tables which is being audited (using DoCmd.GotRecord,,acNewRec). On clicking this a new record is being created, and the trigger is fired to create a new record in the audit table.

The odd thing is that rather than going to a new record in the subform, access is actually taking the user to a completely unrelated record.

On investigation I've discovered that the record that the user is taken to has a primary key which matches the unique ID of the newly created record in the audit table, rather than the source table.

eg

Form is bound to Credits table
Click to enter new record
New record is created in the Credits table with CreditID (PK) of 2668.
New record is created in the AUDIT_Credits table with an AuditID (PK) of 81 detailing the insertion of Record ID 2668.
Access moves focus of form to CreditID 81 rather than 2668

...so I conclude that Access is somehow picking up the ID of the audit record rather than that of the "source" record and shifting the focus of the form accordingly.

This is the first time I've come across this issue in all the years I've been using Access. Really stumped and would be grateful for any advice.

Thanks

Jon

 

[SOS]

For my stuff I put the audit trail stuff in Access itself and then write it to SQL Server. But then again nobody here can write directly to the SQL Server but needs to go through Access (the audit trail I use uses ADO to insert the record in the audit trail table).

But what kind of code is on your form? I don't understand how Access can move to a record of the key of the audit trail table unless you have something in code that is set up wrong. It shouldn't do it by itself as the audit trail table shouldn't be a part of the form's record source. So only some code would be doing it.

 

[JonV72]

The code in the form is literally just using DoCmd.GotRecord,,acNewRec with the form itself bound to the credits table.

The trigger is as follows - the AUDIT_CREDITS table has an identity column called AuditID which is it's primary key:

ALTER TRIGGER [dbo].[TRG_AUDIT_Book_Credits_INSERT]
ON [dbo].[TBL_S_Book_Credits]
AFTER INSERT
AS
BEGIN

SET NOCOUNT ON;


INSERT INTO
AUDIT_BOOK_CREDITS
SELECT
CreditNo,
BookCode,
Code,
HOCode,
Title,
FName,
LName,
HouseNo,
Add1,
Add2,
Add3,
Add4,
Add5,
PostCode,
CreditDate,
TaxPoint,
Net,
VAT,
Gross,
VATCode,
Locked,
CreditNote,
Phone1,
Phone2,
Source2,
CD,
HH_Gross,
Ins,
Bank,
HO,
HH_Net,
HH_VAT,
HOPaid,
'INSERT' as AuditAction,
getdate() as AuditDate,
system_user as AuditUser
from
INSERTED

END

My best guess is that the issue is that Access must be using SCOPE_IDENTITY() - in this case it's returning the primary key of the Audit Table which is the last inserted record rather than that of the Credits table which is the one it should be using. I suspect the solution may lie in somehow passing the correct ID back to Access but have no clue how to do this.

 

[SOS]

The trigger should have absolutely no effect on Access whatsoever. Whatever is causing it is likely in Access itself.

 

[JonV72]

I agree that the trigger shouldn't be causing an issue but it definitely is.... If I disable it, then the form returns the correct record. The problem has only started occurring since introducing the trigger, and the system has been in use for 10 years! There have been no changes to the front end.

As a further test I just dropped the audit table in SQL and recreated it without an identity column and reactivated the trigger - it now works perfectly so it definitely seems that it's a problem with the ID being returned.

I'd leave it at that but I really need a unique ID for each auditable transaction.

 

[JonV72]

Looks like I have a solution... A bit of further googling has turned this up....

//stackoverflow.com/questions/14349671/why-is-my-sql-server-auditing-trigger-messing-up-obdc-call-referesh-from-access

..it looks like Access does pick up the ID of the last inserted record as it uses the @@identity variable. The solution is to spoof it be inserting the correct ID into a tmp table as part of the trigger.

My trigger is now as follows and all seems to be working OK:

ALTER TRIGGER [dbo].[TRG_AUDIT_Book_Credits_INSERT]
ON [dbo].[TBL_S_Book_Credits]
AFTER INSERT
AS
BEGIN

declare @id int
set @id = @@identity

SET NOCOUNT ON;



INSERT INTO
AUDIT_BOOK_CREDITS
SELECT
CreditNo,
BookCode,
Code,
HOCode,
Title,
FName,
LName,
HouseNo,
Add1,
Add2,
Add3,
Add4,
Add5,
PostCode,
CreditDate,
TaxPoint,
Net,
VAT,
Gross,
VATCode,
Locked,
CreditNote,
Phone1,
Phone2,
Source2,
CD,
HH_Gross,
Ins,
Bank,
HO,
HH_Net,
HH_VAT,
HOPaid,
'INSERT' as AuditAction,
getdate() as AuditDate,
system_user as AuditUser

from
INSERTED


DECLARE @SQL varchar(8000)
SET @sql = 'SELECT IDENTITY(INT, ' + CAST(@id as varchar) + ', 1)
AS ident INTO #Tmp'
EXEC(@sql)



END