Skip to Main Content

SQL & PL/SQL

Simple Trigger Issue?

JazzyBJan 2 2019 — edited Jan 3 2019

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!

This post has been answered by AndrewSayer on Jan 3 2019
Jump to Answer
Comments
Post Details
Added on Jan 2 2019
17 comments
272 views