The link will attempt to connect to the database RMT_DB_1 using the same
username and password as the account using the link.
The trigger shown in the following listing uses this link. The trigger is fired after every row is
inserted into the EMPLOYEES table. Because the trigger executes after the row has been inserted,
the row??™s data has already been validated in the local database. The trigger inserts the same row
into a remote table with the same structure, using the TRIGGER_LINK database link just defined.
The remote table must already exist.
create trigger COPY_DATA
after insert on EMPLOYEES
for each row
begin
insert into EMPLOYEES@TRIGGER_LINK
values
(:new.Empno, :new.Ename, :new.Deptno,
:new.Salary, :new.Birth_Date, :new.Soc_Sec_Num);
end;
/
This trigger uses the new keyword to reference the values from the row that was just inserted into
the local EMPLOYEES table.
NOTE
If you use trigger-based replication, your trigger code must account
for potential error conditions at the remote site, such as duplicate key
values, space-management problems, or a shut down database.
select Trigger_Type,
Triggering_Event,
Table_Name
from DBA_TRIGGERS
where Trigger_Name = 'COPY_DATA';
Sample output from this query is as follows:
TYPE TRIGGERING_EVENT TABLE_NAME
---------------- ---------------------- ------------
AFTER EACH ROW INSERT EMPLOYEES
You can query the text of the trigger from DBA_TRIGGERS, as shown in this example:
set long 1000
select Trigger_Body
from DBA_TRIGGERS
where Trigger_Name = 'COPY_DATA';
612 Oracle Database 11g DBA Handbook
TRIGGER_BODY
-------------------------------------------------------
begin
insert into EMPLOYEES@TRIGGER_LINK
values
(:new.
Pages:
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898