Audit
Audit Tweet |
|
|
Categories:
Process Type Plugin
Journalizes all changes of a form in a journal tabAudit Journalizes all changes of a form in a journal table. About the plug/in: The audit process Plug/In is a process type Plug/in that can be used in an APEX form. It extends the functionality auf the dml-process. When the plug/in is started at the first time, it generates an audit table. After that, all changes of the source data will be journalized in this table. How to use the plug/in 1. Install the Plug/in. 2. Open the Form-Site in the application builder 3. Create a new process. 4. Choose the processtype: plug/ins 5. Select the Audit-Process plug/in. 6. Give it a name. 7. Set the properties: Tabelle (table): <database source table>, ID_Item: <item name> (e.g. P10_ID), ID-Spalte (Id-Column): <the primary key column> (e.g. ID ) 8. Create the plug/in More … I’ve developed this and some more Plug/Ins as examples for my book “Oracle Application Express in der Praxis”. (ISBN: 978-3-446-43896-5, http://www.amazon.de/gp/product/3446438963 ). Have a look at this book for more information an the technical background. License: Use the Audit Plug/In under the MIT license. The MIT License (MIT) Copyright (c) 2013 Ralf Beckmann (Eslohe, Germany) Permission is hereby granted, free of charge, to any person obtaining a copy The above copyright notice and this permission notice shall be included in THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR Keywords
audit process type history trace apex mit |
Company:
Date added: 6.11.2013
Views:
4032
Votes:
3
Reviews:
4
Min. APEX Version:
4.2
Link to:
Download
If something is not correct please report it here:
Report Listing
|
Share on Digg
Save This Page|
Do you have a question about this Plugin? Want to write a Review or Comment?
Login first. Reviews / Questions / Comments are e-mailed to the author of the Plug-in. |
I am glad that the plug/in further helps you.
I looked in my source code. You must change the following code pieces.
PROCEDURE create_hist_table
[...]
l_sql := 'create table ' || p_table || '_H as SELECT sysdate hist_timestamp, ' || '''' || 'INITIAL' || '''' || ' as state ,'
|| '''' || ' ' || '''' || ' as user_id , x.* FROM ' || p_table || ' x';
[...]
This procedure create the Hist-Table by the first call.
PROCEDURE fill_hist_table
[...]
IF l_cnt_del = 0 THEN
l_state := 'DEL';
l_sql := 'INSERT INTO ' || p_table || '_H (HIST_TIMESTAMP, STATE, ' || p_id_column || ') VALUES ( sysdate,' || '''' || l_state || '''' ||', ' || v(p_id_item) || ' ,'
|| '''' || v(APP_USER) || '''' ||')';
ELSE
APEX_DEBUG.LOG_MESSAGE( p_message => 'Plug-In Audit ( fill_hist_table) : p_id_item ' || v(p_id_item), p_level => 1);
-- Wurde ein Datensatz geändert ?
l_state := 'CHANGED';
l_sql := '
INSERT INTO ' || p_table || '_H SELECT sysdate,
' || '''' || l_state || '''' ||','
|| '''' || v(APP_USER) || '''' ||',
x.*
FROM ' || p_table || ' x
WHERE ' || p_id_column || ' = ' || v(p_id_item);
END IF;
APEX_DEBUG.LOG_MESSAGE( p_message => 'Plug-In Audit ( fill_hist_table) : ' || l_sql, p_level => 1);
EXECUTE immediate l_sql;
END IF;
[...]
The second procedure inserts the data into the hist-table, if the source has been changed.
I hope, that the informations help.
Ralf
I have just been using your plug in and its exactly what I need apart from one little item I need the user id to be added to the history table as well as the date and state
I though I would kindly ask you to point me in the right direction before I go through the sql and try and work it out myself
I know that there is a fault with the apex_user as it currently adds exactly " apex_user" that to the journal table instead of the correct user name
Keep up the good work
Eleanor Porteous
no that's not correct.
First the plug/in creates a hist-table.
create table
After this "install"-Step the plug/in executes the procedure fill_hist_table.
If you delete a row in the source table, the plug/in inserts a copy of this row in the hist-table and sets the STATE to 'DEL".
Otherwise the plug/in set the STATE to 'CHANGED' and inserts the row to the hist table.
Here is the corresponding code snippet ...
[..]
l_sql := 'select count(*) from ' || p_table || ' x WHERE ' || p_id_column || ' = ' || v(p_id_item);
EXECUTE immediate l_sql INTO l_cnt_del;
IF l_cnt_del = 0 THEN
l_state := 'DEL';
l_sql := 'INSERT INTO ' || p_table || '_H (HIST_TIMESTAMP, STATE, ' || p_id_column || ') VALUES ( sysdate,' || '''' || l_state || '''' ||', ' || v(p_id_item) || ')';
ELSE
APEX_DEBUG.LOG_MESSAGE( p_message => 'Plug-In Audit ( fill_hist_table) : p_id_item ' || v(p_id_item), p_level => 1);
-- Wurde ein Datensatz geändert ?
l_state := 'CHANGED';
l_sql := '
INSERT INTO ' || p_table || '_H SELECT sysdate,
' || '''' || l_state || '''' ||',
x.*
FROM ' || p_table || ' x
WHERE ' || p_id_column || ' = ' || v(p_id_item);
END IF;
APEX_DEBUG.LOG_MESSAGE( p_message => 'Plug-In Audit ( fill_hist_table) : ' || l_sql, p_level => 1);
EXECUTE immediate l_sql;
[..]
Ralf
Efren.
| Powered by Sigsiu.NET |  |