|
Sometimes I fire the proc from code, and sometimes from
within another proc - it just depends on the situation. I do all
deletes via stored proc, so those have the tracking procedure in them.
Sometimes I want to track things that don't necessarily coincide with a record
change.
Tom Oakes
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Angelo Antonino Sent: Friday, October 28, 2005 8:38 PM To: [email protected] Subject: RE: [AccessDevelopers] ADP / SQL Server log tools Thanks
Tom. So you trigger the sp
from VBA? There are tools
available that record changes to the SQL log files, and allow reporting on these
changes. Any one had any
experience with these? thanks, Angelo ________________________ Tanzanite
Software Solutions M:
0403317772 -----Original
Message----- Angelo,
I
do something like this in most of my applications. I have a table named
tblTrack, that looks something like this: TrackID int
(identity) TrackIP nvarchar 15 TrackType nvarchar 50 TrackAction
nvarchar 50 TrackLinkID
int dtmTrackStamp datetime 8 (default
value = getDate()) The stored proc that populates it is pretty simple:
ALTER
Procedure spAddTrack ( @TrackIP
nvarchar(15) = Null, @TrackLogID
int = 0, @TrackLinkID
int = 0, @TrackType
nvarchar(50) = Null, @TrackAction
nvarchar(50) = Null, @TrackText
nvarchar(255) = Null ) As INSERT
INTO tblTrack (TrackIP, TrackLogID, TrackLinkID, TrackType, TrackAction,
TrackText) VALUES
(@TrackIP, @TrackLogID, @TrackLinkID, @TrackType, @TrackAction,
@TrackText) --------------------------------------------------------------------------------------------------------------------------------------- TrackIP
= Many of my applications have a web interface, so I capture the IP address, if
applicable. LogID
= ID of user who is logged into the program. You may want to use the
Windows username (a string), if you're not using custom
security. TrackType
= this is kind of a "category" that lets me know what domain/table we're dealing
with (i.e. "Product", "User", "Project", "Invoice"). TrackAction
= the action that took place ("Add", "Update", "Delete", "Failure",
....) TrackLinkID
= If TrackType is specified, this should be the primary key of the record in
question. This is optional. TrackText =
descriptive
text of what happened. Optional. --------------------------------------------------------------------------------------------------------------------------------------- So...when
a product is deleted from the database, this proc fires prior to the actual
delete: EXEC
spAddTrack "24.21.140.182", 1900, 20332, "Product", "Delete", "Widget
#4055" ...when
a new customer is added: EXEC
spAddTrack "24.21.140.182", 1900, 901, "Customer", "Add", "NW Media
Inc." ...when
someone tries to login and fails: EXEC
spAddTrack "24.21.140.182", 1900, 0, "Login", "Failure",
"" And
so forth. You
could make this a lot more complicated, obviously. One of my clients wants
to be able to see an audit trail of everything that happens on a Project/WBS
basis; so when a product is deleted, for instance, we also store that product's
project id with the audit record. Hope
that helps you. Tom
Oakes From:
[email protected] [mailto:[EMAIL PROTECTED]
On Behalf Of Angelo
Antonino Hi, I have an ADP connected to
a SQL Server 2000. I need to keep a log of
changes done to the data (ie. Edit’s, Create, Delete – who did it, time,
field…etc). The more detail logged the better. Has anyone had experience
with any tools that do such a thing? I could create a stored
procedure, that would populate a table every time data changes, but I want to
investigate what is out there first. Any thoughts
appreciated. thanks, Angelo ________________________ Angelo
Antonino Tanzanite
Software Solutions M:
0403317772 Please zip all files prior to uploading to Files section.
SPONSORED LINKS
YAHOO! GROUPS LINKS
|
- [AccessDevelopers] ADP / SQL Server log tools Angelo Antonino
- RE: [AccessDevelopers] ADP / SQL Server log tools Tom Oakes
