UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Primary Key For Tmp Table In Allen Browne's Great Audit Trail Utility, Any Version    
 
   
bobalston
post Aug 1 2020, 04:01 PM
Post#1



Posts: 128
Joined: 12-October 04
From: Dallas area


I am a long time user of Allen Browne's great audit Trail Utility. I am preparing one application in which I use it for conversion to SQL server. I am ok adding a primary key to the audit trail tables except for the TMP audit trail. Anyone else faced this problem and figured it out?

thanks

Bob
Go to the top of the page
 
GroverParkGeorge
post Aug 1 2020, 05:52 PM
Post#2


UA Admin
Posts: 37,630
Joined: 20-June 02
From: Newcastle, WA


What problem, specifically, is that?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
bobalston
post Aug 1 2020, 06:26 PM
Post#3



Posts: 128
Joined: 12-October 04
From: Dallas area


The problem is that there is no built in field suitable for making a primary key. And the author is very specific about what fields to use - prefix data and user data. Don't want to mess it up. And don't want to spend time trying to figure out something if someone has done so before me.

Bob
Go to the top of the page
 
nvogel
post Aug 2 2020, 12:39 AM
Post#4



Posts: 1,135
Joined: 26-January 14
From: London, UK


Are you trying to audit SQL Server based on triggers? That's not necessary because SQL Server has great audit/history features built in (Change Data Capture, Change Tracking, Temporal Tables and Audit). Triggers require a lot more effort and triggers that insert or modify data are generally deprecated because of the performance overheads, the potential to cause unnecessary blocking and the complexity of supporting them.

If you have more complex, custom requirements then there are lots of third-party audit solutions as well.

This post has been edited by nvogel: Aug 2 2020, 12:40 AM
Go to the top of the page
 
bobalston
post Aug 2 2020, 04:39 AM
Post#5



Posts: 128
Joined: 12-October 04
From: Dallas area


Thanks for that knowledge. Probably something I will investigate down the line. Right now, though, I am planning on converting what I have to SQL Server without changing anything I don't have to. so I would like to stay with Allen Brown's utility - for now.

Bob
Go to the top of the page
 
nvogel
post Aug 2 2020, 12:44 PM
Post#6



Posts: 1,135
Joined: 26-January 14
From: London, UK


You should also bear in mind that SQL Server DML triggers only get called once per update/insert/delete event, not once per row. Trigger code in SQL Server therefore has to be able to handle multi-row DML - otherwise either your trigger won't work correctly and may not log all the updates or it will cause errors that will prevent other people's updates from working.
Go to the top of the page
 
FrankRuperto
post Aug 2 2020, 02:34 PM
Post#7



Posts: 1,219
Joined: 21-September 14
From: Tampa, Florida USA


In Informix db, a "CREATE AUDIT ON targettable" SQL statement automatically creates an audit file for the target table and an audit record is created with a timestamp, transaction type, deviceID, userID, and all field values every time a record in the target table is created, read, updated, and deleted. Pretty much the same thing a db server with transaction logging is doing, except for the specified target table.

In my Access pawnshop app, I created a customer history table, and everytime users add or edit a customer record, a copy of the customer record is saved to the history table. I then have a customer history form tab in my customer form and you can scroll through each history record and the history form highlights which field values have changed, the UserID and the date/time the change occured.
This post has been edited by FrankRuperto: Aug 2 2020, 02:40 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
nvogel
post Aug 2 2020, 03:35 PM
Post#8



Posts: 1,135
Joined: 26-January 14
From: London, UK


In SQL Server:

ALTER TABLE your_table SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.your_history_table));
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th August 2020 - 04:32 AM