How to Create a Trigger for create automatic generated ID….

Use Tools Oracle 10g and Toad 9

Step 1#

–##### Added By khairul. Date :06-Mar-2011
–##################### Start Get PS_LEASE_ASSET_ID(PS_LEASE_ASSET_ID)########################################

FUNCTION GET_NEW_LEASE_ASSET_ID (a IN NUMBER)
RETURN VARCHAR2
IS
tmp_id PS_LEASE_ASSET.PS_LEASE_ASSET_ID%TYPE;
tmpBID VARCHAR2 (5);
BEGIN
SELECT C.COUNTRY_ID ||SUBSTR(C.CMP_BRANCH_ID,7,3) BID INTO tmpBID FROM CM_CMP_BRANCH C WHERE C.IS_OPERATING=’Y';

SELECT NVL (MAX (PS_LEASE_ASSET_ID), TO_CHAR (SYSDATE, ‘yymmdd’)|| tmpBID || ’0000000′)
INTO tmp_id
FROM PS_LEASE_ASSET;

IF SUBSTR (tmp_id, 1, 6) = TO_CHAR (SYSDATE, ‘yymmdd’)
THEN
RETURN TO_CHAR (tmp_id + 1, ’000000000000000000′);
ELSE
RETURN TO_CHAR (SYSDATE, ‘yymmdd’) || tmpBID || ’0000001′;
END IF;
END;
–#########################End Get PS_LEASE_ASSET_ID#####################—–
Read the rest of this entry »