Home » SQL & PL/SQL » SQL & PL/SQL » Insert New Line in Database Trigger
icon10.gif  Insert New Line in Database Trigger [message #186300] Mon, 07 August 2006 06:38 Go to next message
saphr
Messages: 2
Registered: August 2006
Location: Cairo
Junior Member
Hello All
I've database trigger before insert or update for each row
and i've function called get_cond return varchar2
eg.(:new.gross:=nvl(:new.salary ,'0') + nvl(:new.bonus,'0')Wink
and
i want to call this function through this trigger to calculate the gross salary
i tried to write
get_cond;
and tried also
DBMS_OUTPUT.PUT_LINE(GET_COND);
but no way to do this
plz help me
best regards

Re: Insert New Line in Database Trigger [message #186303 is a reply to message #186300] Mon, 07 August 2006 06:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The :new and :old syntax can only be used in triggers, not in PL/SQL called by triggers.

You will need to write a function like this:
CREATE OR REPLACE FUNCTION get_cond (p_salary in number, p_bonus in  number) RETURN number IS
BEGIN
<lots of code to calculate the gross salary>
END get_cond;

Then in your trigger, you write
BEGIN
:new.gross_salary := get_cond(:new.salary, :new.bonus);
END;
Re: Insert New Line in Database Trigger [message #186310 is a reply to message #186303] Mon, 07 August 2006 07:29 Go to previous messageGo to next message
saphr
Messages: 2
Registered: August 2006
Location: Cairo
Junior Member
This Get_cond Function return varchar2
because i stored the calculation formula in other table ,
what i need that
in the trigger i want to write the following
:new.gross:=get_cond;
that is mean
:new.gross:=nvl(........the text wrote in the function);


Re: Insert New Line in Database Trigger [message #186317 is a reply to message #186310] Mon, 07 August 2006 08:06 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:

in the trigger i want to write the following
:new.gross:=get_cond;


I'm afraid you can't do that. The GET_COND function needs to know what the new SALARY and BONUS values are, and because you are doing this processing in a trigger, you cannot perform a SELECT on the table to get them.
Therefore you will have to explicitly pass the SALARY and BONUS values into the function, in the manner I showed you.

I saw that your function returned a VARCHAR2, but if you are returning a numeric value, then the correct data type to use is NUMBER (or one of its subtypes).

Previous Topic: How to use DBMS_LOB package ..Please help
Next Topic: Datatype Conversion
Goto Forum:
  


Current Time: Sun Dec 11 06:05:50 CST 2016

Total time taken to generate the page: 0.16001 seconds