Trigger does not affect the code written in the loop... pls help its urgent

[Tian]

I want to create a trigger wherein with every insert on paymentdetails table the individual commission should be calculated for a particular agent based on his rank and the premium amt. At the same time group commission should be calculated for the subsequent Introducers in the hierarchy and all agents & Introducers commission should be stored in AgentComm table. I hav written d trigger for it but it gives me only the agents commission and not the introducers and the introducer's introducer details.. Can someone please solve this query.
Following are my tables and the code which I have written



create table paymentdetails
(MemberNo int,
LicenseNo int,
PremiumAmt money)

create table Agent
(LicenseNo int,
IntroducersLicNo int,
Rank int
)

create table Rank
(
Rank int,
IComm float,
GComm float
)

create table AgentComm
(
LicenseNo int,
MemberNo int,
Commission money
)

create trigger comm on paymentdetails
for insert
as
declare @LicNo int
declare @NextLicNo int
declare @MemberNo int
declare @Premium int
declare @Introducer int
declare @Rank int
declare @Comm float
declare @CalcComm money

declare @IntLic int
declare @IntRank int
declare @IntIntroducer int
declare @IntComm float
declare @CalculateComm money

select @LicNo= (select LicenseNo from inserted)
select @MemberNo=(select MemberNo from inserted)
select @Premium=(select PremiumAmt from inserted)
select @Rank=(select Rank from Agent where LicenseNo=@LicNo)
select @Comm=(select (IComm/100) from Rank where Rank=@Rank)
select @Introducer=(select IntroducersLicNo from Agent where LicenseNo=@LicNo)
select @CalcComm=@Comm*@Premium

insert into AgentComm (LicenseNo,MemberNo,Commission)
values (@LicNo,@MemberNo,@CalcComm)

select @NextLicNo=@Introducer

while(@NextLicNo<>NULL)
begin
select @IntLic=(select LicenseNo from Agent where LicenseNo=@NextLicNo)
select @IntIntroducer=(select IntroducersLicNo from Agent where LicenseNo=@IntLic)
select @IntRank=(select Rank from Agent where LicenseNo=@IntLic)
select @IntComm=(select (GComm/100) from Rank where Rank=@IntRank)
select @CalculateComm=@IntComm*@Premium

insert into AgentComm (LicenseNo,MemberNo,Commission)
values (@IntLic,@MemberNo,@CalculateComm)

select @NextLicNo=@IntIntroducer
end

[Tian]

I want to create a trigger wherein with every insert on paymentdetails table the individual commission should be calculated for a particular agent based on his rank and the premium amt. At the same time group commission should be calculated for the subsequent Introducers in the hierarchy and all agents & Introducers commission should be stored in AgentComm table. I hav written d trigger for it but it gives me only the agents commission and not the introducers and the introducer's introducer details.. Can someone please solve this query.
Following are my tables and the code which I have written



create table paymentdetails
(MemberNo int,
LicenseNo int,
PremiumAmt money)

create table Agent
(LicenseNo int,
IntroducersLicNo int,
Rank int
)

create table Rank
(
Rank int,
IComm float,
GComm float
)

create table AgentComm
(
LicenseNo int,
MemberNo int,
Commission money
)

create trigger comm on paymentdetails
for insert
as
declare @LicNo int
declare @NextLicNo int
declare @MemberNo int
declare @Premium int
declare @Introducer int
declare @Rank int
declare @Comm float
declare @CalcComm money

declare @IntLic int
declare @IntRank int
declare @IntIntroducer int
declare @IntComm float
declare @CalculateComm money

select @LicNo= (select LicenseNo from inserted)
select @MemberNo=(select MemberNo from inserted)
select @Premium=(select PremiumAmt from inserted)
select @Rank=(select Rank from Agent where LicenseNo=@LicNo)
select @Comm=(select (IComm/100) from Rank where Rank=@Rank)
select @Introducer=(select IntroducersLicNo from Agent where LicenseNo=@LicNo)
select @CalcComm=@Comm*@Premium

insert into AgentComm (LicenseNo,MemberNo,Commission)
values (@LicNo,@MemberNo,@CalcComm)

select @NextLicNo=@Introducer

while(@NextLicNo<>NULL)
begin
select @IntLic=(select LicenseNo from Agent where LicenseNo=@NextLicNo)
select @IntIntroducer=(select IntroducersLicNo from Agent where LicenseNo=@IntLic)
select @IntRank=(select Rank from Agent where LicenseNo=@IntLic)
select @IntComm=(select (GComm/100) from Rank where Rank=@IntRank)
select @CalculateComm=@IntComm*@Premium

insert into AgentComm (LicenseNo,MemberNo,Commission)
values (@IntLic,@MemberNo,@CalculateComm)

select @NextLicNo=@IntIntroducer
end

Please help..Its vry urgent

[reon]

select @LicNo= (select LicenseNo from inserted)

the above select u want to replace it with set that is the mistake...
select means you are just selecting from that inserted table but set means you are setting it to some other values......
replace that select with set ..ok

I want to create a trigger wherein with every insert on paymentdetails table the individual commission should be calculated for a particular agent based on his rank and the premium amt. At the same time group commission should be calculated for the subsequent Introducers in the hierarchy and all agents & Introducers commission should be stored in AgentComm table. I hav written d trigger for it but it gives me only the agents commission and not the introducers and the introducer's introducer details.. Can someone please solve this query.
Following are my tables and the code which I have written



create table paymentdetails
(MemberNo int,
LicenseNo int,
PremiumAmt money)

create table Agent
(LicenseNo int,
IntroducersLicNo int,
Rank int
)

create table Rank
(
Rank int,
IComm float,
GComm float
)

create table AgentComm
(
LicenseNo int,
MemberNo int,
Commission money
)

create trigger comm on paymentdetails
for insert
as
declare @LicNo int
declare @NextLicNo int
declare @MemberNo int
declare @Premium int
declare @Introducer int
declare @Rank int
declare @Comm float
declare @CalcComm money

declare @IntLic int
declare @IntRank int
declare @IntIntroducer int
declare @IntComm float
declare @CalculateComm money

select @LicNo= (select LicenseNo from inserted)
select @MemberNo=(select MemberNo from inserted)
select @Premium=(select PremiumAmt from inserted)
select @Rank=(select Rank from Agent where LicenseNo=@LicNo)
select @Comm=(select (IComm/100) from Rank where Rank=@Rank)
select @Introducer=(select IntroducersLicNo from Agent where LicenseNo=@LicNo)
select @CalcComm=@Comm*@Premium

insert into AgentComm (LicenseNo,MemberNo,Commission)
values (@LicNo,@MemberNo,@CalcComm)

select @NextLicNo=@Introducer

while(@NextLicNo<>NULL)
begin
select @IntLic=(select LicenseNo from Agent where LicenseNo=@NextLicNo)
select @IntIntroducer=(select IntroducersLicNo from Agent where LicenseNo=@IntLic)
select @IntRank=(select Rank from Agent where LicenseNo=@IntLic)
select @IntComm=(select (GComm/100) from Rank where Rank=@IntRank)
select @CalculateComm=@IntComm*@Premium

insert into AgentComm (LicenseNo,MemberNo,Commission)
values (@IntLic,@MemberNo,@CalculateComm)

select @NextLicNo=@IntIntroducer
end