I have an attendance table in one Oracle schema named attendance_database and another schema named payroll_database and its table name is payroll_attendance_table.

Is it possible if I insert new data in attendance_table then payroll_attendance_table auto synchronize (auto insert/update) with that new inserted data. I hear it can be done by a trigger. Is it, else is there any other way. I want to handle this in database end, not want to handle this by any back-end language.

CodePudding user response：

Either:

1. Create a database link from attendance_database to payroll_database.
2. Create a row-level trigger on attendance_database.attendance_table to INSERT new rows across the database link into the payroll_database.payroll_attendance_table.

Or:

1. Create a database link from payroll_database to attendance_database.
2. Create payroll_attendance_table as a MATERIALIZED VIEW with FAST REFRESH ON COMMIT of attendance_database.attendance_table across the database link.

CodePudding user response：

First, since the data is just in two different schemas, are you sure you really need to copy it from one to the other? If attendance_database.attendance_table and payroll_database.payroll_attendance_table are supposed to have the same data, it would make vastly more sense to eliminate one and just create a synonym in payroll_database that points to attendance_database.

grant select on attendance_database.attendance_table
   to payroll_database;

create synonym payroll_database.payroll_attendance_table
   for attendance_database.attendance_table;

If they are not supposed to have the same data (perhaps the payroll_database.payroll_attendance_table table is supposed to have additional attributes, for example), you'd most likely want to make payroll_database.payroll_attendance_table a child table. Something like

grant references on attendance_database.attendance_table
   to payroll_database;

create table payroll_database.payroll_attendance_table (
  attendance_id integer references attendance_database.attendance_table( attendance_id ),
  <<additional attributes>>
);

If you really, really want to have two separate copies of the same data and to maintain them via a trigger, you could do something like

create or replace trigger trg_pointless_sync
  before insert or update or delete on attendance_database.attendance_table
  for each row
declare
begin
  if inserting 
  then
    insert into payroll_database.payroll_attendance_table( attendance_id,
                                                           col1,
                                                           col2,
                                                           ...
                                                           colN )
      values( :new.attendance_id,
              :new.col1,
              :new.col2,
              ...,
              :new.colN );
  elsif updating
  then 
    update payroll_database.payroll_attendance_table
       set col1 = :new.col1,
           col2 = :new.col2,
           ...
           colN = :new.colN
     where attendance_id = :new.attendance_id;
  else
    delete from payroll_database.payroll_attendance_table
     where attendance_id = :new.attendance_id;
  end if;
end; 

Of course, trigger based solutions are usually somewhat problematic. They can impose pretty significant performance penalties by transforming nice, fast set-based operations on the source table into recursive row-by-row operations. They can create maintenance issues if, say, a DBA wants to quickly restore attendance_table back to a previous state because someone goofed on some data and doesn't realize that deleting all the data has the side-effect of deleting all the data from the payroll_database table. And they almost always create synchronization issues-- what happens if someone modifies one of the rows in payroll_database, for example? Or if someone updates the PK of attendance_table, the trigger I wrote isn't smart enough to handle that correctly (you could extend it, of course, but there are usually more of these sorts of corner cases than you're likely to code for) so the two tables will eventually be not-quite-in-sync.