Home » SQL & PL/SQL » SQL & PL/SQL » Multiple 'OF' clauses in a trigger?
Multiple 'OF' clauses in a trigger? [message #272647] Fri, 05 October 2007 15:24 Go to next message
clintonf
Messages: 82
Registered: May 2006
Member
Hi all,

Are multiple 'OF' clauses allowed in trigger definitions?

I would like to create a trigger that will fire on the insert/update of one of several different fields. The fields are all somewhat related so it makes sense intuitively to have one trigger.

For example, my database has a table called samples. Solid samples have their masses stored in a field called weight and liquid samples have their volumes stored in a field called volume. The two are mutually exclusive in this case.

Masses are only stored in grams and volumes are only stored in litres but the database allows inserts in mg, mL, etc (enter triggers). Thus I want to a create one trigger that will automatically scale masses or volumes to grams and litres. Something like:

CREATE OR REPLACE trigger unitConverter
BEFORE insert OR update
OF weight OR liquid
ON samples
FOR EACH ROW
BEGIN
.....
END;


The syntax that I have used above is incorrect.

Is there a proper syntax to supply multiple 'OF' clauses?

Thanks!
Re: Multiple 'OF' clauses in a trigger? [message #272652 is a reply to message #272647] Fri, 05 October 2007 16:07 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

Calling a Procedure in a Trigger Body: Example You could create the salary_check trigger described in the preceding example by calling a procedure instead of providing the trigger body in a PL/SQL block. Assume you have defined a procedure check_sal in the hr schema, which verifies that an employee's salary is in an appropriate range. Then you could create the trigger salary_check as follows:

CREATE TRIGGER salary_check
BEFORE INSERT OR UPDATE OF salary, job_id ON employees
FOR EACH ROW
WHEN (new.job_id <> 'AD_VP')
CALL check_sal(:new.job_id, :new.salary, :new.last_name)


The procedure check_sal could be implemented in PL/SQL, C, or Java. Also, you can specify :OLD values in the CALL clause instead of :NEW values.




Above text is a copy paste from Oracle Sql reference manual.

Regards

Raj
Re: Multiple 'OF' clauses in a trigger? [message #273265 is a reply to message #272652] Tue, 09 October 2007 15:18 Go to previous messageGo to next message
clintonf
Messages: 82
Registered: May 2006
Member
I can't believe that I didn't try that... Embarassed

Thanks!
Re: Multiple 'OF' clauses in a trigger? [message #273325 is a reply to message #273265] Tue, 09 October 2007 23:59 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
clintonf wrote on Tue, 09 October 2007 22:18

I can't believe that I didn't try that... Embarassed


I hope you mean "search the documentation" with that..
Previous Topic: USING IN with Variable
Next Topic: Get the maximum value per row
Goto Forum:
  


Current Time: Thu Dec 08 10:27:36 CST 2016

Total time taken to generate the page: 0.11862 seconds