Re: Using Created_Date, Revised_Date [message #1282 is a reply to message #1281] |
Fri, 07 March 2008 04:54 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to previous message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
Firstly, no database fields are automatically added by the framework. The data dictionary 'import' facility will only ever import the details of fields (columns) which have been defined in the database schema.
Most of the tables that I create always have the following columns at the end:
`created_date` datetime NOT NULL default '2000-01-01 00:00:00',
`created_user` varchar(16) NOT NULL default 'UNKNOWN',
`revised_date` datetime default NULL,
`revised_user` varchar(16) default NULL,
I have been using these for several decades as a first level auditing facility, and the habit has stuck. It is also a useful way of being able to isolate recent inserts or updates with simple SQL queries.
After these columns have been imported into the data dictionary their details need to be updated as follows:
- for all four columns set NOEDIT and NOSEARCH.
- for 'created_date/user' set AUTO_INSERT.
- for 'revised_date/user' set AUTO_UPDATE.
You can either do this manually using the online screen, or you can run script 'radicore\dict\sql\update_created_date.sql'.
This means that the AUTO_INSERT columns will only be set by the framework on an INSERT, and the AUTO_UPDATE columns will only be set on an UPDATE. The values inserted will be as follows:
- string fields = $_SESSION['logon_user_id']
- date fields = current date
- time fields = current time
- datetime fields = current date+time
In addition none of these fields will be visible on INSERT or SEARCH screens, and will be display-only in all others.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|