CODEWITHSUNDEEP
oracle-databasetriggersoracle19caudit-tables
Tharindu Chathuranga
Tharindu Chathuranga

Reputation: 25

Audit-table-DEPT Trigger

What would happen when this database trigger is fired?

Command (as user SYS):

SQL> CREATE OR REPLACE TRIGGER audit-table-DEPT AFTER
INSERT OR UPDATE OR DELETE ON DEPT FOR EACH ROW
declare
audit_data DEPT$audit%ROWTYPE;
begin
if inserting then audit_data.change_type := 'I';
elsif updating then audit_data.change_type :='U';
else audit_data.change_type := 'D';
end if;

audit_data.changed_by := user;
audit_data.changed_time := sysdate;
case audit_data.change_type
when 'I' then
audit_data.DEPTNO := :new.DEPTNO;
audit_data.DNAME := :new.DNAME;
audit_data.LOC := :new.LOC;
else
audit_data.DEPTNO := :old.DEPTNO;
audit_data.DNAME := :old.DNAME;
audit_data.LOC := :old.LOC;
end case;
insert into DEPT$audit values audit_data;
end;
/

how this can affect normal database operations?

Upvotes: 0

Views: 128

Answers (1)

APC
APC

Reputation: 146199

What will happen if you run this command? Nothing, The trigger won't compile as you have given it an invalid object name. (Replace those dashes with underscores.)

After that, you have a trigger which inserts an audit record for DML activity on the DEPT table. When inserting you get an AUDIT_DEPT record with the values of the inserted DEPT record. When deleting you get an AUDIT_DEPT record with the values of the deleted DEPT record. When updating you get a somewhat useless AUDIT_DEPT record which tells you a DEPT record was updated but doesn't identify which one.

how this can affect normal database operations?

It won't cause anything to fail. However, you are executing additional insert statements every time you execute DML on the DEPT table. You probably won't notice the impact on single-row statements, but you might notice a slower response time if you insert, update or delete a large number of DEPT records. You will need to bench mark it.

One last observation:

Command (as user SYS):

Uh oh.

The better interpretation of this statement is that the trigger won't compile, because the SYS schema doesn't have a DEPT table. Connect as the user which owns the DEPT table, then run the CREATE TRIGGER statement.

The worrying option is that the trigger compiles because you have put a DEPT table in its schema. This is bad practice. The SYS schema is maintained by Oracle to run the database internal software. Changing the SYS schema unless authorised by Oracle could corrupt your database and invalidate any support contract you have. What you should do is use SYS (or SYSTEM) to create a user to host your application's objects, then connect as that user to build tables, triggers and whatever else you need.

Upvotes: 4

Related Questions