| procedure in oracle trigger [message #405167] |
Tue, 26 May 2009 13:01  |
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 #405169 is a reply to message #405167] |
Tue, 26 May 2009 13:17  |
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 #405173 is a reply to message #405167] |
Tue, 26 May 2009 13:31 |
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 #405182 is a reply to message #405174] |
Tue, 26 May 2009 15:12 |
 |
Littlefoot
Messages: 21818
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?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
