| How to Set A Default Record [message #90605] |
Wed, 12 May 2004 06:21  |
rajesh
Messages: 173
Registered: November 1998
|
Senior Member |
|
|
Hi All
I have a problem that I am describing below.
I have one Table say Emp1 wherein EmpID is defined as the Primary Key.I have a second table say Phone which has EmpId as the Foreign Key.Now One Employee can have several Phones so that Emp--[>]Phone relationship is 1--[>]Many.
But I have another field in Phone called DEFAULT.Now I want to ensure that at any time there is always one Phone record that has its DEFAULT field set to TRUE and all others should be FALSE.So if i set one phone record DEFAULT value as TRUE all other Phone Records for that Employee should become FALSE.
Does Oracle give any way so that I can define this restriction in the schema itself.
Thanks
Raj
|
|
|
|
| Re: How to Set A Default Record [message #90606 is a reply to message #90605] |
Wed, 12 May 2004 11:12  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
No, you need to do this programatically. Doing it in your application's code will be the easiest. If you try to do it using triggers on "Phone" it will be a lot more difficult because you will have a mutating table(you'll typically need a pre trigger, a post trigger and a package to pass global info from the pre to the post).
Try something like this:
if v_default = 'Y' then
-- reset other rows to 'N'
update phone set default = 'N'
where emp_id = 123
and default = 'Y';
end if;
-- now insert/update New record default = 'Y' where emp_id = 123;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
