Hi,
In am setting up a DWH - transfering data with CDC, in order to get changes visible immediatly. I do not wish to have a job, asking and asking if there is new data all the time, I would like to have the new data to trigger the job, so I tried to make a workaround.
Instead of use the extend window, and select from the subscriber view - which means I will have to make a job, that is asking for new changes every minute - I made a after insert trigger on the capture table XX_CT, in order to get new data populated to the Warehouse table immediatly, and only running the job, when there is actually new data to be stored.
I have tested the trigger by inserting rows into the XX_CT table, and it works, but when rows are inserted by CDC, the trigger does not fire.
Why is that? Does CDC not commit when inserting? What am I missing here?
Best Regards
Lene
CDC is working perfect, I do not include the CDC setup scripts.
CREATE TABLE TEST_CT
(
OPERATION$ CHAR(2 BYTE),
CSCN$ NUMBER,
COMMIT_TIMESTAMP$ DATE,
XIDUSN$ NUMBER,
XIDSLT$ NUMBER,
XIDSEQ$ NUMBER,
RSID$ NUMBER,
ROW_ID$ ROWID,
USERNAME$ VARCHAR2(30 BYTE),
TIMESTAMP$ DATE,
TARGET_COLMAP$ RAW(128),
DDLOPER$ NUMBER,
DDLDESC$ CLOB,
DDLPDOBJN$ NUMBER,
ID VARCHAR2(36 BYTE),
NAME VARCHAR2(20 BYTE),
DESCRIPTION VARCHAR2(100 BYTE),
CREATED_TS TIMESTAMP(6),
CREATED_BY VARCHAR2(30 BYTE),
CHANGED_TS TIMESTAMP(6),
CHANGED_BY VARCHAR2(30 BYTE)
)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
PARTITION BY RANGE (CSCN$)
(
PARTITION P1 VALUES LESS THAN (281474976710656)
LOGGING
NOCOMPRESS
TABLESPACE TS_HAVOERNEN_DAILY
LOB (DDLDESC$) STORE AS
( ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
NOCACHE
INDEX (
STORAGE (
PCTINCREASE 0
))
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
)
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
I am writing a message into a log table:
CREATE TABLE LOG_CDC
(
MESSAGE VARCHAR2(2000 BYTE)
)
I also run an insert procedure, but the text to the log table is enough to see, that it does'nt fire,
CREATE OR REPLACE TRIGGER CDC.TEST
AFTER INSERT
ON CDC.TEST_CT REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
v_ok varchar2(2000);
BEGIN
v_ok := ('Trigger fires');
insert into LOG_CDC(message) values(v_ok);
CDC.create_test
(:new.RSID$,
:new.ID,
:new.NAME ,
:new.DESCRIPTION,
:new.CREATED_TS,
:new.CREATED_BY,
:new.CHANGED_TS,
:new.CHANGED_BY
);
end;
/
triggers are designed to validate and even CHANGE data.
The thought is that that triggers have already fired ( on the initiating site ) and the data is validated and the changes have been made - and that firing the trigger again would be WRONG.
Also, there are occurrences when triggers do not fire. I hate triggers.
But CDC isn't designed to support triggers on the target table, just like in replication you cannot put triggers on materialized views (replicas) and so on.
ops$tkyte%ORA10GR2> create table t ( x int );
Table created.
ops$tkyte%ORA10GR2> create table t2 ( x int );
Table created.
ops$tkyte%ORA10GR2> create trigger t after insert on t for each row
2 begin
3 insert into t2 values (:new.x);
4 end;
5 /
Trigger created.
ops$tkyte%ORA10GR2> !cat t.ctl
LOAD DATA
INFILE *
INTO TABLE t
REPLACE
FIELDS TERMINATED BY '|'
(
x
)
BEGINDATA
1
2
ops$tkyte%ORA10GR2> !sqlldr / t
SQL*Loader: Release 10.2.0.4.0 - Production on Thu Oct 22 11:54:27 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Commit point reached - logical record count 2
ops$tkyte%ORA10GR2> select * from t2;
X
----------
1
2
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> truncate table t;
Table truncated.
ops$tkyte%ORA10GR2> truncate table t2;
Table truncated.
ops$tkyte%ORA10GR2> !sqlldr / t direct=y
SQL*Loader: Release 10.2.0.4.0 - Production on Thu Oct 22 11:54:28 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Load completed - logical record count 2.
ops$tkyte%ORA10GR2> select * from t2;
no rows selected