| PL/SQL problem [message #151004] |
Mon, 12 December 2005 10:08  |
oraqle
Messages: 4
Registered: December 2005
|
Junior Member |
|
|
I am trying to create a trigger on a form button which will insert the system date into a field in an associative entity (figure 1 in end date) and then use the address sequence (figure 2) to start another record having the new primary key of the address table and the pk of the employee from the employee table.Below is a structure of the tables Figure 1
EMPLOYEE TABLE
SSN L_NAME PHONE EMAIL STARTDATE
100000020 Bola 07923592340 hungey@yahoo.com 08-DEC-05
100000001 Steven 07748375647 farouqbu@gmail.com 05-DEC-05
EMP_ADDRESS TABLE
SSN ADDRESS_ID EMP_ADD_STARTDATE EMP_ADD_ENDDATE
100000001 3 05-DEC-05
100000002 1 17-JAN-05 09-DEC-05
100000003 2 02-FEB-00 18-NOV-05
100000004 4 05-DEC-05
ADDRESS TABLE
ADDRESS_ID BUILDING_NO STREET_NAME POSTCODE
1 14A King william walk SE10 9JW
2 2 Claremont close E10 3LZ
3 7E Harts lane IG11 7PW
4 128 Hyde park street W2 8PJ
CREATE SEQUENCE address_seq
START WITH 1
INCREMENT BY 2;
Upd-mod: Add 'code' tags.
[Updated on: Mon, 12 December 2005 21:14] by Moderator Report message to a moderator |
|
|
|
| Re: PL/SQL problem [message #151053 is a reply to message #151004] |
Mon, 12 December 2005 21:21  |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
I will update this post to add the code as I go along so this initial entry will be quite short.
I assume that you have the master-detail-detail working successfuly and all you wish to do is update the EMP_ADDRESS on which you are resting, create a new EMP_ADDRESS, and then the corresponding ADDRESS entry.
Is there a one-to-one relationship between EMP_ADDRESS and ADDRESS or do you intend to have the ADDRESSes as an LOV from which the user can select them?
May I suggest that we assume that there is a one-to-one relationship between EMP_ADDRESS and ADDRESS.
What we do is add BUILDING_NO, STREET_NAME, and POSTCODE to the EMP_ADDRESS block as non-database fields and populate them in the Post-Query trigger.
To terminate all addresses for an EMPLOYEE (like when they leave the organisation) you simply populate the EMP_ADD_ENDDATE.
To add a new EMPLOYEE you go to the EMP_ADDRESS and populatethe various fields, both database and non-database, an in the Post-Insert trigger perform an INSERT operation on the ADDRESS table.
To add a new EMP_ADDRESS you either mouse-click the 'Insert' icon or press the 'down' key to go to a new line. In the When-Create-Record trigger you should test to make sure that the EMP_ADD_ENDDATE of all EMP_ADDRESS records for this EMPLOYEE are NOT NULL. Do a 'POST' before running your 'CURSOR'. Also get the 'MAX' EMP_ADD_ENDDATE as we going to put it into the EMP_ADD_STARTDATE of the new EMP_ADDRESS record. Whether or not the operator can make the date further into the future is up to you. You also fire your sequence number to get the next ADDRESS_ID.
QUESTION: Can an employee have two 'open' address records?
Now, in the Post-Insert trigger of the EMP_ADDRESS block you take the ADDRESS_ID, BUILDING_NO, STREET_NAME, and POSTCODE from EMP_ADDRESS and use it to run an 'INSERT' statement on the ADDRESS table.
Hope this makes sense. If not, please reread it, word by word, action by action, it WILL work.
If you have other questions please ANSWER the questions that I have posted here.
David
[Updated on: Mon, 12 December 2005 22:09] Report message to a moderator |
|
|
|
| Re: PL/SQL problem [message #151136 is a reply to message #151053] |
Tue, 13 December 2005 07:22 |
oraqle
Messages: 4
Registered: December 2005
|
Junior Member |
|
|
Thanks David,
here is whats happening,
Yes, the master detail from is working perfectly
Yes, im trying to update the emp_address field by inserting an endate as the system date not touching the address table. a new address table is loaded which automatically has the primary keys of employee and address as foreign keys.
Employee address is an associative table as a result of a many to many relationship btw employee and addresss made so i can have a historical record (startdate, endate) of where an employee has lived through his stay.
Answer to question
An employee can have a lot of addresses associated to him, however after using the fk's to determine which addresses are associated to him, a query based on the emp_address.endate being null is used to determine which one is his/her current address.
Thank you once again i hope this helps
|
|
|
|
| Re: PL/SQL problem [message #151137 is a reply to message #151136] |
Tue, 13 December 2005 07:25 |
oraqle
Messages: 4
Registered: December 2005
|
Junior Member |
|
|
correction to second question
Yes, im trying to update the emp_address field by inserting an endate as the system date not touching the address table. a new address table is loaded from which the primary keys of employee and address are foreign keys in the emp_address table.
|
|
|
|
|
|
| Re: PL/SQL problem [message #151396 is a reply to message #151223] |
Wed, 14 December 2005 11:23 |
oraqle
Messages: 4
Registered: December 2005
|
Junior Member |
|
|
David i was able to do it in a different longer manner and what i did was get the user to put in an end date and when its saved it automatically loads a new record for the user to fill taking the enddate just filled as the new startdate.
Anyway thanks again for the help as my work is due today ill have to make do with that.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
