| Positioned delete(using current of logic) [message #25671] |
Sat, 10 May 2003 10:37  |
Mr SQL Chap
Messages: 1
Registered: May 2003
|
Junior Member |
|
|
I like to ask a question.There are 3 tables TV3CLMS and TV3CLMT and TV3WKLS. Dependencies are like this. CLMSCLMT,CLMSWKLS that is (parentchild) ex CLMS
Is parent and CLMT is child. Now I am using a "CURRENT OF" to delete rows from TV3WKLS, so there is declare cursor then fetch cursor and then delete current of
EXEC SQL
DELETE FROM TV3WKLS
WHERE CURRENT OF SI_CSR
END-EXEC.
WHEN DELETE IS EXECUTED IT GIVES SQLDODE OF -532(RI VIOLATION) ON CLMSCLMT. WHY IS CLMSCLMT PREVENTING DELETING FROM TV3WKLS The declare cursor looks like this
EXEC SQL
DECLARE SI_CSR CURSOR WITH HOLD FOR
SELECT WORK_LIST_KEY,
CRTN_DATE,
SYSTM_GNRTD_FLAG,
MLSTN_CODE,
MLSTN_TEXT,
CLAIM_KEY,
CLAIM_NO,
MLSTN_TITLE,
UPDT_EMPLY_ID
FROM TV3WKLS
WHERE (TV3WKLS.MLSTN_CODE = :WS-002 OR
TV3WKLS.MLSTN_CODE = :WS-003 OR
TV3WKLS.MLSTN_CODE = :WS-004 OR
TV3WKLS.MLSTN_CODE = :WS-005)
AND EXISTS
(SELECT CLAIM_NO
FROM TV3CLMS
WHERE TV3CLMS.CLAIM_KEY = TV3WKLS.CLAIM_KEY
WHERE TV3WKLS.CLAIM_KEY = TV3CLMS.CLAIM_KEY
AND TV3CLMS.TWO_DSPLY_TYPE = :WS-SI-CLAIM)
END-EXEC.
HERE IS THE ERROR REPORT OF DSNTIAR CALL
DSNTAR-ER-TX1: DSNT408I SQLCODE = -532, ERROR: THE RELATIONSHIP CLMSCLMT RESTRICTS THE DELETION OF ROW WITH RID X'0000005A06'
DSNTAR-ER-TX2: DSNT418I SQLSTATE = 23504 SQLSTATE RETURN CODE
DSNTAR-ER-TX3: DSNT415I SQLERRP = DSNXRDWC SQL PROCEDURE DETECTING ERROR
DSNTAR-ER-TX4: DSNT416I SQLERRD = -250 13172769 0 13228485 -742129664 0 SQL DIAGNOSTIC INFORMATION
DSNTAR-ER-TX5: DSNT416I SQLERRD = X'FFFFFF06' X'00C90021' X'00000000' X'00C9D9C5' X'D3C40000' X'00000000' SQL DIAGNOSTIC
DSNTAR-ER-TX6: INFORMATION
DSNTAR-ER-TX7:
DSNTAR-ER-TX8:
WS-SYS-ERR-FMT = DSNT408I SQLCODE = -532, ERROR: THE RELATIONSHIP CLMSCLMT RESTRICTS THE DELETION OF ROW WITH
WS-SQLCODE-ERROR-MSG = ERROR: THE RELATIONSHIP CLMSCLMT RESTRICTS THE DELETION OF ROW WITH
----------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION:WHY IS CLMSCLMT GETTING IN THE WAY OF DELETING A ROW FROM TV3CWKLS.
QUESTION:WHY DOES IT THINK WE ARE TRYING TO DELETE FORM TV3CLMS TABLE.
I WILL APPRECIATE VERY MUCH IF YOU CAN SHED SOME LIGHT ON THIS.
|
|
|
|
| Re: Positioned delete(using current of logic) [message #25680 is a reply to message #25671] |
Mon, 12 May 2003 11:26  |
Sud
Messages: 63
Registered: September 2002
|
Member |
|
|
Check the DELETE constraint you set on the primary table, whether it is ON DELETE CASCADE or ON DELETE RESTRICT. If it is CASCADE, and when you attempt to delete rows from parent, it also removes from child automatically, where as RESTRICT restricts you to delete rows from parent unless you delete the pertinent in child table. Check it out.
Good luck :)
|
|
|
|
|
|
| Re: Positioned delete(using current of logic) [message #25955 is a reply to message #25870] |
Wed, 28 May 2003 07:50 |
Sud
Messages: 63
Registered: September 2002
|
Member |
|
|
Its not that you are trying to delete from CLMS table. Since it is restricting to remove rows from WKLS table, it may have a foreign key which refers to a key/column in other parent (CLMS) table. Check the data integration and how three tables are related together. As you said, all the tables are defined with RESTRICT constraint, something a link between two of these restricting you to delete. Check it out. Otherwise, post the description of these three tables here, so it may give us a clear idea of it. Check it out.
Good luck :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
