CODEWITHSUNDEEP
oracle-databaseplsql
raja
raja

Reputation: 189

two triggers on one table

I am very new to oracle database my office is using oracle 10g. My question is I have two tables one is current_cases having columns as case_id, col1, col2 col3..... another table backup_cases have backup_id, case_id, col1,col2,col3... where case_id of current_cases is the same as case_id of backup_cases

I would like to create a trigger before update current_cases to insert all row the data into backup_cases, but there is already one more trigger on backup_cases to insert backup_sequence next value. Then how to create the update trigger, will the nextval trigger on backup_cases will automatically fill or should I over ride and take the sequence.next val an insert into the backup_cases. please give some idea about this small problem.....

Upvotes: 0

Views: 572

Answers (2)

TommCatt
TommCatt

Reputation: 5636

It doesn't look like you have anything to worry about. I assume there is an insert trigger on the backup table to generate the backup ID. I also assume there is an insert trigger on the current table to insert the incoming row also to the backup table. It may also be generating the current ID.

If you add an update trigger on the current table, it can write the NEW row to the backup table and everything should work normally. You don't need to make any changes to any existing trigger on either table.

If you have any doubts, this is a very easy operation to test.

Upvotes: 0

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

...will the nextval trigger on backup_cases will automatically fill?

Trigger on backup_cases will work, but you must explicitly list all the inserted values, not this way: insert ... select * ....

Test: (everything is simplified, no primary keys, indices, foreign keys, constraints, just to address your question in short, readable way):

-- tables creation
create table current_cases (case_id number, col1 varchar2(20), 
  col2 varchar2(20));
create table backup_cases (backup_id number, case_id number, col1 varchar2(20), 
  col2 varchar2(20));

-- sequences creation
create sequence cc_seq;
create sequence bc_seq;

-- triggers
create or replace trigger bc_trg before insert on backup_cases
for each row
begin
  select bc_seq.nextval into :new.backup_id from dual;
end;

create or replace trigger cc_trg before insert or update on current_cases
for each row
begin
  if inserting then 
    select cc_seq.nextval into :new.case_id from dual;
  else
    insert into backup_cases (case_id, col1, col2)
      values (:old.case_id, :old.col1, :old.col2);
  end if;
end;

-- inserts and update sample data
insert into current_cases (col1, col2) values ('a1', 'a1');
insert into current_cases (col1, col2) values ('b1', 'b1');
insert into current_cases (col1, col2) values ('c1', 'c1');
update current_cases set col1 = 'b2a', col2='b2b' where case_id=2;

Results:

select * from current_cases;

   CASE_ID COL1                 COL2               
---------- -------------------- --------------------
         1 a1                   a1                   
         2 b2a                  b2b                  
         3 c1                   c1                   

select * from backup_cases;

 BACKUP_ID    CASE_ID COL1                 COL2               
---------- ---------- -------------------- --------------------
         1          2 b1                   b1

Upvotes: 1

Related Questions