insert sysdate into field on create record 1

[robdunfey]

Hi,

I have a Form with a field called app_date. When a new record is created I want the current date to be inserted into this field, and then ideally locvked so it cant be changed in future.

However, i am currentely stuck just trying to insert the date into the field.

I have the following PL/SQL behind 'WHEN-CREATE-RECORD':

DECLARE
BEGIN
:applicant.app_date := sysdate;
END;

applicant is the name of the datablock/underlying table. When I compile I dotn get any errors, but as said, it doesnt work. If anyone has any ideas, your comments rae much appreciated.

Rob

 

[chochoo]

hey u can do this:
alter your table by giving sysdate default to your date field and hide the date item in your form. it will automatically insert the sysdate in your table and no body can change that.
--
alter table applicant add (app_dat date DEFAULT sysdate);
--
hope this would solve your problem

 

[robdunfey]

chochoo-

cheers. that great, i will do that. however, how would i set field values using form triggers?

many thanks,

rob

 

[lewisp]

In a text item that is a date type, use $$DATE$$ 0r $$DATETIME$$ in the Default Value property of the item.

If you dont want to display the item you could populate it in a PRE-INSERT trigger.

 

[robdunfey]

lewisp-

thanks, but i cant populate anything from my triggers and i dont know why. i cant see why the above code doesnt work.

many thanks,

rob

 

[lewisp]

At what level have you created the WHEN-RECORD-CREATED trigger?

I stongly advise against this because if you navigate to an empty block, or an empty record, the form will think that changes have been made even if you have entered no data. This will give you the 'do you want to save the changes you have made' message when you have done nothing.

Using the Default Value property does not give you this problem.

 

[BJCooperIT]

Try putting your code in the PRE-INSERT trigger.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[robdunfey]

BJCooperIT-

When I right click on my text box and select smart triggers>>other PRE-INSERT isn't in the list of triggers?

I am really baffelled as to why my code my code doesnt work.

rob

 

[lewisp]

What level have you created the WHEN-RECORD-CREATED at? PRE-INSERT is only available at block or form level, so you wont see it in the list of smart triggers at item level.

 

[chochoo]

hi rob
i think there is a problem in the code u are using. can u plz explain your code with the trigger accociated with that to find what the exact problem u r going to have?

 

[robdunfey]

Guys,

Thanks for all your help. Unfortunatelly I still have the same problem?! It is such a simple thing I am sure!

I have a form and the following code is associated with the Pre-Insert Trigger. Basically I want to count the number of applicant already entered for a year, then I want to add this number to the year of their application. Eg. All applicant ID's for this year will start 04, all next year will start 05, so the applicant# (which is really a varchar2 field) will look something like:

04001
04002
04003
05001
05002
06001
06002
06003
06004

I havce tried various variation of this code but no luck. This is the most explicit. I cant believe I need to declare three variables to do this!

Any further help would be much appreciated.

Kind Regards,

Rob

DECLARE

date_yy CHAR;
numOfApps number;
numOfApps_AsChar CHAR;

BEGIN


date_yy := TO_CHAR(SYSDATE, 'YY');


select COUNT(APPLICANT#) INTO numOfApps
from APPLICANT where APPLICANT# like '%'|| TO_CHAR(SYSDATE, 'YY');

numOfApps_AsChar := to_char(numOfApps);

:APPLICANT.APPLICANT# := date_yy || numOfApps_AsChar;

END;

 

[BJCooperIT]

This method requires no variables:

select to_char(SYSDATE,'YY') ||
       to_char(COUNT(APPLICANT#),'FM099')
into   :APPLICANT.APPLICANT#
from   APPLICANT 
where  APPLICANT# like '%'|| TO_CHAR(SYSDATE, 'YY');

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8i & 9i - Windows 2000

 

[sem]

Another opinion is to suggest you to redesign your application and not use such not quite obvious considerations further
BTW, do you need a year at the beginning or at the end of your complex identifier? I suspect that you (as well as BJCooperIT who followed your example than question text) might put '%' to the wrong place.

And at last generating sequential record numbers in Oracle is quite complex thing: it needs "single-user mode" approach with explicit locking and in general is not encouraged.

Regards, Dima

http://seminihin.narod.ru

 

[BJCooperIT]

Good catch Dima. This might be the "right" way based on your design:

where  APPLICANT# like TO_CHAR(SYSDATE,'YY') || '%';

While the idea of concatenated keys such as this is now considered poor design, I just considered that this might be a legacy system which had to be dealt with rather than redesigned.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8i & 9i - Windows 2000

 

[robdunfey]

BJCooper,

Thanks for the reply. I see that how your codde is tidier without the variables. However, I still cant get this to work. I get an error when I compile the code saying,

Error 307 Too many declarations of 'TO_CHAR' match this call?

I have modified your code sligtly as I now get the two digit year from a field, opposed to the SYSDATE.

SELECT TO_CHAR(:APPLICANT.INTENDED_ENROL_DATE,'YY')||TO_CHAR(MAX(APPLICANT#), 'FM099')
INTO :APPLICANT.APPLICANT# from APPLICANT
where APPLICANT#
like TO_CHAR(:APPLICANT.INTENDED_ENROL_DATE,'YY')||'%';

Any further suggestions, as always would be much appreciated,

Rob

 

[BJCooperIT]

Is APPLICANT# a numeric column? If not, you would receive this error. In your earlier example you were selecting a COUNT, not a MAX.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8i & 9i - Windows 2000

 

[robdunfey]

BJCooperIT,

Thanks for your help. Yes it is a varchar2 column. I have decided to go with just a normal sequence. I think it is untidy as you end up with an array of numbers with little meaning, except the fact they are unique, but I guess this makes more sense to a database?!

Thanks Again,

Rob