Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

21205
Answer
Welcome to the forum!

You can't pass the complete row
you can pass individual column values, so it would be something like 
create or replace trigger logTrigger after update on test_table for each row
begin
   logProcedure(:old.col1, :new.col1);
end;
Marked as Answer by herzal · Sep 27 2020
21205
or something like this 
create or replace trigger logTrigger after update on test_table for each row
declare
   old_row test_table%rowtype;
   new_row test_table%rowtype;
begin
   old_row.col1 := :old.col1;
   ....
   new_row.col1 := :new.col1;

   logProcedure(old_row, new_row);
end;
herzal
wow, that was quick o_O

thanks for your reply.

I'll explain my problem: we have more than 300 tables and every insert/update/delete should be logged, so I need more than 300 triggers. I want to avoid that the trigger has to be rewritten every time the structure of the table changes. So I thouht I use a trigger and a log-procedure. The only things the trigger does are checking if the log-procedure is up2date, recreating the log-procedure if necessary, and calling the log-procedure with :old and :new as parameters.

But since that doesn't work: any suggestions how to solve that?

thanks for your help =) (sitting here with my stop watch)
21205
Before writing your own implementation, check auditing.


sorry for the delay, but I had to run out do some groceries, feed the cat and debug some applications...
;)

Edited by: Alex Nuijten on Aug 13, 2009 1:03 PM
21205
maybe something like http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/cfgaudit.htm#sthref1769
herzal
thanks your your help, Alex =) but auditing is no option. I think I will end up using a DDL-trigger that recreates the log-triggers and then the log-triggers do the logging.
21205
doing it with DDL triggers might proof quite tricky.
It would be pretty straight forward to write your own generator to create these triggers. Or take a look at codegen, a utility to generate source code.
http://codegen.inside.quest.com/index.jspa
herzal
thanks, I will have a look ;)
1 - 8

Post Details

Added on Jan 2 2019
#sql, #trigger
17 comments
334 views