If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Oracle Trigger. Please help!!!

Hello, I'm very new at using triggers and I cannot find the answer to my problem online or in my oracle books.

I work for an insurance company and we have 1000+ policies that we are renewing that were previously policies for another insurance company.

Each month we receive an excel spreadsheet with all of the policy information. After I do some data conversion, we enter the policies into our system.

We need to track the status of each policy from start to finish.

An example would be:
1.Policy received with monthly load file OR
policy received separately.

Next step either way:

2. a)Data conversion complete and policy is ready to enter OR
b)Data conversion is complete and information is missing OR
c)Data conversion is complete and policy is ineligible

Next step for 2a):
3. a)Policy has been entered and issued OR
b)Policy has been entered and suspended

Next step for 2b):
Waiting on missing information

Next step for 2c):
Nothing

Next step for 3a):
Policy print has been pulled

then policy print has beem mailed, etc... etc...

What I want to do is have the data stored in an oracle database table (already done this) and for everyone to access the data via MS Access.

The people entering the policies would work with the policies that are okay to enter and then wither issue or suspend the policies.

For each issue or suspend, each time the policy goes from missing info to ok to enter, each time the policy goes from print pulled to print mailed, etc.. etc... I want the users to select the status from a dropdown in access to update the row.

Where the trigger comes in:
Each time the status changes, I want the policy ID, status and date/timestamp to be inserted as a new row into a transactions table. (I'd also like for the status date field in the monthly load table to be updated with the same date/timestamp each time the status changes)

This would enable me to have the current status in the main table at all times and also allow me to report the lifecycle of each policy based on the date/timestamp in the transaction table. I can acheive something similar by simply having multiple status fields in the monthly load table and each new status can be entered in the next field, however this does not give me one column containing the current status at all times.

If anyone can help me I would be VERY happy as I'm supposed to have this set up one way or another by Monday morning and I'm stuck on this part preventing me from doing the rest of the reporting and entry design.

You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.

You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.

Can you give me an example for the actual trigger? I've only done one trigger and it was an after update trigger that adds the timestamp to the same row of a table. For this one, I would not be adding new rows to the OCTOBER table, I would only be updating the status. I've tried writing it several different ways and I keep getting it to "compile with warning" and then when I change something in the October table I cannot save the table because of the trigger.

(I want an after update for each row trigger that will add in the timestamp into TRANS_DATE on the LG.OCTOBER table, then select the ROW_1, TRANS_CODE and TRANS_DATE from LG.OCTOBER and insert them as a new row in LG.TRANS into the fields ROW_NUM, TRANS_CODE and TRANS_NUM.)

P.S. I can make ROW_1 the primary key, but since I'm in control of anything that goes into the table, I'm already ensuring that there are no duplicates in that field. I would usually make it the primary key though. Good suggestion.

Read & heed #1 STICKY post at top of the forum on how to use VB <code tags>

You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.

You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.