Hi ,

i REALLY NEED HELP IN CREATING THIS TRIGGER AS FAST AS i CAN. i AM NEW TO IT.
The business logic is as follows:
Trigger on CHECK_TABLE to enter a new REGION_DISTRICT.

1. On Insert
2. Is TABLE_NUM = 81?
a. No – do nothing and STOP
b. Yes – Continue
3. Get the MODEL_ID for the Current Model [CURRENT_MODEL_ID:]
a. SELECT MODEL_ID
FROM MODELS
WHERE YEAR = [max YEAR in MODELS]
AND SEQUENCE_NO = [max SEQUENCE_NO for max YEAR in MODELS]
AND DESIGNATION <> ‘P’
4. Is CHECK_TABLE.CODE in the REGION_DISTRICT table where MODEL_ID = CURRENT_MODEL_ID: ?
a. Yes – Do nothing and STOP
b. No – Continue
5. Insert new record into REGION_DISTRICT table
a. REGION = [1rst character of the CHECK_TABLE.CODE]
b. REGION_DISTRICT_CODE = CHECK_TABLE.CODE
c. PRINT_SEQUENCE = [max PRINT_SEQUENCE where MODEL_ID = CURRENT_MODEL: ]
d. DISTRICT_ABBREV = CHECK_TABLE.CODE
e. ALT_REGION_OFFICE = CHECK_TABLE.CODE
f. HEADER_LINE_1 = NULL
g. HEADER_LINE_2 = CHECK_TABLE.CODE
h. CREATED_BY = ‘ADMIN’
i. MODIFIED_BY = ‘ADMIN’
j. CREATED_DATE = [system date]
k. MODIFIED_DATE = [system date]
l. OFFICE_NAME = CHECK_TABLE.DESCRIPTION
m. MODEL_ID = CURRENT_MODEL_ID:
6. Does MODELS.DESIGNATION = ‘W” where MODEL_ID = CURRENT_MODEL_ID:?
a. No – do nothing and STOP
b. Yes – Continue
7. Get the MODEL_ID for the most recent Model from the previous year [PREV_MODEL_ID:]
a. SELECT MODEL_ID
FROM MODELS
WHERE YEAR = [max YEAR in MODELS minus 1]
AND SEQUENCE_NO = [max SEQUENCE_NO for (max YEAR in MODELS – 1)]
AND DESIGNATION <> ‘P’
8. Is CHECK_TABLE.CODE in the REGION_DISTRICT table where MODEL_ID = PREV_MODEL_ID: ?
a. Yes – Do nothing and STOP
b. No – Continue on to 9
9. Insert new record into REGION_DISTRICT table
a. REGION = [1rst character of the CHECK_TABLE.CODE]
b. REGION_DISTRICT_CODE = CHECK_TABLE.CODE
c. PRINT_SEQUENCE = [max PRINT_SEQUENCE where MODEL_ID = PREV_MODEL: ]
d. DISTRICT_ABBREV = CHECK_TABLE.CODE
e. ALT_REGION_OFFICE = CHECK_TABLE.CODE
f. HEADER_LINE_1 = NULL
g. HEADER_LINE_2 = CHECK_TABLE.CODE
h. CREATED_BY = ‘ADMIN’
i. MODIFIED_BY = ‘ADMIN’
j. CREATED_DATE = [system date]
k. MODIFIED_DATE = [system date]
l. OFFICE_NAME = CHECK_TABLE.DESCRIPTION
m. MODEL_ID = PREV_MODEL_ID:
10. Is the REGION code [first character of CHECK_TABLE.CODE] in the REGION table?
a. Yes – Do nothing and STOP
b. No – Continue on to 11
11. Insert new record into REGION table
a. REGION = [first character of CHECK_TABLE.CODE]
b. REGION_ABBREV = [first character of CHECK_TABLE.CODE]
c. HEADER_LINE_1 = NULL
d. HEADER_LINE_2 = ‘NEW REGION’
e. DESCRIPTION = ‘NEW REGION – CODE = ’ & [first character of CHECK_TABLE.CODE]
f. CREATED_BY = ‘ADMIN’
g. MODIFIED_BY = ‘ADMIN’
h. CREATED_DATE = [system date]
i. MODIFIED_DATE = [system date]
END

Add new code to FDA_PAC_UPDATE trigger on PROGRAM_ASSIGNMENTS table to enter a new PMS_CODE. This code is executed ONLY IF the PAC_CODE is new [not already in the PAC table]. First part of FDA_PAC_UPDATE should be making this check.

