Home » Developer & Programmer » Forms » procedure in oracle trigger
procedure in oracle trigger [message #405167] Tue, 26 May 2009 13:01 Go to next message
Nish1
Messages: 4
Registered: May 2009
Junior Member
Hi all,

I am wondering how i can create a trigger.

I seem to be able to create simple triggers, but when it comes to inputting a function into a trigger, it does not work.

i have created a function to return business days only
workdays_between2 (date1, date2)
which returns a number value of how many business days exist between date1 and date2.

Can anyone help me to create a trigger in which the value the function returns should be entered to.

Any help is appreciated.

Thank you.

Re: procedure in oracle trigger [message #405168 is a reply to message #405167] Tue, 26 May 2009 13:06 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Post your code.
Read the Forum Guide.

By
Vamsi
Re: procedure in oracle trigger [message #405169 is a reply to message #405167] Tue, 26 May 2009 13:17 Go to previous messageGo to next message
Nish1
Messages: 4
Registered: May 2009
Junior Member
i created a simple triggers to test.

--Test1
BEGIN
if :new.Date1 is not null then --Date1, Date2 are DATE
:new.Date2:= :new.Date1;
end if;
end;

--Test2
BEGIN
if :new.Date1 is not null then
:new.count1:=:new.Date1 - :new.Date2; --Date1, Date2 are DATE
end if; --count1 is NUMBER
end;

Test1 and Test2 works.

the way i'm inputting my function in there is,
----------------------------------------------------
select workdays_between2 (Date2, Date1)
FROM TABLE1

:new.count1 := workdays_between2(:TABLE1.DATE2, :TABLE1.DATE1) --where Date1 and Date2 are DATE and returns a value NUMBER.

--------------------------------
Thank you.
Re: procedure in oracle trigger [message #405172 is a reply to message #405169] Tue, 26 May 2009 13:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a working Test case: create table and so on. What you poste is meaningless.
Quote:
i created a simple triggers to test.

And what does the test gives?

Quote:
Can anyone help me to create a trigger in which the value the function returns should be entered to.

A trigger on what?

Regards
Michel
Re: procedure in oracle trigger [message #405173 is a reply to message #405167] Tue, 26 May 2009 13:31 Go to previous messageGo to next message
Nish1
Messages: 4
Registered: May 2009
Junior Member
sorry.

I'm creating a trigger so that when a user clicks on the save button in an application, the field populate with the business day value.

Test1 puts in the value in Date1 to Date2. I've never created a trigger before. I just wanted to make sure if i can and if it works. so, when i click on the save button on the application, the date in DATE1 field is automatically inputted into DATE2 field.

but, i will need to call a function within the trigger in order to output a value in a number field.

Table Name: TABLE1
Variable in TABLE1: DATE1 Date, DATE2 Date, count1 Number

The following function returns the business days between Date1 and Date2.
workdays_between2(:TABLE1.DATE2, :TABLE1.DATE1)
Re: procedure in oracle trigger [message #405174 is a reply to message #405167] Tue, 26 May 2009 13:38 Go to previous messageGo to next message
Nish1
Messages: 4
Registered: May 2009
Junior Member
Oracle: Version 9.2.0.1.0
Re: procedure in oracle trigger [message #405182 is a reply to message #405174] Tue, 26 May 2009 15:12 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
We are talking Forms triggers here, right?

As you've said that you'd like to populate some fields when user clicks the "save" button, it might be the KEY-COMMIT trigger. You'd simply put your code into it and - at the end - commit changes you've made. Something like this:
-- This is the KEY-COMMIT trigger

if :new.Date1 is not null then
   :new.Date2 := :new.Date1;
end if;

:new.count1 := workdays_between2(:new.DATE2, :new.DATE1);

commit;

":new" represents name of the data block; if you want to save those values, this block should be based on a table, and "date2" as well as "count1" should be database items, mapped to corresponding columns in the table.

Though: where's the point in IF-THEN-ELSE here? What will you do if ":new.date1" is null? What will be ":new.date2" value then? Also, if we suppose that ":new.date1" is not null then both ":new.date1" and ":new.date2" are equal and "workdays_between" (whatever it does) will return 0 (zero).

Does the above help? If not, could you explain it once again?
Previous Topic: frm-40737 illegal restricted procedure first_record next_record
Next Topic: how make pass word
Goto Forum:
  


Current Time: Sun Dec 11 06:22:29 CST 2016

Total time taken to generate the page: 0.08429 seconds