Forum Stats

  • 3,875,396 Users
  • 2,266,910 Discussions
  • 7,912,192 Comments

Discussions

If I perform a DML operation in Table1 the data should reflect in table2

User_051LO
User_051LO Member Posts: 8 Green Ribbon
edited Nov 15, 2022 4:31AM in SQL & PL/SQL

Hi, Please help me with this proc. I need to do this in plsql. version Oracle 19c and the tool I use is TOAD.

Table 1: employee_1 with columns employee_id, employee_name, contact_number

primary key is employee_id

Table 2: emp_hist with columns emp_id, emp_name, phone_number

Thank You

Tagged:

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 42,580 Red Diamond
    edited Nov 11, 2022 10:58AM Answer ✓

    Well, I'm short on time myself... I do have a day job to do.

    Here's one way (not necessarily the best way, but then you fail to give us the details we need to know what would be best)...

    create trigger trgTable1Change before insert or update or delete on table1
    for each row
    begin
      if deleting then
        delete from table2 where emp_id =:old.employee_id;
      elsif inserting then
        insert into table2 (emp_id, emp_name, phone_number) values (:new.employee_id, :new.employee_name, :new.contact_number);
      elsif updating then
        update table2
          set emp_name = :new.employee_name
             ,phone_number = :new.contact_number
          where emp_id = :new.employee_id;
      end if;
    end;
    /
    
    


Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 42,580 Red Diamond

    In what way should it 'reflect'?

    If it's an exact copy, with just differently named columns, you would be better with a view...

    create view table2 as
      select employee_id as emp_id
            ,employee_name as emp_name
            ,contact_number as phone_number
      from   table1;
    

    Then, you will have an instant reflection of what is in table1, appearing as table2.

    There's little point in duplicating exactly the same data in a database. 😉

  • User_051LO
    User_051LO Member Posts: 8 Green Ribbon
    edited Nov 11, 2022 9:41AM

    @BluShadow Hi, Thanks for the response. But yes I need to duplicate. I I enter the data into table 1 then the data should e loaded in table 2 as well, similarly if i delete a value from table 1, it should be deleted in table2.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,580 Red Diamond

    Ok, but a view would give you that.

    What's the reason for actually duplicating the data?

    If really necessary, you could use a materialized view with fast refresh?

    Or, perhaps what I'd consider a last resort, use triggers on your table1 to capture the changes happening and apply the same to table2.

    There are of course other ways of replicating tables/databases... but it really depends what the overall requirement is.

    This sounds like a bit of an XYProblem

  • User_051LO
    User_051LO Member Posts: 8 Green Ribbon

    @BluShadow Its Just an individual task that I need to do. Could you please send the procedure? I have a very less time to send the update.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,580 Red Diamond
    edited Nov 11, 2022 10:58AM Answer ✓

    Well, I'm short on time myself... I do have a day job to do.

    Here's one way (not necessarily the best way, but then you fail to give us the details we need to know what would be best)...

    create trigger trgTable1Change before insert or update or delete on table1
    for each row
    begin
      if deleting then
        delete from table2 where emp_id =:old.employee_id;
      elsif inserting then
        insert into table2 (emp_id, emp_name, phone_number) values (:new.employee_id, :new.employee_name, :new.contact_number);
      elsif updating then
        update table2
          set emp_name = :new.employee_name
             ,phone_number = :new.contact_number
          where emp_id = :new.employee_id;
      end if;
    end;
    /
    
    


  • BluShadow
    BluShadow Member, Moderator Posts: 42,580 Red Diamond

    *Moderator Note: Please don't delete your comments from the discussion as it makes other replies look like nonsense and disrupts the content of the forum. I've re-instated the comments you deleted.