![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 11 total |
![]() |
Thu, Mar 5 2009 9:16 AM | Permanent Link |
"Robert" | I have a customer that wants to log EVERY change to a table. They want
before and after values on each changed field. I assume the idea is to grab the record on before post and loop thru the fields. It sounds horrible, but maybe it's the only solution. Has anybody done something like this? Any tips, examples, wise words of advice? Thanks. Robert |
Thu, Mar 5 2009 9:48 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Robert
Last time I did something like that was c1985 when I was young and naive and my word of wisdom is - don't - On a more serious note find out why. It may be that they think this is a solution to a problem and they are wrong. Roy Lambert |
Thu, Mar 5 2009 11:18 AM | Permanent Link |
"Rita" | "Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message news:0D7EDA32-99DD-4249-9F6A-6BDEAA233A5D@news.elevatesoft.com... >I have a customer that wants to log EVERY change to a table. They want >before and after values on each changed field. > What a pain its doable but the customer is never right. Assume lots of tables ? Who will have rights to the log ? Even the data manager can cover his/her tracks. A couple of new fields on each table, say -- RecordChangedAt.DateTimeStamp next -- ByWhom.Changed.String That info will need to go to another identical table or someone else will come along and u will never find what the 1st guy wipes out. Text log files dont stand a chance, Carbon copies of all the tables is the only real way to go. I know coz I had to redo my booking screen to find out who deleted some calls taxi firms never showed up for. After I put that in by altering all the booking's code the customer wanted it on over 200 other tables, I told him to get lost but then I had that sort of relationship with most of my customers "Erin Brokovitch there called boobs Ed." Good luck tho I will think off u while I have my feet up. Rita |
Thu, Mar 5 2009 3:02 PM | Permanent Link |
"Jeff Cook" | Roy Lambert wrote:
> Robert > > > Last time I did something like that was c1985 when I was young and > naive and my word of wisdom is - don't - > > On a more serious note find out why. It may be that they think this > is a solution to a problem and they are wrong. > > Roy Lambert Or on the contrary, it can be a useful tool to find out what when wrong, "The reason that so-and-so stopped getting email statements was that Joe deleted the email address and he did it on 12 January 2009 at 12:15 pm". I agree with other comments at this is pretty useless from a security point of view (though most of our users don't even know it is there and if they did they'd have no idea how to fiddle it) - but for training and support it is potentially invaluable. You can also gain insights into how much maintenance is done on tables etc. - you can tell when the log table swells ![]() We do it on master tables only - we have a separate audit trail for transactions. I'll post my code below - it called from the BeforePost and BeforeDelete of each table you want to log. The gurus will instantly realise that any changes done by SQL will not be logged automatically. I have avoided this problem by avoiding SQL changes to master tables - if I *had to* do SQL changes, then I guess I'd create the log entries at the time. Cheers Jeff --------------- I've used a few of my own functions, but should be self explanatory. /* SQL-92 Table Creation Script with DBISAM Extensions */ CREATE TABLE IF NOT EXISTS "Log" ( "Type" VARCHAR(16), "LoggedAt" TIMESTAMP, "ComputerName" VARCHAR(40), "UserName" VARCHAR(40), "TableName" VARCHAR(20), "LogDetail" BLOB, NOCASE PRIMARY KEY ("LoggedAt","ComputerName","UserName","Type") COMPRESS FULL LANGUAGE "ANSI Standard" SORT "Default Order" USER MAJOR VERSION 1 ); CREATE NOCASE INDEX IF NOT EXISTS "idxTypeTable" ON "Log" ("Type","TableName","LoggedAt"); procedure TCommon.Log(EventText: string; TableName: string = ''; sType: string = ''); begin if apmDM.LogTable.Exists then begin apmDM.OpenTable(apmDM.LogTable); apmDM.LogTable.Insert; apmDM.LogTableType.AsString := sType; apmDM.LogTableLoggedAt.AsDateTime := Now; apmDM.LogTableComputerName.AsString := ComputerName; apmDM.LogTableUserName.AsString := UserName; apmDM.LogTableTableName.AsString := TableName; apmDM.LogTableLogDetail.AsString := EventText; try apmDM.LogTable.Post; except Sleep(1000); apmDM.LogTableLoggedAt.AsDateTime := Now;// Avoid duplicate keys apmDM.LogTable.Post; end; end; end; procedure TCommon.LogTableChange(Table: TDBISAMTable); var s, s1, s2, s3: string; i: integer; img: TImage; bImage: Boolean; begin if Table.State = dsInsert then s3 := 'Table Insert' else if Table.State = dsEdit then s3 := 'Table Change' else s3 := ''; s := ''; with Table do for i := 0 to FieldCount - 1 do begin if (Fields[i].FieldKind = fkData) and ((i < 4) or (Fields[i].Value <> Fields[i].OldValue)) then begin bImage := False; if Fields[i].IsBlob then begin img := TImage.Create(Self); try if DBPhotos.PhotoFieldToImage((Fields[i] as TBlobField), img) then bImage := True; finally img.Free; end; end; if bImage then begin if (Fields[i].Value <> Fields[i].OldValue) or (Table.State = dsInsert) then s := s + Fields[i].FieldName + ': New Image'#13#10 else s := s + Fields[i].FieldName + ': Unchanged Image'#13#10; end else begin try s1 := Fields[i].OldValue; except s1 := '(null)'; end; try s2 := Fields[i].Value; except s2 := '(null)'; end; if (Fields[i].Value <> Fields[i].OldValue) then s := s + Fields[i].FieldName + ': ' + s1 + ' => ' + s2 + #13#10 else s := s + Fields[i].FieldName + ': ' + s2 + #13#10; end; end; end; if s <> '' then Common.Log(s, Table.TableName, s3); end; procedure TCommon.LogTableDelete(Table: TDBISAMTable); var s, s2: string; i: integer; begin s := ''; with Table do for i := 0 to FieldCount - 1 do begin if (Fields[i].FieldKind = fkData) then begin try s2 := Fields[i].Value; except s2 := '(null)'; end; s := s + Fields[i].FieldName + ': ' + s2 + #13#10; end; end; if s <> '' then Common.Log(s, Table.TableName, 'Table Delete'); end; -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz + Joan and Jeff Cook The Cooks Oasis www.cookislandsoasis.com |
Thu, Mar 5 2009 3:40 PM | Permanent Link |
"Robert" | "Jeff Cook" <jeffc@aspect.co.nz> wrote in message news:4FD16FAA-CD9E-472F-8031-9D74E59FF5D6@news.elevatesoft.com... > Roy Lambert wrote: > Thanks a lot for the reply and the code. This might work well for the application in question, since we already have a transaction log and I believe all changes to "master" tables are made using tTables. Why the test for 4 when looping thru the tables? I assume it is some unique issue with your table layout. Please explain. Have you ever cosidered using the persistent field DisplayLabel instead of the field name? Robert |
Thu, Mar 5 2009 3:41 PM | Permanent Link |
"Robert" | "Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message news:3E801349-819F-4D7F-957B-87821A9CF56D@news.elevatesoft.com... > Robert > > > Last time I did something like that was c1985 when I was young and naive > and my word of wisdom is - don't - > > On a more serious note find out why. It may be that they think this is a > solution to a problem and they are wrong. > I hear you, but if it needs to be done, then I need to have a plan for doing it. Robert |
Thu, Mar 5 2009 3:42 PM | Permanent Link |
"Robert" | "Rita" <no@spam.com> wrote in message news:88DAD738-5D87-4A5B-8990-491B343E4080@news.elevatesoft.com... > > "Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message > news:0D7EDA32-99DD-4249-9F6A-6BDEAA233A5D@news.elevatesoft.com... >>I have a customer that wants to log EVERY change to a table. They want >>before and after values on each changed field. >> > > What a pain its doable but the customer is never right. They do write the checks, a not totally inconsecuential matter. Robert |
Thu, Mar 5 2009 4:22 PM | Permanent Link |
"Jeff Cook" | Hi Robert
> <snip> > > Why the test for 4 when looping thru the tables? I assume it is some > unique issue with your table layout. Please explain. > We assume that the first 4 columns in a table are enough to uniquely identify the row that has been changed, so we log the contents of those whether changed or not. > Have you ever cosidered using the persistent field DisplayLabel > instead of the field name? > No! Should I? Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz + Joan and Jeff Cook The Cooks Oasis www.cookislandsoasis.com |
Thu, Mar 5 2009 5:57 PM | Permanent Link |
"Robert" | "Jeff Cook" <jeffc@aspect.co.nz> wrote in message news:6A804A26-62D0-476D-9EDB-4074B6EAFC0B@news.elevatesoft.com... > >> Have you ever cosidered using the persistent field DisplayLabel >> instead of the field name? >> > No! Should I? > Don't know in your case. For my databases, it would make the log much more readable, since the field names would be the the same as the column descriptions that users see in the grids, etc. My field names have a method to the madness, but they are not very descriptive to a user. Robert |
Fri, Mar 6 2009 4:17 AM | Permanent Link |
"Trevor Davis" | >I have a customer that wants to log EVERY change to a table. They want before
>and after values on each changed field. > > I assume the idea is to grab the record on before post and loop thru the > fields. It sounds horrible, but maybe it's the only solution. > > Has anybody done something like this? Any tips, examples, wise words of > advice? Thanks. Robert, I just did this as a custom change for a client that licenses our source code. They have the SQL Server version of our accounting software and wanted it done via triggers, which works well... no messing about with the application (other than the form that creates the database tables and triggers, etc.), and the code runs on the server. If ElevateDB supports triggers I would suggest doing it that way rather than via the BeforePost and BeforeDelete events. -- Trevor Davis Davis Business Systems Ltd. www.dbsonline.com BS1 Accounting, Time Billing, Distribution, and Manufacturing Software Delphi Source Code (royalty-free) BS1 Professional Time Billing and Accounting is free for Delphi and C++Builder programmers |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |