In order to minimize code and data manipulation at the application server, I have suggested to the team that this will be controlled at the database server by using stored procedures and triggers to update the child's table status AUTOMATICALLY if the parent's table status is changed. Hopefully it will lighten up the network transacts...
Example:
Parent =
GBL_ORGANIZATION_MSTChild =
GBL_ADDRESS_DTL
1. Create Stored Proc: Focus on the bolded Item. I have other tables using this sp as well. I need to control the flow of the data here.PROCEDURE SP_UPDATESTATUSDTLADDRESS(
ADDRESSID_ IN GBL_ADDRESS_DTL.ADDRESSID%TYPE,
OWNERTYPE_ IN GBL_ADDRESS_DTL.OWNERTYPE%TYPE,
OWNERID_ IN GBL_ADDRESS_DTL.OWNERID%TYPE,
BRANCHID_ IN GBL_ADDRESS_DTL.BRANCHID%TYPE,
STATUS_ IN GBL_ADDRESS_DTL.STATUS%TYPE,
WHERESTATUS_ IN CHAR,
LASTMODIFIEDBY_ IN GBL_ADDRESS_DTL.LASTMODIFIEDBY%TYPE
)
AS
BEGIN
IF ADDRESSID_ <> 0 THEN
UPDATE GBL_ADDRESS_DTL
SET STATUS = STATUS_,
LASTMODIFIEDBY = LASTMODIFIEDBY_,
LASTMODIFIEDDATE = SYSDATE
WHERE ADDRESSID=ADDRESSID_ and STATUS = WHERESTATUS_ and BRANCHID=BRANCHID_;
ELSIF ADDRESSID_ = 0 THEN
UPDATE GBL_ADDRESS_DTL SET STATUS = STATUS_, LASTMODIFIEDBY = LASTMODIFIEDBY_, LASTMODIFIEDDATE = SYSDATE WHERE OWNERTYPE=OWNERTYPE_ and OWNERID=OWNERID_ AND STATUS = WHERESTATUS_ and BRANCHID=BRANCHID_; END IF;
END;
2. Create Trigger: Multiple triggers can be created in each table. Value of 6001 is actually the type of data that we have in our table.CREATE OR REPLACE TRIGGER UPDATESTATUS_MSTORGANIZATION AFTER update OF STATUS ON GBL_ORGANIZATION_MST for each row
begin
IF :new.Status = 'A' then
GBL_Address_PCKG.SP_UPDATESTATUSDTLADDRESS( 0,6001,:new.ORGID,:new.BRANCHID,'A','D',:new.LASTMODIFIEDBY);
elsif :new.Status = 'D' then
GBL_Address_PCKG.SP_UPDATESTATUSDTLADDRESS( 0,6001,:new.ORGID,:new.BRANCHID,'D','A',:new.LASTMODIFIEDBY);
end if;
end;
3. Execute both of the script..and wallaahhh...its done!..Thanks to En Hidayat for the guide..:)
