This content has been marked as final. Show 4 replies
just use database triggers, set and forget.
CREATE OR REPLACE TRIGGER TABLENAME_AUD BEFORE INSERT OR UPDATE ON TABLENAME FOR EACH ROW BEGIN IF inserting THEN :new.created_by := nvl(apex_custom_auth.get_user,user); :new.created_date := sysdate; END IF; IF updating THEN :new.last_updated_by := nvl(apex_custom_auth.get_user,user); :new.last_updated_date := sysdate; END IF; END;
Thanks Andre. You're solution is pretty sweet but would mean I have to make changes to each table.
I think it is working though as I had not cancelled the previous button function that the form created.
Once I removed it the procedure seems to be running although not doing anything
Trying now to figure out the debugging side of things.
Its true that you have to put it on every table that you want to audit, but once its there, its there for what ever API accesses the table. The code is very basic and if you keep your auditing column names consistent, then the only code changes required when you create the triggers for different tables is the trigger name, usually tablename_aud, and the actual table name. On top of that, its the method any experienced DBA will tell you is the way to go.
Doing it at the application level means that you have to call the function for every page that accesses a table and every other API that accesses the data, such as sqlplus, sql developer, toad, sql loader etc.
Also, note that a function to get the current user, either the Apex user if in an Apex session or the database user if outside an Apex session is simply.