I am attempting to create a trigger that will insert values into an audit table for approval by an admin user. The trigger will insert new values that are added into a consultant table into this audit table.
DROP TABLE MyAuditTable;
CREATE TABLE MyAuditTable (
audit_id INTEGER NOT NULL,
new_name VARCHAR2 (30),
new_postcode VARCHAR2 (20),
status VARCHAR2 (15),
CONSTRAINT pk_MyAuditTable PRIMARY KEY ( audit_id )
);
drop trigger MyTrigger;
create trigger MyTrigger
after insert on my_consultant_table
for each row
begin
insert into MyAuditTable values (
MySeq.nextval, :new.con_name,
:new.con_postcode,
'Pending')
end;
/
The trigger has no errors but does not insert data into my audit table.
Ideally looking to create a second trigger so that once 'pending' has been changed to something along the lines of 'authorised' the changes are made.
Can anyone provide insight as to how I can overcome this so that the data is correctly inserted into my audit table and not instantly updated into the consultant table?
Many Thanks!