Re: How can I make this trigger?

> 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 Received on Mon Feb 09 2004 - 00:04:23 CET