1. On Insert
2. Get the MODEL_ID for the Current Model [CURRENT_MODEL_ID:]
a. SELECT MODEL_ID
FROM MODELS
WHERE YEAR = [max YEAR in MODELS]
AND SEQUENCE_NO = [max SEQUENCE_NO for max YEAR in MODELS]
AND DESIGNATION <> ‘P’
3. Is PROGRAM_ASSIGNMENTS.PMS_CODE in the PMS table where MODEL_ID = CURRENT_MODEL_ID: ?
a. Yes – do nothing and STOP
b. No – Continue
4. Insert new record into PMS table
a. PMS_GROUP = 0
b. PMS_CODE = PROGRAM_ASSIGNMENTS.PMS_CODE
c. DESC_TEXT = ‘NEW PPS – PPS GROUP AND CENTER UNKNOWN’
d. CENTER_CODE = 0
e. MODEL_ID = CURRENT_MODEL_ID:
f. CREATED_BY = ‘ADMIN’
g. MODIFIED_BY = ‘ADMIN’
h. CREATED_DATE = [system date]
i. MODIFIED_DATE = [system date]
5. Does MODELS.DESIGNATION = ‘W” where MODEL_ID = CURRENT_MODEL_ID:?
a. No – do nothing and STOP
b. Yes – Continue
6. Get the MODEL_ID for the most recent Model from the previous year [PREV_MODEL_ID:]
a. SELECT MODEL_ID
FROM MODELS
WHERE YEAR = [max YEAR in MODELS minus 1]
AND SEQUENCE_NO = [max SEQUENCE_NO for (max YEAR in MODELS – 1)]
AND DESIGNATION <> ‘P’
7. Is PROGRAM_ASSIGNMENTS.PMS_CODE in the PMS table where MODEL_ID = PREV_MODEL_ID: ?
a. Yes – do nothing and STOP
b. No – Continue
8. Insert new record into PMS table
a. PMS_GROUP = 0
b. PMS_CODE = PROGRAM_ASSIGNMENTS.PMS_CODE
c. DESC_TEXT = ‘NEW PPS – PPS GROUP AND CENTER UNKNOWN’
d. CENTER_CODE = 0
e. MODEL_ID = PREV_MODEL_ID:
f. CREATED_BY = ‘ADMIN’
g. MODIFIED_BY = ‘ADMIN’
h. CREATED_DATE = [system date]
i. MODIFIED_DATE = [system date]
END

The tables are as follows:
These are the two tables involved:
CREATE TABLE REGION_DISTRICT
(
REGION CHAR(1) NOT NULL,
REGION_DIST_CODE VARCHAR2(2) NOT NULL,
PRINT_SEQUENCE NUMBER,
DISTRICT_ABBREV VARCHAR2(10),
ALT_REGION_OFFICE VARCHAR2(10),
HEADER_LINE1 VARCHAR2(10),
HEADER_LINE2 VARCHAR2(10),
CREATED_BY VARCHAR2(10),
CREATED_DATE DATE,
MODIFIED_BY VARCHAR2(10),
MODIFIED_DATE DATE,
OFFICE_NAME VARCHAR2(50),
MODEL_ID NUMBER NOT NULL
)




CREATE TABLE MODELS
(
MODEL_ID NUMBER NOT NULL,
YEAR NUMBER,
DESIGNATION CHAR(1),
SEQUENCE_NO NUMBER,
STATUS CHAR(1),
TO_STATUS CHAR(1),
MODELS_COMMENT VARCHAR2(1000),
CREATED_BY VARCHAR2(10),
CREATED_DATE DATE,
MODIFIED_BY VARCHAR2(10),
MODIFIED_DATE DATE,
WP_START_DATE DATE,
TRANSMIT_DATE DATE,
RELEASE_TO CHAR(1) DEFAULT 'F',
WP_SEQUENCE NUMBER DEFAULT 1 NOT NULL,
OLD_MODEL_ID NUMBER,
TRANSMIT_SCHEDULED CHAR(1)
)


CREATE TABLE CHECK_TABLE
(
TABLE_NUM VARCHAR2(2) NOT NULL,
CODE VARCHAR2(7),
DESCRIPTION VARCHAR2(74),
END_DATE DATE
)

THANK YOU VERY MUCH IN ADVANCE.
GYANA