smalloy
Posts: 107
|
| Posted: 01/19/2005, 2:03 PM |
|
What's the best way to capture all of the changes to the data so that one could "audit" who changed/added what and when. I thought perhaps a trigger (I am using SQL server) but then how can I pass the session variables(user) to the trigger. Also, how can I just capture the changed fields and have a good way to show these changes in a history grid?
Anyone do this before?
Thanks!
_________________
Anything can be done, just give me time and money. |
 |
 |
Nicole
Posts: 586
|
| Posted: 01/20/2005, 2:49 AM |
|
Hello,
You can add special fields like last_modified_date and modified_by into your table to store last modification date/time and user ID who updated a record. To pass values to a table fields fill Default Value properties of corresponding controls on a form. Set
Now() for last_modified_date or select predefined CurrentDate or CurrentDateTime setting
And
CCGetUserID for modified_by field.
_________________
Regards,
Nicole |
|
|
|
smalloy
Posts: 107
|
| Posted: 01/20/2005, 6:01 AM |
|
Hmmm, that will solve my issue, but when the user performs an UPDATE, it sets the old value to new, I think I'll use a trigger for the updates which can get the user info from the modified_by field as you suggested!
How do you handle the UPDATE's history?
Also, thank you very much for your assistance.
Steve
_________________
Anything can be done, just give me time and money. |
|
|
|
Ben Denison
|
| Posted: 01/20/2005, 8:11 AM |
|
I went through the same problem some time ago. I noticed that CCS
performs an update on all the records that are displayed in a grid
regardless if the data has been changed.
Do you need to store just the last date/user updated or are you going to
keep a history of the data (i.e. snapshots or field changes)?
smalloy wrote:
> What's the best way to capture all of the changes to the data so that one could
> "audit" who changed/added what and when. I thought perhaps a trigger (I am
> using SQL server) but then how can I pass the session variables(user) to the
> trigger. Also, how can I just capture the changed fields and have a good way to
> show these changes in a history grid?
>
> Anyone do this before?
>
> Thanks!
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
smalloy
Posts: 107
|
| Posted: 01/20/2005, 8:18 AM |
|
I need to keep the history of the data (i.e. the OLD data) too. Its financial information so we need to know who made the change and what change was made.
Thanks for the help!
Steve
_________________
Anything can be done, just give me time and money. |
|
|
|
Ben Denison
|
| Posted: 01/20/2005, 8:59 PM |
|
There is probably a better way to do this, but, I did one that stores
the history of the record along with the user, date, and type of change.
The delete trigger adds a DELETE record. You can also check for the
change and then write only the changed field into another table. If you
need a sample of what I did for that let me know and I'll try and find it.
CREATE TRIGGER trg_Badges_upd ON dbo.Badges
FOR UPDATE
AS
SET NOCOUNT ON
DECLARE @iBadgeID int,@iSubID int,@iCompID int, @iCurBadgeNbr int,
@iIntBadgeNbr char (10),@iSubmitDate datetime,.....
DECLARE @dBadgeID int,@dSubID int,@dCompID int, @dCurBadgeNbr int,
@dIntBadgeNbr char (10),@dSubmitDate datetime,......
SELECT @iBadgeID = i.BadgeID, @iSubID = i.SubID, @iCompID =
i.CompID, @iCurBadgeNbr = i.CurBadgeNbr, @iIntBadgeNbr =
i.IntBadgeNbr, @iSubmitDate = i.SubmitDate,..... FROM inserted i
SELECT @dBadgeID = d.BadgeID, @dSubID = d.SubID, @dCompID =
d.CompID, @dCurBadgeNbr = d.CurBadgeNbr, @dIntBadgeNbr =
d.IntBadgeNbr, @dSubmitDate = d.SubmitDate, .....FROM deleted d
IF @dBadgeID <> @iBadgeID or @dSubID <> @iSubID or @dCompID <> @iCompID
or @dCurBadgeNbr <> @iCurBadgeNbr or @dIntBadgeNbr <> @iIntBadgeNbr or
@dSubmitDate <> @iSubmitDate .....
BEGIN
INSERT INTO dbo.BadgeHistory(BadgeID, TranDate, TranUser, TranType,
SubID, Name, CompID, CompanyName, CurBadgeNbr, IntBadgeNbr, SubmitDate, ...
SELECT d.BadgeID, getdate(), @iChangeUser, 'UPDATE', d.SubID, ....
FROM deleted d LEFT OUTER JOIN
dbo.Sub......
END
smalloy wrote:
> What's the best way to capture all of the changes to the data so that one could
> "audit" who changed/added what and when. I thought perhaps a trigger (I am
> using SQL server) but then how can I pass the session variables(user) to the
> trigger. Also, how can I just capture the changed fields and have a good way to
> show these changes in a history grid?
>
> Anyone do this before?
>
> Thanks!
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
smalloy
Posts: 107
|
| Posted: 01/21/2005, 7:07 AM |
|
Thank you for your guidence, I learned a lot from your example!
I have some questions though, I was hoping that my history could capture the user so I wouldn't have to have a user field in my some 50+ tables. My thougts were that the data tables just held the data and the history table(s), perhaps just 3 for the entire database, would hold something like:
-User
-Change date/time
-table
-field
-old value
-new-value
I figured I would just link the table & field fileds in a query, order by change date and then as part of the GUI dispay in a view_history grid.
I just can't figure out how to get the user info to the trigger without it being in the table itself.
Thanks again!
Steve
_________________
Anything can be done, just give me time and money. |
|
|
|
BRD
|
| Posted: 01/21/2005, 7:59 AM |
|
First, how are you connecting to SQL Server? Domain account, or SQL
Server account? Look at the USER, USER_ID, USER_NAME, SYSTEM_USER,
SESSION_USER, CURRENT_USER, SUSER_SID, and SUSER_SNAME functions
available in SQL to determine the best option for your app. If you are
using one login for the entire app this may not work, so I believe the
only way to get the user id into the trigger is to have a UserID field
in the table. (Unless anyone else has a better idea).
I did the same thing at another client using this sample:
CREATE TRIGGER trg_Owners_upd ON dbo.Owners
FOR UPDATE
AS
DECLARE @PrjID VARCHAR(8)
DECLARE @OwnerID INT
DECLARE @MG_User VARCHAR(8)
DECLARE @OldVal VARCHAR(50)
DECLARE @NewVal VARCHAR(50)
SELECT @PrjID = PrjID FROM inserted
SELECT @OwnerID = OwnerID FROM inserted
SELECT @MG_User = UserLastChange FROM inserted
IF UPDATE(PctEquity)
BEGIN
SELECT @OldVal = CONVERT(VARCHAR(50),PctEquity) FROM deleted
SELECT @NewVal = CONVERT(VARCHAR(50),PctEquity) FROM inserted
IF @OldVal <> @NewVal AND @OldVal <> NULL AND @OldVal <> '0'
EXEC uspRecordOwnerChanges
PrjID,@OwnerID,'PctEquity',@OldVal,@NewVal,@MG_User
END
IF UPDATE(PctNOI)
BEGIN
SELECT @OldVal = CONVERT(VARCHAR(50),PctNOI) FROM deleted
SELECT @NewVal = CONVERT(VARCHAR(50),PctNOI) FROM inserted
IF @OldVal <> @NewVal
EXEC uspRecordOwnerChanges
@PrjID,@OwnerID,'PctNOI',@OldVal,@NewVal,@MG_User
END
............................
smalloy wrote:
> Thank you for your guidence, I learned a lot from your example!
>
> I have some questions though, I was hoping that my history could capture the
> user so I wouldn't have to have a user field in my some 50+ tables. My thougts
> were that the data tables just held the data and the history table(s), perhaps
> just 3 for the entire database, would hold something like:
>
> -User
> -Change date/time
> -table
> -field
> -old value
> -new-value
>
> I figured I would just link the table & field fileds in a query, order by
> change date and then as part of the GUI dispay in a view_history grid.
>
> I just can't figure out how to get the user info to the trigger without it
> being in the table itself.
>
> Thanks again!
> Steve
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
smalloy
Posts: 107
|
| Posted: 01/21/2005, 8:36 AM |
|
I am using a single login to access the database, I guess thats going to be myn hurdle. I'll take a look at those functions. I like your trigger too, If I find a function that works I'll post it with my solution.
As an aside, I found a bank data base (SQL Server) with a history table that stored the changes with pipes '|', I'm still looking at the database for some idea's but I have no middle tier or user app. to help me.
Thanks again!
_________________
Anything can be done, just give me time and money. |
|
|
|
|
