![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 3 of 3 total |
![]() |
Mon, Oct 28 2013 3:31 PM | Permanent Link |
Norman Clark Clark-Tech Inc. | 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? |
Mon, Oct 28 2013 6:25 PM | Permanent Link |
Adam Brett Orixa Systems | >>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 |
Tue, Nov 5 2013 3:45 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
This web page was last updated on Wednesday, August 14, 2024 at 03:12 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |