Skip to Main Content

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!

Creating a Update delete insert trigger with sequence number

zephyr223Feb 27 2009 — edited Feb 27 2009
Hey all,
I have two tables. tbl_main, tbl_temp. Whenever a record is inserted, deleted, updated in tbl_main, i need to insert those records in tbl_temp. All the field/columns names are exactly the same, except that in tbl_temp i now have a unique column using a sequence number, a column to identify if the record was Updated, deleted, modified(hence the codes) and date when record was added to the table.
So far this is what i have:

create or replace
trigger audit_trg
after update or insert or delete ON tbl_main
for each row
begin
if updating then
insert into tbl_temp (seq_id, idx, ctl, action_taken, date_added)
VALUES
(temp_seq.nextval,idx, mke, ctl, 'U', sysdate);
ELSif INSERTING then
insert into tbl_temp (seq_id, idx, ctl, action_taken, date_added)
VALUES (temp_seq.nextval,idx, mke, ctl, 'N', sysdate);
ELSIF deleting then
insert into tbl_temp (seq_id, idx, ctl, action_taken, date_added)
VALUES (temp_seq.nextval,idx, mke, ctl, 'D', sysdate);
END IF;
END audit_trg;

i have tried numerous combinations, but i get many complilation errors. Does this code look right or am i way of track?? Thanks in advance.
This post has been answered by JustinCave on Feb 27 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 27 2009
Added on Feb 27 2009
4 comments
1,081 views