June 20, 2004 at 12:55 am
HI,
I have two tables named as table1 and table2.
In Table2 i have 2 columns one is sno and another one is email2
In table2 i inserted the values through admin module.
In table1 i have columns like id,name,address,email1,refno etc.
When iam inserting the values in table1 programatically i need to check the values email1=email2. First time i have to insert the sno(in table2) as refno in table1. From second time onwards i have to insert the value as refno = max(refno) + 1...
So Please help me how to write the trigger.
Thanx and Regards
Balu
June 21, 2004 at 12:45 am
This is a bit confusing and needs a bit more info. Are you saying that every time you create a record in table1, you need to create an associated record in table2, with a link between table1.refno and table2.sno? If so, is table2.sno the PK and an IDENTITY field?
Please describe the process in a bit more detail & then we'll be able to help I'm sure.
Regards.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 21, 2004 at 12:56 am
in table1 i have id, Sno, Email1 (id is the identity)
values like (100,'abcd@abcd.com')
In table 2 i have Id, fname,laname,email2,refno etc
here id is the primary key and identity filed.
Using Third party component except refno all the values will be inserted. At that time refno is empty. So i have to compare email2 exists in table1 or not. If exists i have to put sno as refno is table1. If not exists i will put some default value. From second time onwards refno is there for email2 so i need to increment refno as refno+1.
Thank u
June 21, 2004 at 1:10 am
So, on insert to table2, need to set t2.refno to
select refno from table1 t1
where t1.email1 = t2.email2?
(where t2 is aliased to table2)
Is table1.email1 unique? If not, obviously an issue here because the above select query would return more than 1 result in some instances.
When you say "from the second time onwards", I'm not sure what you mean. If you are inserting records, the insert can happen only once. Or are you talking about inserts and then updates at some later stage?
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply