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..:)

Oracle 10g: Execute Stored Procedure(Add)

SET SERVEROUTPUT ON
DECLARE
ROW NUMBER;
BEGIN
TRNWORKFLOW_PCKG.SP_ADDTRNWORKFLOW
(55,1,1,1,1,SYSDATE,NULL,1,NULL,NULL,ROW);
DBMS_OUTPUT.PUT(ROW);
END;

Oracle 10g: Create New Package

CREATE OR REPLACE PACKAGE DB_PCKG IS
/*
*
* This package used for internal database procedures (logfile).
* Inclusive of all modules.
*
*
*
* @package DB_PCKG
* @author Nur Fiza Suhati
* @version 1.0
*
*/

PROCEDURE SP_ADDLOGFILE
(
LOGDATE_ IN INTEGER,
LOGACTION_ IN INTEGER,
TASKID_ IN INTEGER,
USERID_ IN INTEGER,
TABLENAME_ IN VARCHAR2,
BRANCHID_ IN INTEGER
);
END DB_PCKG;
/

CREATE OR REPLACE PACKAGE BODY DB_PCKG IS

PROCEDURE SP_ADDLOGFILE
(
LOGDATE_ IN INTEGER,
LOGACTION_ IN INTEGER,
TASKID_ IN INTEGER,
USERID_ IN INTEGER,
TABLENAME_ IN VARCHAR2,
BRANCHID_ IN INTEGER
)
AS
TABLEID INTEGER;
BEGIN
/*
Create auto running number
*/
SELECT MAX(LOGID) INTO TABLEID from wf_logfile where BRANCHID = BRANCHID_;

IF TABLEID IS NULL THEN
TABLEID := 0;
END IF;
TABLEID := TABLEID + 1;

/*
Insert statement
*/
INSERT INTO WF_LOGFILE(LOGID,LOGDATE,LOGACTION,TASKID,USERID,TABLENAME,BRANCHID)
VALUES(TABLEID,'',1,1,1,'WF_LOGFILE',1);
END;