ClientServer latest version.

I have over 50 tables in a recent application under development.  Every table includes four fields: CreatedBy, CreatedOn, ModifiedBy, ModifiedOn  [string and timestamp]

I need to record the User and the timestamp when the records are created and subsequently modified.
The CreatedOn timestamp is easy with a default CURRENT_TIMESTAMP().  The CreatedBy and ModifiedBy fields are the current username (not the ElevateDB User but rather the value of a global string which is established after the "user" logs into the application).

Rather than include the remaining three fields in every query and table edit/insert/post operation (obviously hundreds of queries), I was thinking that a single trigger mechanism would work to update the CreatedBy, ModifiedBy and ModifiedOn fields.  Since every table requires the same trigger, is there any mechanism to have a common trigger for every table?

I would welcome any other suggestions on how best to maintain these three fields?

>>Since every table requires the same trigger, is there any mechanism to have a common trigger for every table?
>>I would welcome any other suggestions on how best to maintain these three fields?

--

I tend to manage these types of values from the Delphi application, as it gives more fine-grained control of variables such as the UserID.

--
Triggers are also a really good way to do it, and make the database relationships application independent ... which is a plus if you are also using ODBC for data-access.

Others may know more, but here is how I would do it.

Write the trigger once, then write a script to apply it to every table. I do not think that EDB has session level or database level triggers, I believe all triggers are applied to 1 table.

If you use the Information.TableColumns table to retrieve all tables with Columns for EditedBy, you can then call create trigger for each one passing in the table-name variables.

SCRIPT
BEGIN
 DECLARE Crsr Cursor FOR Stmt;
 DECLARE TableName VARCHAR;

PREPARE Stmt FROM
' SELECT TableName FROM Information.Tables WHERE Name LIKE ''EditedBy'' ';
OPEN Crsr;
FETCH FIRST FROM Crsr('Name') INTO TableName;
WHILE NOT EOF(Crsr) DO
  EXECUTE IMMEDIATE
  ' CREATE TRIGGER AFTER EDIT ON '+TableName+
  --add details of the rest of the trigger in here.
  FETCH NEXT FROM Crsr('Name') INTO TableName;
  END WHILE;
CLOSE Crsr;

END

Norman,

<< I have over 50 tables in a recent application under development.  Every
table includes four fields: CreatedBy, CreatedOn, ModifiedBy, ModifiedOn
[string and timestamp]

I need to record the User and the timestamp when the records are created and
subsequently modified.
The CreatedOn timestamp is easy with a default CURRENT_TIMESTAMP().  The
CreatedBy and ModifiedBy fields are the current username (not the ElevateDB
User but rather the value of a global string which is established after the
"user" logs into the application).

Rather than include the remaining three fields in every query and table
edit/insert/post operation (obviously hundreds of queries), I was thinking
that a single trigger mechanism would work to update the CreatedBy,
ModifiedBy and ModifiedOn fields.  Since every table requires the same
trigger, is there any mechanism to have a common trigger for every table? >>

You can have a trigger that is fired for every operation (insert, update,
and delete), and you can use the OPERATION() function to find out which
operation is taking place from within the trigger, but you can't use the
exact same trigger instance for multiple tables since a trigger is tied to
the table.  So, ultimately, you have to make a copy of the trigger for every
table.  But, it is very easy to replicate the same trigger among multiple
tables using a single script that loops through the Information.Tables
system table and does an CREATE/ALTER TRIGGER on each table.  If you want to
see how it's done, just let me know and I'll work something up for you.

Tim Young
Elevate Software
www.elevatesoft.com