Oracle 10g: Updating Child's Table's Status

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_MST
Child = 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..:)

Financial Tips: Home Downpayment

Put at least 20% down on a home. Making a down payment of less than 20% will usually result in a private mortgage insurance (PMI) fee being added. This is usually 0.5%, meaning it could cost you about $1,000 a year on a $200,000 principal.