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_STUD ENT and KEY_SKILLS. There are fields in
KEY_SKILLS_STUD ENT: KEY_SKILLS_ID, PORTFOLIO_RESUL T and ACHIEVED. The
trigger should update automatically the field ACHIEVED to TRUE (-1),
when PORTFOLIO_RESUL T 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]

Re: How can I make this trigger?

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


galkas@mail.ru (Galina) wrote in message news:<ecdc865.0 402020610.6e1f3 e85@posting.goo gle.com>...[color=blue]
> Hello
> I work with Oracle 9 database. I want to create a trigger using 2
> tables: KEY_SKILLS_STUD ENT and KEY_SKILLS. There are fields in
> KEY_SKILLS_STUD ENT: KEY_SKILLS_ID, PORTFOLIO_RESUL T and ACHIEVED. The
> trigger should update automatically the field ACHIEVED to TRUE (-1),
> when PORTFOLIO_RESUL T 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.[/color]

[Folke Larsson]

Re: How can I make this trigger?

> Hello[color=blue]
> I work with Oracle 9 database. I want to create a trigger using 2
> tables: KEY_SKILLS_STUD ENT and KEY_SKILLS.[/color]


Hello Galina

I am not that experienced with triggers myself but I have some ideas.
If you considers only UPDATE on KEY_SKILLS_STUD ENT I think you
should use AFTER UPDATE on PORTFOLIO_RESUL T row-trigger that updates
ACHIEVED after the UPDATE on PORTFOLIO_RESUL T.
A row-level trigger with the conditions in a WHEN clause could
be advantegous withsomething like:
WHEN( PORTFOLIO_RESUL T 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_SKILL S_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_STUD ENT 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