Trigger-Synchronize data from two tables

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Thu, 13 Jan 2005 4:21 pm

Hello guys I have two tables A and B that has similar columns. When I change a field in a, também change the corresponding field in the other. And vice versa. The ideal was to make this control by trigger, but went into a loop, because when I change in the, triggers the trigger for change in B, when changes in B fires the trigger for change at a. has anyone went through this situation?
Thank you.
lamanita
Location: Porto Alegre - RS

Samuel, o lamanit@

Poston Thu, 13 Jan 2005 4:43 pm

These tables are in the same schema?

I have done this with tables in different schemas. Then I placed well at the beginning of the code: In BRAZIL:
Code: Select all
IF user='BRASIL' 
THEN
  código...  atualiza tabelas no esquema EUA
  código...
END IF;
and in USA:
Code: Select all
IF user='EUA' 
THEN
  código...  atualiza tabelas no esquema BRASIL
  código...
END IF;
Thus, the trigger would be round only once!
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Thu, 13 Jan 2005 4:45 pm

Yes, they're in the same ...

Could you elaborate on how you make the different schema, suddenly I can figure out a way to ask to change the schema.

You get the owner of the table or the database user?
lamanita
Location: Porto Alegre - RS

Samuel, o lamanit@

Poston Fri, 14 Jan 2005 12:27 pm

I had 2 systems running on the same server. (each system, with an owner different)

The user control system level era, i.e. all users if they log on with the same user on the bench and the controlled system.

With this, it was possible to make some tables integrations: Everything that was changed into a owner, was also changed in a table the other owner-equivalent for purposes of systems integration.

How it was done?
He tested the trigger who was the USER who was doing the change!
Since each system just changed their own tables, the trigger was testing whether the USER making the adulteration was the same. If it wasn't, this meant that it was the result of a \"LOOP\" and stop there.
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Fri, 14 Jan 2005 1:15 pm

Here system users are user também Bank ...
lamanita
Location: Porto Alegre - RS

Samuel, o lamanit@

Poston Mon, 17 Jan 2005 2:12 pm

Then you can do so: * create a package that holds a variable. Since this variable is visible during the session, you can define if there is to insert.
When you are entering in the first, change the value, because when the trigger of another is called, she won't enter.

To illustrate, here's the code: Creating the tables
Code: Select all
create table x(id number,col1 varchar2(10))
/
create table y(id number,col1 varchar2(10))
/
Creating the package
Code: Select all
create or replace package test_pkg is
update_from_trigger boolean := false;
end;
/
Creating the triggers
Code: Select all
create or replace trigger x_bir_bur_bdr_trg
before insert or update or delete on x
for each row
begin

  if test_pkg.update_from_trigger = false then
     test_pkg.update_from_trigger := true;

     if inserting then
        insert into y (id,col1)
        values (:new.id,:new.col1);
     elsif updating then
           update y set col1 = :new.col1
           where id = :new.id;
     elsif deleting then
           delete from y
           where id = :old.id;
     end if;
  end if;

  test_pkg.update_from_trigger := false;
end;
/

create or replace trigger y_bir_bur_bdr_trg
before insert or update or delete on y
for each row
begin

  if test_pkg.update_from_trigger = false then
     test_pkg.update_from_trigger := true;

     if inserting then
        insert into x (id,col1)
        values (:new.id,:new.col1);
     elsif updating then
           update x set col1 = :new.col1
           where id = :new.id;
     elsif deleting then
           delete from x
           where id = :old.id;
     end if;
  end if;
  test_pkg.update_from_trigger := false;

end;
/
Testing!
Code: Select all
SQL> insert into x values (1,'a');

1 row created.

SQL> select * from y;

        ID COL1
---------- ----------
         1 a

SQL> insert into y values (2,'b');

1 row created.

SQL>
SQL> select * from x;

        ID COL1
---------- ----------
         1 a
         2 b

SQL>
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered


  • See also
    Replies
    Views
    Last Post


Return to PL/SQL

Who is online

Users browsing this forum: Bing [Bot] and 8 guests