Re: Audit File Updates via Triggers...
On Wed, Nov 21, 2018 at 12:35 PM Benedict, Tom via 4D_Tech < 4d_tech@lists.4d.com> wrote: > If you live on an island where the only language is 4D, objects offer > some advantages. But if there's ever the chance that you'd like to > communicate with the outside world the lingua franca of rows and columns > makes you friends around the globe. It's a peninsula, actually. -- Kirk Brooks San Francisco, CA === *We go vote - they go home* ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Audit File Updates via Triggers...
Kirk Brooks writes: >John, >Sure you can - you make a method to extract the data >you want. In selection based listboxes you can put the >method in a column formula. >On Tue, Nov 20, 2018 at 1:14 PM John DeSoi via 4D_Tech <4d_tech@lists.4d.com> wrote: >> Just note that you won't be able to see any of the data in an object >> field if you are displaying records in a list box. Some possible >> options now in version 17 if you are using collections/entity selections. I'm with John on the suitability of object fields. If you live on an island where the only language is 4D, objects offer some advantages. But if there's ever the chance that you'd like to communicate with the outside world the lingua franca of rows and columns makes you friends around the globe. Tom Benedict Optum This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Audit File Updates via Triggers...
John, Sure you can - you make a method to extract the data you want. In selection based listboxes you can put the method in a column formula. It's definitely easier in v17 but I've been using this in v15 for a long time. On Tue, Nov 20, 2018 at 1:14 PM John DeSoi via 4D_Tech <4d_tech@lists.4d.com> wrote: > Just note that you won't be able to see any of the data in an object field > if you are displaying records in a list box. Some possible options now in > version 17 if you are using collections/entity selections. > -- Kirk Brooks San Francisco, CA === *We go vote - they go home* ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Audit File Updates via Triggers...
As always sql does not see object fields. So if you use sql to move data
around.
Sent from my iPhone
> On Nov 20, 2018, at 3:56 PM, Robert ListMail via 4D_Tech
> <4d_tech@lists.4d.com> wrote:
>
> Wow, you guys are the best—GREAT idea! I think we all need to use objects
> more often and this is a great example—thanks Kirk.
>
>> On Nov 20, 2018, at 2:48 PM, Kirk Brooks via 4D_Tech <4d_tech@lists.4d.com>
>> wrote:
>> Robert,
>> My thoughts on the created by/modified by stuff:
>> 1) I moved all this to a single object field. I call it '_meta' and it's on
>> every table I want to manage.
>> 2) At a minimum the field has:
>> {created"; {"name: "", "date": ""},
>> "modified"; {"name": "", "date": ""}}
>
> Thanks,
>
> Robert
>
> ===
> Robert Broussard
> Houston, TX
> ===
>
> **
> 4D Internet Users Group (4D iNUG)
> Archive: http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
> **
**
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**
Re: Audit File Updates via Triggers...
Just note that you won't be able to see any of the data in an object field if you are displaying records in a list box. Some possible options now in version 17 if you are using collections/entity selections. John DeSoi, Ph.D. > On Nov 20, 2018, at 2:56 PM, Robert ListMail via 4D_Tech > <4d_tech@lists.4d.com> wrote: > > Wow, you guys are the best—GREAT idea! I think we all need to use objects > more often and this is a great example—thanks Kirk. ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Audit File Updates via Triggers...
Kirk,
since I don't have objects yet...
Trigger actions - 1 method
tracking actions (including created and modified) - 4 methods
On Tue, 20 Nov 2018 12:48:01 -0800, Kirk Brooks via 4D_Tech wrote:
> Robert,
> My thoughts on the created by/modified by stuff:
>
> 1) I moved all this to a single object field. I call it '_meta' and it's on
> every table I want to manage.
>
> 2) At a minimum the field has:
>
> {"created"; {"name: "", "date": ""},
>
> "modified"; {"name": "", "date": ""}}
>
> 3) I wrote a few (I think it's 5) methods to manage it. Mainly:
>
> META_UPDATE(ptr to _meta field) // generally in table trigger code
>
> // figures out the table and trigger event then update _meta
>
> Meta_get_text(object) -> returns a pretty text string of "Created: ...
> Modified: ... "
>
>
> Once I started using this I liked it so much I've converted most of my
> projects to it. Having a single method to return the create/mod info string
> is a delight. This field is also good for other types of meta data.
> Calculation dates are one I use. Could include things like last printed or
> whatever. Totally optional.
>
> Keep in mind trigger code always runs on the server regardless of where the
> record mod takes place. So client side IP vars are meaningless in a
> trigger. Current user is the fastest reliable way to know who the user is.
>
> Avoid trigger operations that do lookups or long verification procedures.
> Do that stuff prior to saving if you need it. Trigger code is best when
> it's lean and fast.
>
> On Tue, Nov 20, 2018 at 11:59 AM Robert ListMail via 4D_Tech <
> 4d_tech@lists.4d.com> wrote:
>
>> At a minimum, I just have the simple need of recording “CreatedBy” and
>> “ModifiedBy” values (username or ID) for one particular table. I do have
>> extra fields already in that table that could be repurposed and I would
>> probably add “DateModified” to that. However, that’s three fields
>> used just
>> for auditing. When I look back at previous posts I see that some of you are
>> tracking (saving audit data for) for tables and even individual fields. So,
>> as I have a chance to redesign this DB, I wanted to re-think how this is
>> implemented.
>>
>>
>> So, Chip, when you have the need to store the username (or ID) of the user
>> that created and/or last modified a key record, do you ever have this data
>> in the field of the original record? I’m sure I have seen this more
>> commonly where the data is in the original record. I guess to keep things
>> normalized, you would use a related table (or a standalone table) where
>> each transaction that you want to remember is stored. However, with the
>> separate table method I suppose you would have to search this audit table
>> every time your detail form is loaded (assuming you want to show who
>> created or modified a record) and that each time the record is updated the
>> audit table would be getting a new record.
>>
>> Thoughts about your data audit strategy are appreciated.
>>
>> Thanks,
>>
>> Robert
>>
>> ===
>> Robert Broussard
>> Houston, TX
>> ===
>>
>>> On Aug 26, 2015, at 9:26 AM, Chip Scheide <4d_o...@pghrepository.org>
>> wrote, Re: Triggers and error handling:
>>>
>>> so my triggers tend to look like this:
>>> case of
>>> (Database event = : (Database event=On Saving New Record Event)
>>> trk_Last_Modified (->[Account_Postings]Created_Who)
>>> trk_Last_Modified (->[Account_Postings]Modified_Who)
>>
>> **
>> 4D Internet Users Group (4D iNUG)
>> Archive: http://lists.4d.com/archives.html
>> Options: https://lists.4d.com/mailman/options/4d_tech
>> Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
>> **
>
>
>
> --
> Kirk Brooks
> San Francisco, CA
> ===
>
> *We go vote - they go home*
> **
> 4D Internet Users Group (4D iNUG)
> Archive: http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
> **
---
Gas is for washing parts
Alcohol is for drinkin'
Nitromethane is for racing
**
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**
Re: Audit File Updates via Triggers...
Wow, you guys are the best—GREAT idea! I think we all need to use objects more
often and this is a great example—thanks Kirk.
> On Nov 20, 2018, at 2:48 PM, Kirk Brooks via 4D_Tech <4d_tech@lists.4d.com>
> wrote:
> Robert,
> My thoughts on the created by/modified by stuff:
> 1) I moved all this to a single object field. I call it '_meta' and it's on
> every table I want to manage.
> 2) At a minimum the field has:
> {created"; {"name: "", "date": ""},
> "modified"; {"name": "", "date": ""}}
Thanks,
Robert
===
Robert Broussard
Houston, TX
===
**
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**
Re: Audit File Updates via Triggers...
Robert,
My thoughts on the created by/modified by stuff:
1) I moved all this to a single object field. I call it '_meta' and it's on
every table I want to manage.
2) At a minimum the field has:
{"created"; {"name: "", "date": ""},
"modified"; {"name": "", "date": ""}}
3) I wrote a few (I think it's 5) methods to manage it. Mainly:
META_UPDATE(ptr to _meta field) // generally in table trigger code
// figures out the table and trigger event then update _meta
Meta_get_text(object) -> returns a pretty text string of "Created: ...
Modified: ... "
Once I started using this I liked it so much I've converted most of my
projects to it. Having a single method to return the create/mod info string
is a delight. This field is also good for other types of meta data.
Calculation dates are one I use. Could include things like last printed or
whatever. Totally optional.
Keep in mind trigger code always runs on the server regardless of where the
record mod takes place. So client side IP vars are meaningless in a
trigger. Current user is the fastest reliable way to know who the user is.
Avoid trigger operations that do lookups or long verification procedures.
Do that stuff prior to saving if you need it. Trigger code is best when
it's lean and fast.
On Tue, Nov 20, 2018 at 11:59 AM Robert ListMail via 4D_Tech <
4d_tech@lists.4d.com> wrote:
> At a minimum, I just have the simple need of recording “CreatedBy” and
> “ModifiedBy” values (username or ID) for one particular table. I do have
> extra fields already in that table that could be repurposed and I would
> probably add “DateModified” to that. However, that’s three fields used just
> for auditing. When I look back at previous posts I see that some of you are
> tracking (saving audit data for) for tables and even individual fields. So,
> as I have a chance to redesign this DB, I wanted to re-think how this is
> implemented.
>
>
> So, Chip, when you have the need to store the username (or ID) of the user
> that created and/or last modified a key record, do you ever have this data
> in the field of the original record? I’m sure I have seen this more
> commonly where the data is in the original record. I guess to keep things
> normalized, you would use a related table (or a standalone table) where
> each transaction that you want to remember is stored. However, with the
> separate table method I suppose you would have to search this audit table
> every time your detail form is loaded (assuming you want to show who
> created or modified a record) and that each time the record is updated the
> audit table would be getting a new record.
>
> Thoughts about your data audit strategy are appreciated.
>
> Thanks,
>
> Robert
>
> ===
> Robert Broussard
> Houston, TX
> ===
>
> > On Aug 26, 2015, at 9:26 AM, Chip Scheide <4d_o...@pghrepository.org>
> wrote, Re: Triggers and error handling:
> >
> > so my triggers tend to look like this:
> > case of
> > (Database event = : (Database event=On Saving New Record Event)
> > trk_Last_Modified (->[Account_Postings]Created_Who)
> > trk_Last_Modified (->[Account_Postings]Modified_Who)
>
> **
> 4D Internet Users Group (4D iNUG)
> Archive: http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
> **
--
Kirk Brooks
San Francisco, CA
===
*We go vote - they go home*
**
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**
Re: Audit File Updates via Triggers...
I use 2 fields (nearly) every table, some linking (many to many) tables do not have this. [Table]Created_Who [text] [Table]Modifed_Who [text] both fields track the same info Current User, Date, time (in that order and that format) This info is updated, most often by a trigger, using the Database Events. I also have a separate table for specific action(s) I want to track (ex: deleting a record, user created some specific type of record, user logged in etc). In this table I track basically the same information as above (in a different manner) as well as what the action was. For Record deletions, I track the table, and original record ID and I convert all the data of the record to text (I do/can/will/would lose blob and pict data, however, not an issue at the moment) and save the text of the deleted record data in the record with the tracked action. For the tracked actions I use another table, to hold the actions I want to track. On Tue, 20 Nov 2018 13:59:36 -0600, Robert ListMail via 4D_Tech wrote: > At a minimum, I just have the simple need of recording “CreatedBy” > and “ModifiedBy” values (username or ID) for one particular table. > I do have extra fields already in that table that could be repurposed > and I would probably add “DateModified” to that. However, that’s > three fields used just for auditing. When I look back at previous > posts I see that some of you are tracking (saving audit data for) for > tables and even individual fields. So, as I have a chance to redesign > this DB, I wanted to re-think how this is implemented. > > > So, Chip, when you have the need to store the username (or ID) of the > user that created and/or last modified a key record, do you ever have > this data in the field of the original record? I’m sure I have seen > this more commonly where the data is in the original record. I guess > to keep things normalized, you would use a related table (or a > standalone table) where each transaction that you want to remember is > stored. However, with the separate table method I suppose you would > have to search this audit table every time your detail form is loaded > (assuming you want to show who created or modified a record) and that > each time the record is updated the audit table would be getting a > new record. > > Thoughts about your data audit strategy are appreciated. > > Thanks, > > Robert > > === > Robert Broussard > Houston, TX > === > >> On Aug 26, 2015, at 9:26 AM, Chip Scheide >> <4d_o...@pghrepository.org> wrote, Re: Triggers and error handling: >> >> so my triggers tend to look like this: >> case of >> (Database event = : (Database event=On Saving New Record Event) >> trk_Last_Modified (->[Account_Postings]Created_Who) >> trk_Last_Modified (->[Account_Postings]Modified_Who) > > ** > 4D Internet Users Group (4D iNUG) > Archive: http://lists.4d.com/archives.html > Options: https://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > ** --- Gas is for washing parts Alcohol is for drinkin' Nitromethane is for racing ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Audit File Updates via Triggers...
Hi Chuck, I think in this case, this makes sense. I never thought about tracking all record updates or going further… all updates on chosen fields. I recall in the past that other developers that cared about such things had a 3-4 fields added to each record and could easily see who created and who updated and when…. When I looked at some of the previous (more recent) NUG messages I see the where some people needed the granularity of field level or they need to know about every update (not just creation/modified). Thanks for chiming in. 4D is an amazing set of tools but sometimes you want to consider design ideas that have been successful in other projects. Thanks, Robert === Robert Broussard Houston, TX === > On Nov 20, 2018, at 2:12 PM, Charles Miller via 4D_Tech > <4d_tech@lists.4d.com> wrote: > > I think the strategy depends upon need. If all you need to to know > who created, data and time created and last modified by, last modified date > and time, then I would add fields to table in question. ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Audit File Updates via Triggers...
I think the strategy depends upon need. If all you need to to know who created, data and time created and last modified by, last modified date and time, then I would add fields to table in question. If you have a need to track who made every modification, I would put that in a sperate table. You could even track it to field level. If you are in a sesitive area (Hospital, secure setting), you might also need to track who viewed and when. Depending upon number of records, I might create a seperate table for each one to be tracked or place it in one table with table name in the the definition. You also will have to identify if you are tracking deletes and what happens if I add a record, but cancel and never save it. Hope this helps Regards, Chuck On Tue, Nov 20, 2018 at 2:59 PM Robert ListMail via 4D_Tech < 4d_tech@lists.4d.com> wrote: > At a minimum, I just have the simple need of recording “CreatedBy” and > “ModifiedBy” values (username or ID) for one particular table. I do have > extra fields already in that table that could be repurposed and I would > probably add “DateModified” to that. However, that’s three fields used just > for auditing. When I look back at previous posts I see that some of you are > tracking (saving audit data for) for tables and even individual fields. So, > as I have a chance to redesign this DB, I wanted to re-think how this is > implemented. > > > So, Chip, when you have the need to store the username (or ID) of the user > that created and/or last modified a key record, do you ever have this data > in the field of the original record? I’m sure I have seen this more > commonly where the data is in the original record. I guess to keep things > normalized, you would use a related table (or a standalone table) where > each transaction that you want to remember is stored. However, with the > separate table method I suppose you would have to search this audit table > every time your detail form is loaded (assuming you want to show who > created or modified a record) and that each time the record is updated the > audit table would be getting a new record. > > Thoughts about your data audit strategy are appreciated. > > Thanks, > > Robert > > === > Robert Broussard > Houston, TX > === > > > On Aug 26, 2015, at 9:26 AM, Chip Scheide <4d_o...@pghrepository.org> > wrote, Re: Triggers and error handling: > > > > so my triggers tend to look like this: > > case of > > (Database event = : (Database event=On Saving New Record Event) > > trk_Last_Modified (->[Account_Postings]Created_Who) > > trk_Last_Modified (->[Account_Postings]Modified_Who) > > ** > 4D Internet Users Group (4D iNUG) > Archive: http://lists.4d.com/archives.html > Options: https://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > ** -- - Chuck Miller Voice: (617) 739-0306 Fax: (617) 232-1064 Informed Solutions, Inc. Brookline, MA 02446 USA Registered 4D Developer Providers of 4D, Sybase & SQL Server connectivity http://www.informed-solutions.com - This message and any attached documents contain information which may be confidential, subject to privilege or exempt from disclosure under applicable law. These materials are intended only for the use of the intended recipient. If you are not the intended recipient of this transmission, you are hereby notified that any distribution, disclosure, printing, copying, storage, modification or the taking of any action in reliance upon this transmission is strictly prohibited. Delivery of this message to any person other than the intended recipient shall not compromise or waive such confidentiality, privilege or exemption from disclosure as to this communication. ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Audit File Updates via Triggers...
At a minimum, I just have the simple need of recording “CreatedBy” and “ModifiedBy” values (username or ID) for one particular table. I do have extra fields already in that table that could be repurposed and I would probably add “DateModified” to that. However, that’s three fields used just for auditing. When I look back at previous posts I see that some of you are tracking (saving audit data for) for tables and even individual fields. So, as I have a chance to redesign this DB, I wanted to re-think how this is implemented. So, Chip, when you have the need to store the username (or ID) of the user that created and/or last modified a key record, do you ever have this data in the field of the original record? I’m sure I have seen this more commonly where the data is in the original record. I guess to keep things normalized, you would use a related table (or a standalone table) where each transaction that you want to remember is stored. However, with the separate table method I suppose you would have to search this audit table every time your detail form is loaded (assuming you want to show who created or modified a record) and that each time the record is updated the audit table would be getting a new record. Thoughts about your data audit strategy are appreciated. Thanks, Robert === Robert Broussard Houston, TX === > On Aug 26, 2015, at 9:26 AM, Chip Scheide <4d_o...@pghrepository.org> wrote, > Re: Triggers and error handling: > > so my triggers tend to look like this: > case of > (Database event = : (Database event=On Saving New Record Event) > trk_Last_Modified (->[Account_Postings]Created_Who) > trk_Last_Modified (->[Account_Postings]Modified_Who) ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
