user3357649 user3357649 - 6 months ago 11
SQL Question

SQL Trigger past paper

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.

(6 marks)

Command (as user SYS):
SQL> CREATE OR REPLACE TRIGGER audit-table-DEPT AFTER
2 INSERT OR UPDATE OR DELETE ON DEPT FOR EACH ROW
3 declare
4 audit_data DEPT$audit%ROWTYPE;
5 begin
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;
10
11 audit_data.changed_by := user;
12 audit_data.changed_time := sysdate;
13
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;
19 else
20 audit_data.DEPTNO := :old.DEPTNO;
21 audit_data.DNAME := :old.DNAME;
22 audit_data.LOC := :old.LOC;
23 end case;
24
25 insert into DEPT$audit values audit_data;
26 end;
27 /
Trigger created.

Answer

The trigger is inserting a new row into an audit table every time something is inserted into, deleted from, or updated in the DATA table.

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 INT or DOUBLE would.

lines 6-9 set the value of audit_data's change_type column. If the trigger was called because we're inserting a row into DATA, then change_type is set to the value I. If we're updating DATA, change_type is set to U. Otherwise, we're deleting from DATA, and change_type is set to D.

Lines 11 & 12 set two more columns in audit_data. The 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 audit_data: DEPTNO, DNAME, and 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 DATA.

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.

Comments