Database Reference
In-Depth Information
Our first step (after the
BEGIN
header) is to insert the current date in our management table. That will
prevent others from also trying to manage the error log table. We commit it, which is only allowed here
because we are an autonomous transaction.
The second step is to delete records in the error log that are over 45 days old. Notice that we do
some date arithmetic involving
SYSDATE
, in this case
SYSDATE - 45
, which is equivalent to 45 days ago.
We will use similar date arithmetic in our trigger. We also commit this deletion.
The last thing under the
BEGIN
header is to insert a “Success” message in the error log, and commit
it. Why not? That seems like a good place.
As in our other procedures we've seen so far, we are going to catch errors. In this case, we will insert
the error into our error log table (once again, why not? It'll be nice to have all our troubleshooting
messages in one place.) And commit it.
Creating a Trigger to Maintain the Error Log Table
The maintenance procedure we defined above will work every time you call it, and however you call it.
You could hire someone to manually run that procedure once a day. Oracle database has a scheduler
(the
DBMS_SCHEDULER PL/SQL
package or the older
DBMS_JOB PL/SQL
package) that you could alternatively
use to run it once a day.
Instead, we are going to make the table self-governing by adding a trigger. A trigger has a lot of
similarities to a procedure, so it is consistent with the syntax we've been discussing. Execute the code in
Listing 7-5 to create and enable a trigger on the
t_appsec_errors
table that will run after each row (log
entry) is inserted in the table.
Listing 7-5.
Insert Trigger on Error Log Table,
t_appsec_errors_iar
CREATE OR REPLACE TRIGGER appsec.t_appsec_errors_iar
AFTER INSERT
ON t_appsec_errors FOR EACH ROW
DECLARE
m_log_maint_dt DATE;
BEGIN
SELECT MAX( update_ts ) INTO m_log_maint_dt
FROM t_appsec_errors_maint;
-- Whenever T_APPSEC_ERRORS_MAINT is empty, M_LOG_MAINT_DT is null
IF( ( m_log_maint_dt IS NULL ) OR
( m_log_maint_dt < ( SYSDATE - 1 ) ) )
THEN
p_appsec_errors_janitor;
END IF;
END;
/
ALTER TRIGGER appsec.t_appsec_errors_iar ENABLE;
This trigger runs after each insert,
AFTER INSERT
; however, we only want our procedure to run once a
day. To accomplish this, we get the
MAX( update_ts )
of the last time our procedure was run from the
t_appsec_errors_maint
table, and store that date in
m_log_maint_dt
. (Note this example of
SELECT INTO
syntax—selecting a value into a variable.) Then we check to see if
m_log_maint_dt
is
NULL
(whenever
t_appsec_errors_maint
table is empty) or
m_log_maint_dt
is earlier than 24 hours ago (
< SYSDATE - 1
). If
it is, then we run our procedure,
p_appsec_errors_janitor
.
