Home » Server Options » Streams & AQ » Trigger execution in oracle streams.
Trigger execution in oracle streams. [message #75671] Tue, 20 January 2004 20:42 Go to next message
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
Re: Trigger execution in oracle streams. [message #75672 is a reply to message #75671] Tue, 20 January 2004 21:35 Go to previous messageGo to next message
hazrin
Messages: 1
Registered: January 2004
Junior Member
u need to set the firing property trigger to FALSE..

begin
DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY
('schema','trigger_name',FALSE);
end;
/

hope this help..
Re: Trigger execution in oracle streams. [message #75673 is a reply to message #75672] Wed, 21 January 2004 21:54 Go to previous messageGo to next message
Vidyanand More
Messages: 35
Registered: January 2003
Member
Hi hazrin,
Yes, the problem is solved now, after executing the pl-sql block mentioned by you.
Thanks a lot for your help.
Is this a new change in oracle 9i ?
Thanks in Advance.
Regards,
Vidyanand
Re: Trigger execution in oracle streams. [message #388680 is a reply to message #75673] Wed, 25 February 2009 22:50 Go to previous messageGo to next message
mpalacios_pe
Messages: 1
Registered: February 2009
Junior Member
Hi

Did you run the DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY on target or source database?

Regards
Miguel.
Re: Trigger execution in oracle streams. [message #420481 is a reply to message #75671] Sun, 30 August 2009 12:58 Go to previous message
mrunalnargunde
Messages: 1
Registered: August 2009
Junior Member
Hello ,
I also have same problem as mentioned above but I am using oracle 10g.The snippet provided worked successfully. Its still does not work ! please help in the context !

Previous Topic: Can PAYLOAD in Queues be other than OBJECT like PL/SQL table?
Next Topic: Production Server moves on different machine
Goto Forum:
  


Current Time: Thu Mar 28 14:07:16 CDT 2024