| Trigger execution in oracle streams. [message #75671] |
Tue, 20 January 2004 20:42  |
Vidyanand More
Messages: 35
Registered: January 2003
|
Member |
|
|
Hi All,
Oracle Database version : 9.2.0.4 on windows NT/2000 environment.
We managed to install,configure oracle stream technologies.
Oracle Stream seems to be working fine for replication of DML & DDL changes from source database to target database.
Following is detail at source end.
Source Sid = acc
Source Schema = stream
Source Table = dept
structure of dept table.
Name Null? Type
----------------------------------------- -------- ------------------
DEPTNO NOT NULL NUMBER(5)
DNAME NOT NULL VARCHAR2(10)
LOC NOT NULL VARCHAR2(10)
Streamadmin user = strmadmin
Following is detail at target end.
Target Sid = fin
Target Schema = stream
Target Table = dept
structure of dept table.
Name Null? Type
----------------------------------------- -------- ------------------
DEPTNO NOT NULL NUMBER(5)
DNAME NOT NULL VARCHAR2(10)
LOC NOT NULL VARCHAR2(10)
TRAN_DATE NULL DATE DEFAULT SYSDATE
I checked on insert/update/delete of rows into dept table at source database, changes are correctly replicated to target table dept.
I wrote a simple trigger which is as follows on dept table at target database.
create or replace trigger dept_upd_del
before delete or update of dname,loc on stream.dept
for each row
begin
dbms_output.put_line('Inside Trigger');
if updating then
dbms_output.put_line('Update');
insert into stream.dept_change values (:old.deptno,'U',sysdate);
end if;
if deleting then
dbms_output.put_line('Delete');
insert into stream.dept_change values (:old.deptno,'D',sysdate);
end if;
end;
I expect this trigger to get executed whenever changes occurs into dept table at target database whenever dml changes are propagated from source to target table. However i found that the above trigger is not executed at all.
I was further surprised, since incase i update/delete rows from target table dept the above trigger is executing correctly.
Can someonle please let me know about this?
I believe stream technology is using INSERT / UPDATE & DELETE statement when changes are applied at target table but this doesn't seems to be the case?
Thanks in Advance.
Regards,
Vidyanand
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
