How can I make this trigger?

[Galina]

Hello
I work with Oracle 9 database. I want to create a trigger using 2
tables: KEY_SKILLS_STUDENT and KEY_SKILLS. There are fields in
KEY_SKILLS_STUDENT: KEY_SKILLS_ID, PORTFOLIO_RESULT and ACHIEVED. The
trigger should update automatically the field ACHIEVED to TRUE (-1),
when PORTFOLIO_RESULT is updated with strings "Pass" or "Exempt", but
not always. A field KEY_SKILLS_ID is a link to KEY_SKILLS table.
KEY_SKILLS table has got fields KEY_SKILLS_AREA_ID and KEY_SKILLS_ID.
I want the trigger to work, only if KEY_SKILLS_AREA_ID is >3.
I am not very experienced with Oracle triggers. I have made a couple
in my life, but they were much simpler, on a single table. Please
could you post me an example of a code for such trigger, or to point
into a right direction.
Thank you very much.

[s.kapitza]

goggle trigger oracle,

or http://groups.google.com/groups?hl=e...1%40wanadoo.fr

could provide some programmatic items you could use.

regards

s.kapitza
ga****@mail.ru (Galina) wrote in message news:<ec*************************@posting.google.c om>...

Hello
I work with Oracle 9 database. I want to create a trigger using 2
tables: KEY_SKILLS_STUDENT and KEY_SKILLS. There are fields in
KEY_SKILLS_STUDENT: KEY_SKILLS_ID, PORTFOLIO_RESULT and ACHIEVED. The
trigger should update automatically the field ACHIEVED to TRUE (-1),
when PORTFOLIO_RESULT is updated with strings "Pass" or "Exempt", but
not always. A field KEY_SKILLS_ID is a link to KEY_SKILLS table.
KEY_SKILLS table has got fields KEY_SKILLS_AREA_ID and KEY_SKILLS_ID.
I want the trigger to work, only if KEY_SKILLS_AREA_ID is >3.
I am not very experienced with Oracle triggers. I have made a couple
in my life, but they were much simpler, on a single table. Please
could you post me an example of a code for such trigger, or to point
into a right direction.
Thank you very much.

[Folke Larsson]

> Hello

I work with Oracle 9 database. I want to create a trigger using 2
tables: KEY_SKILLS_STUDENT and KEY_SKILLS.

Hello Galina

I am not that experienced with triggers myself but I have some ideas.
If you considers only UPDATE on KEY_SKILLS_STUDENT I think you
should use AFTER UPDATE on PORTFOLIO_RESULT row-trigger that updates
ACHIEVED after the UPDATE on PORTFOLIO_RESULT.
A row-level trigger with the conditions in a WHEN clause could
be advantegous withsomething like:
WHEN( PORTFOLIO_RESULT IN ('Pass','Exempt' ) AND .....)
To find out the conditions for KEY_SKILLS_AREA_ID in the other
table you can make a variable v_nr and another v_key_skills_id
with the current KEY_SKILLS_ID and use

SELECT COUNT INTO v_nr FROM KEY_SKILLS
WHERE KEY_SKILLS_AREA_ID > 3
AND KEY_SKILLS_ID = v_key_skills_id

To find if there is any records with your desired criteria. Put
AND v_nr > 0 in WHEN conditions so your triggers wont fire if
no corresponding post in KEY_SKILLS is found.

The only problem is to find out the actual KEY_SKILLS_ID. You cannot
use :NEW because KEY_SKILLS_ID is not changed. You cannot make a SELECT
on the same table(KEY_SKILLS_STUDENT) because you get "mutating problems".
When making row-triggers you have to consider "mutating issues" that
happens if you in the trigger makes a SELECT on the same table
that the trigger is defined on. It also happens if you in the
trigger tries to change the primary-key that has a foreign-key
defined on it. I think this could be different in different
Oracle-versions, but I hope it is like this now.
I think you will not see the mutating problems until
you run a statement that invokes the trigger.

If you have a package with all stored code for students or
student skills you can make a global variable with the actual
KEY_SKILLS_ID. To capture it you can make a STATEMENT trigger
BEFORE UPDATE on KEY_SKILLS_STUDENT that sets the global variable.

I must say I haven't made exactly this myself, only been thinking of
it. Perhaps anyone more experienced can give an easier solution for
your problem. I am interested to know myself.
Regards

Folke Larsson