Problem in Update statement in autonomous transaction

From: Hemlata <hemlata_d_at_hotmail.com>
Date: 10 Feb 2002 22:08:03 -0800
Message-ID: <52a7a66e.0202102208.1bcbe78f_at_posting.google.com>

Hi there,

Please check the attched DDL.

---

create table contact(contactid number not null, name varchar2(10) ,
primary key (contactid)
);

create table household ( householdid number not null, hhname varchar2(10),
primary key(householdid)
);

create table householdmembers ( householdid number not null, contactid number not null,
primary key(householdid,contactid)
);

create table personal (contactid number not null, primaryhouseholdid number ,
primary key (contactid)
);

CREATE OR REPLACE PACKAGE PKG_CHECKDELETE IS ISDELETECLIENT NUMBER := 0;
END;
/

create OR REPLACE trigger td_CONTACT3 before Delete ON CONTACT begin
 PKG_CHECKDELETE.IsDeleteClient := 1;
end;
/

create OR REPLACE trigger td_CONTACT2 after Delete ON CONTACT begin
 PKG_CHECKDELETE.IsDeleteClient := 0;
end;
/

CREATE OR REPLACE TRIGGER TD_CONTACT1 AFTER DELETE ON CONTACT FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
begin
DELETE FROM HOUSEHOLDMEMBERS T2 WHERE t2.CONTACTID = :OLD.CONTACTID; DELETE FROM PERSONAL T2 WHERE t2.CONTACTID = :OLD.CONTACTID; COMMIT;
END;
/

CREATE OR REPLACE TRIGGER TD_HOUSEHOLDMEMBER AFTER DELETE ON HOUSEHOLDMEMBERS
REFERENCING OLD AS OLDROW NEW AS NEW FOR EACH ROW DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF PKG_CHECKDELETE.ISDELETECLIENT <> 1 THEN UPDATE PERSONAL P SET PRIMARYHOUSEHOLDID = (SELECT MIN(H.HOUSEHOLDID) FROM HOUSEHOLDMEMBERS H WHERE H.CONTACTID = :OLDROW.CONTACTID AND P.PRIMARYHOUSEHOLDID = :OLDROW.HOUSEHOLDID ) WHERE P.CONTACTID = :OLDROW.CONTACTID AND P.PRIMARYHOUSEHOLDID = :OLDROW.HOUSEHOLDID;
END IF;
COMMIT;
END;
/

INSERT INTO CONTACT VALUES (1,'DEOTALE'); INSERT INTO HOUSEHOLD VALUES (1,'H1'); INSERT INTO HOUSEHOLD VALUES (2,'H2'); INSERT INTO HOUSEHOLDMEMBERS VALUES (1,1); INSERT INTO HOUSEHOLDMEMBERS VALUES (2,1); INSERT INTO PERSONAL VALUES (1,1);

---

Now if I will issue following command:

DELETE FROM HOUSEHOLDMEMBERS WHERE HOUSEHOLDID = 1 AND CONTACTID = 1; It should update PERSONAL.PRIMARYHOUSEHOLDID = 2. But it is still 1.

Please tell me, is there anything wrong in trigger td_householdmembers?

Thanks in advance,
Hemlata Received on Mon Feb 11 2002 - 07:08:03 CET