| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Triggers
You can find examples in your Oracle7 Server Application Developper's Guide
Chapter Using Database Triggers Section Examples of Trigger Applications
(pages 9-21 to 9-36).
For instance, there is an example Auditing with Triggers:
Auditing with Triggers
Triggers are commonly used to supplement the built-in auditing features of
Oracle. Although triggers can be written to record information similar to that
recorded by the AUDIT command, triggers should be used only when more detailed
audit information is required. For example, use triggers to provide value-based
auditing on a per-row basis for tables.
Sometimes, the Oracle AUDIT command is considered a security audit facility,
while triggers can provide financial audit facility.
When deciding whether to create a trigger to audit database activity, consider what Oracle's auditing features provide, compared to auditing defined by triggers.
DML as well as DDL auditing
Standard auditing options permit auditing of DML and DDL statements regarding all types of schema objects and structures. Comparatively, triggers only permit auditing of DML statements issued against tables.
Centralized audit trail
All database audit information is recorded centrally and automatically using the auditing features of Oracle.
Declarative method
Auditing features enabled using the standard Oracle features are easier to declare and maintain, and less prone to errors when compared to auditing functions defined by triggers.
Auditing options can be audited
Any changes to existing auditing options can also be audited to guard against malicious database activity.
Session and execution time auditing
Using the database auditing features, records can be generated once every time an audited statement is issued (BY ACCESS) or once for every session that issues an audited statement (BY SESSION). Triggers cannot audit by session; an audit record is generated each time a trigger-audited table is referenced.
Auditing of unsuccessful data access
Database auditing can be set to audit when unsuccessful data access occurs. However, any audit information generated by a trigger is rolled back if the triggering statement is rolled back.
Sessions can be audited
Connections and disconnections, as well as session activity (physical I/Os, logical I/Os, deadlocks, etc.), can be recorded using standard database auditing.
When using triggers to provide sophisticated auditing, AFTER triggers are normally used. By using AFTER triggers, auditing information is recorded after the triggering statement is subjected to any applicable integrity constraints, preventing cases where the audit processing is carried out unnecessarily for statements that generate exceptions to integrity constraints.
When to use AFTER row vs. AFTER statement triggers depends on the information being audited. For example, row triggers provide value-based auditing on a per-row basis for tables. Triggers can also require the user to supply a "reason code" for issuing the audited SQL statement, which can be useful in both row and statement-level auditing situations.
The following example demonstrates a trigger that audits modifications to the EMP table on a per-row basis. It requires that a "reason code" be stored in a global package variable before the update.
Example
This trigger demonstrates
how triggers can be used to provide value-based auditing
how to use public package variables
Comments within the code explain the functionality of the trigger.
CREATE TRIGGER audit_employee
AFTER INSERT OR DELETE OR UPDATE ON emp
FOR EACH ROW
BEGIN
/* AUDITPACKAGE is a package with a public package
variable REASON. REASON could be set by the application by a command such as EXECUTE AUDITPACKAGE.SET_REASON(reason_string). Note that a package variable has state for the duration of a session and that each session has a separate copy of all package variables. */
IF auditpackage.reason IS NULL THEN
raise_application_error(-20201, 'Must specify reason'
|| ' with AUDITPACKAGE.SET_REASON(reason_string)'); END IF; /* If the above conditional evaluates to TRUE, the
user-specified error number and message is raised, the trigger stops execution, and the effects of the triggering statement are rolled back. Otherwise, a new row is inserted into the predefined auditing table named AUDIT_EMPLOYEE containing the existing and new values of the EMP table and the reason code defined by the REASON variable of AUDITPACKAGE. Note that the "old" values are NULL if triggering statement is an INSERT and the "new" values are NULL if the triggering statement is a DELETE. */
INSERT INTO audit_employee VALUES
(:old.ssn, :old.name, :old.job_classification, :old.sal,
:new.ssn, :new.name, :new.job_classification, :new.sal,
auditpackage.reason, user, sysdate );
END;
Optionally, you can also set the reason code back to NULL if you wanted to force
the reason code to be set for every update. The following simple AFTER statement
trigger sets the reason code back to NULL after the triggering statement is
executed:
CREATE TRIGGER audit_employee_reset
AFTER INSERT OR DELETE OR UPDATE ON emp
BEGIN
auditpackage.set_reason(NULL);
END;
Notice that the previous two triggers are both fired by the same type of SQL
statement. However, the AFTER row trigger is fired once for each row of the
table affected by the triggering statement, while the AFTER statement trigger is
fired only once after the triggering statement execution is completed.
Another example of using triggers to do auditing is shown below. This trigger tracks changes being made to the EMP table, and stores this information in AUDIT_TABLE and AUDIT_TABLE_VALUES.
CREATE OR REPLACE TRIGGER audit_emp
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
DECLARE
time_now DATE;
terminal CHAR(10);
ELSIF UPDATING ('DEPTNO') THEN
INSERT INTO audit_table_values
VALUES (audit_seq.CURRVAL, 'DEPTNO',
:old.deptno, :new.deptno);
END IF;
END IF;
William F. O'Neill a écrit dans le message <37C87FCF.298504D2_at_mindspring.com>...
>Can someone supply me with an example of a Trigger as used on an Oracle >database, eg. a trigger that would update an audit table, depending on >whether the action on a table was Update, Delete, or Insert. To clarify, >if I edit Table_A, I want the trigger to activate, bring up the Audit >table, and indicate in an Action field that Table_A was just Updated, >and that a particular field's value changed ->new value, and what the >old value was. If you have any code that is in anyway similar to my >requirements, I'd appreciate it. thank you. >Bill.... >Received on Mon Aug 30 1999 - 02:47:17 CDT
 |
 |