Creating a Update delete insert trigger with sequence number
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.