You are not logged in.
Announcement
Unanswered posts
|
Pages: 1
Hi, I'm in trouble with addting Sybase CDC on the table that has composit index.
The table has BANGOU, SHIMEI, ADDRESS, PHONE, COMPANY, EMAIL and AGE.
PRIMARY KEY CLUSTERED ( BANGOU, SHIMEI ).
I tried to "add CDC" to this table and clicked "Execute" button.
The pop up message "Incorrect syntax near ','." appeared three times.
I've confirmed otherwise trigger generation succeeded.
I guess that three trigger creation of insert, update, and delete shows three pop-up messages.
On the other hand one of index tables if successful.
What's wrong ?
What should I do ?
Environment :
Sybase Adaptive Server Enterprise Edition 15.5 Developer's Edition
Talend Data Quality Enterprise Edition MPX (4.2.3.r67267)
Adding script is as follows
-----------------------------
INSERT INTO "cdcdb"."cdc"."TSUBSCRIBERS"
(
"TALEND_CDC_TABLE_TO_WATCH",
"TALEND_CDC_SUBSCRIBER_NAME",
"TALEND_CDC_CREATION_DATE"
)
values ('data1db.ohtake.meibo2','APP1',getdate())
CREATE TABLE "cdcdb"."cdc"."TCDC_meibo2"
(
"TALEND_CDC_SUBSCRIBERS_NAME" VARCHAR(50) NOT NULL,
"TALEND_CDC_STATE" VARCHAR(1),
"TALEND_CDC_TYPE" VARCHAR(1),
"TALEND_CDC_CREATION_DATE" DATETIME,
"BANGOU" INT NOT NULL,
"SHIMEI" VARCHAR(5) NOT NULL
)
CREATE VIEW "cdc"."TCDC_VIEW_meibo2" AS
SELECT
SRC."TALEND_CDC_TYPE" TALEND_CDC_TYPE,
SRC."TALEND_CDC_SUBSCRIBERS_NAME" TALEND_CDC_SUBSCRIBERS_NAME,
max(SRC."TALEND_CDC_CREATION_DATE") TALEND_CDC_CREATION_DATE,
SRC."BANGOU" BANGOU,
SRC."SHIMEI" SHIMEI,
TARG."ADDRESS" ADDRESS,
TARG."PHONE" PHONE,
TARG."COMPANY" COMPANY,
TARG."EMAIL" EMAIL,
TARG."AGE" AGE
FROM "cdcdb"."cdc"."TCDC_meibo2" SRC LEFT JOIN "data1db"."ohtake"."meibo2" TARG
ON SRC."BANGOU"=TARG."BANGOU" AND SRC."SHIMEI"=TARG."SHIMEI"
WHERE SRC."TALEND_CDC_STATE"='1'
GROUP BY SRC."TALEND_CDC_SUBSCRIBERS_NAME", SRC."BANGOU", SRC."SHIMEI", TARG."ADDRESS", TARG."PHONE", TARG."COMPANY", TARG."EMAIL", TARG."AGE",
SRC."TALEND_CDC_TYPE"
USE data1db
CREATE TRIGGER "cdc"."TCDC_TG_meibo2_I" ON "data1db"."ohtake"."meibo2"
FOR INSERT
AS
INSERT INTO "cdcdb"."cdc"."TCDC_meibo2"
(
"TALEND_CDC_SUBSCRIBERS_NAME",
"TALEND_CDC_STATE",
"TALEND_CDC_TYPE",
"TALEND_CDC_CREATION_DATE",
"BANGOU",
"SHIMEI"
) SELECT
"TALEND_CDC_SUBSCRIBER_NAME",
'0',
'I',
getdate(),
(SELECT "BANGOU","SHIMEI" FROM inserted )
FROM "cdcdb"."cdc"."TSUBSCRIBERS"
WHERE "TALEND_CDC_TABLE_TO_WATCH"='data1db.ohtake.meibo2'
CREATE TRIGGER "cdc"."TCDC_TG_meibo2_U" ON "data1db"."ohtake"."meibo2"
FOR UPDATE
AS
INSERT INTO "cdcdb"."cdc"."TCDC_meibo2"
(
"TALEND_CDC_SUBSCRIBERS_NAME",
"TALEND_CDC_STATE",
"TALEND_CDC_TYPE",
"TALEND_CDC_CREATION_DATE",
"BANGOU",
"SHIMEI"
) SELECT
"TALEND_CDC_SUBSCRIBER_NAME",
'0',
'U',
getdate(),
(SELECT "BANGOU","SHIMEI" FROM inserted )
FROM "cdcdb"."cdc"."TSUBSCRIBERS"
WHERE "TALEND_CDC_TABLE_TO_WATCH"='data1db.ohtake.meibo2'
CREATE TRIGGER "cdc"."TCDC_TG_meibo2_D" ON "data1db"."ohtake"."meibo2"
FOR DELETE
AS
INSERT INTO "cdcdb"."cdc"."TCDC_meibo2"
(
"TALEND_CDC_SUBSCRIBERS_NAME",
"TALEND_CDC_STATE",
"TALEND_CDC_TYPE",
"TALEND_CDC_CREATION_DATE",
"BANGOU",
"SHIMEI"
) SELECT
"TALEND_CDC_SUBSCRIBER_NAME",
'0',
'D',
getdate(),
(SELECT "BANGOU","SHIMEI" FROM deleted )
FROM "cdcdb"."cdc"."TSUBSCRIBERS"
WHERE "TALEND_CDC_TABLE_TO_WATCH"='data1db.ohtake.meibo2'
-----------------------------
Offline
I found three facts.
I tried and successed to create triggers after this operation with Sybase Central's Interactive SQL tool as follows.
(1) CREATE TRIGGER FOR INSERT AND DELETE
change (SELECT "BANGOU","SHIMEI" FROM inserted ) into (SELECT "BANGOU" FROM inserted) , (SELECT "SHIMEI" FROM inserted).
(2) CREATE TRIGGER FOR DELETE
change (SELECT "BANGOU","SHIMEI" FROM deleted ) into (SELECT "BANGOU" FROM deleted ) , (SELECT "SHIMEI" FROM deleted )
This must be a bug.
Offline
Pages: 1