Fix cascade deletion of IDN_CLAIM_MAPPING table when deleting IDN_CLAIM by the claim id
@@ -902,7 +902,7 @@ CREATE TABLE IDN_CLAIM (
CLAIM_URI VARCHAR (255) NOT NULL,
TENANT_ID INTEGER NOT NULL,
PRIMARY KEY (ID),
- FOREIGN KEY (DIALECT_ID) REFERENCES IDN_CLAIM_DIALECT(ID) ON DELETE CASCADE,
+ FOREIGN KEY (DIALECT_ID) REFERENCES IDN_CLAIM_DIALECT(ID) ON DELETE NO ACTION,
CONSTRAINT CLAIM_URI_CONSTRAINT UNIQUE (DIALECT_ID, CLAIM_URI, TENANT_ID)
);
@@ -937,7 +937,7 @@ CREATE TABLE IDN_CLAIM_MAPPING (
MAPPED_LOCAL_CLAIM_ID INTEGER NOT NULL,
TENANT_ID INTEGER NOT NULL,
PRIMARY KEY (ID),
- FOREIGN KEY (EXT_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE ,
+ FOREIGN KEY (EXT_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE NO ACTION ,
FOREIGN KEY (MAPPED_LOCAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE NO ACTION ,
CONSTRAINT EXT_TO_LOC_MAPPING_CONSTRN UNIQUE (EXT_CLAIM_ID, TENANT_ID)
);
@@ -1369,3 +1369,22 @@ CREATE INDEX IDX_CORS_SP_APP_ID ON IDN_CORS_ASSOCIATION (SP_APP_ID);
-- IDN_CORS_ASSOCIATION --
CREATE INDEX IDX_CORS_ORIGIN_ID ON IDN_CORS_ASSOCIATION (IDN_CORS_ORIGIN_ID);
+
+CREATE TRIGGER IDN_CLAIM_DIALECT_DELETE_TRIGGER
+
+-- Trigger IDN_CLAIM delete by dialect on IDN_CLAIM_DIALECT deletion --
+ON IDN_CLAIM_DIALECT
+INSTEAD OF DELETE
+AS BEGIN
+ DELETE FROM IDN_CLAIM WHERE DIALECT_ID IN (SELECT ID FROM deleted)
+ DELETE FROM IDN_CLAIM_DIALECT WHERE ID IN (SELECT ID FROM deleted)
+END;
+
+-- Trigger IDN_CLAIM_MAPPING delete by claim id on IDN_CLAIM deletion by claim id --
+CREATE TRIGGER IDN_CLAIM_MAPPING_DELETE_TRIGGER
+ON IDN_CLAIM
+INSTEAD OF DELETE
+AS BEGIN
+ DELETE FROM IDN_CLAIM_MAPPING WHERE EXT_CLAIM_ID IN (SELECT ID FROM deleted) OR MAPPED_LOCAL_CLAIM_ID IN (SELECT ID FROM deleted)
+ DELETE FROM IDN_CLAIM WHERE ID IN (SELECT ID FROM deleted)
+END;
