i'm going through an old past paper on Database server management for my exam tomorrow and can't for the life of me answer it. Could anyone give me concise answer or at least point me in the right direction? Any help is greatly appreciated!
b) Audit data is often captured by the use of database triggers.
(i) What would happen when the trigger is fired?
(ii) Discuss how this can affect normal database operations.
Command (as user SYS):
SQL> CREATE OR REPLACE TRIGGER audit-table-DEPT AFTER
2 INSERT OR UPDATE OR DELETE ON DEPT FOR EACH ROW
4 audit_data DEPT$audit%ROWTYPE;
6 if inserting then audit_data.change_type := 'I';
7 elsif updating then audit_data.change_type :='U';
8 else audit_data.change_type := 'D';
9 end if;
11 audit_data.changed_by := user;
12 audit_data.changed_time := sysdate;
14 case audit_data.change_type
15 when 'I' then
16 audit_data.DEPTNO := :new.DEPTNO;
17 audit_data.DNAME := :new.DNAME;
18 audit_data.LOC := :new.LOC;
20 audit_data.DEPTNO := :old.DEPTNO;
21 audit_data.DNAME := :old.DNAME;
22 audit_data.LOC := :old.LOC;
23 end case;
25 insert into DEPT$audit values audit_data;
The trigger is inserting a new row into an audit table every time something is inserted into, deleted from, or updated in the
line 4 declares a new variable
audit_data, which is a
%ROWTYPE variable, meaning it will mimic a row in a database table, rather than holding just a single value the way an
lines 6-9 set the value of
change_type column. If the trigger was called because we're inserting a row into
change_type is set to the value
I. If we're updating
change_type is set to
U. Otherwise, we're deleting from
change_type is set to
Lines 11 & 12 set two more columns in
changed_by field is set equal to the user who is performing this insert/update/delete, and
change_time is set equal to the current time.
Lines 14-23 then set the final three fields of
LOC. These fields have corresponding fields in the original
DATA table. If we are inserting a new row into
DATA (we've previously set
change_type to I), then the values of our new
audit_data fields will be the new values inserted into
DATA. If we're either updating or deleting rows in
DATA, we set the values of the new
audit_data fields to instead be the old pre-update/pre-delete values from
Finally, Line 25 performs the actual insert into the audit table, inserting a row with all of the
audit_data values we just got finished setting.