I have a table called Customer_Career and there are ID,CusID,SponsorID,CareerID,PeriodID columns. Secondly,I have a table called Customer_Career_Period and there are ID,CusID,SponsorID,CareerID,PeriodID,Description,CareerDate columns.
if sponsor number of the customer is 15,I making 2 its career.if career of the sponsor is 2 and if to 2 equal two of them by taking number of sponsored persons, I want to do 3 to that customer.
ALTER TRIGGER [dbo].[KariyerYap] ON [dbo].[Customer_Career] AFTER INSERT
AS
Declare
@Period int,
@CareerID int,
@SponsorID int,
@CusID int,
@Sonuc int,
---------------------------
@Bronz int,
@Silver int,
@Gold int,
@Platin int,
-----------------
@SpID int,
@CarID int
BEGIN
Select @CusID=CusID,@Period=Period,@SponsorID=SponsorID,@CareerID=CareerID From inserted
Set @SONUC=(Select COUNT(*) from Customer_Career where Period=@Period and CareerID=@CareerID and SponsorID=@SponsorID)
if(@Sonuc = 15)
Begin
Set @CarID=(Select CareerID from Customer where ID=@SponsorID )
Set @SpID= (Select SponsorID From Customer Where ID=@SponsorID)
if(@CarID=1)
Begin
INSERT INTO Customer_Career_Period(CusID,SponsorID,CareerID,Period,CareerDate,Description)
Values(@SponsorID,@SpID,2,@Period,GETDATE(),'* Bronz Oldu')
Update Customer Set CareerID=2 Where ID=@SponsorID
End
End
While(@SpID = 0 )
Begin
Set @SpID= (Select SponsorID From Customer Where ID=@SponsorID)
Set @Bronz = (Select COUNT(*) From Customer_Career_Period Where SponsorID=@SpID And CareerID=2 And Period=@Period)
if(@Bronz = 2 And @Sonuc >= 15)
Begin
Set @SponsorID=(Select SponsorID From Customer Where ID=@SpID)
Update Customer Set CareerID=3 Where ID=@SpID
INSERT INTO Customer_Career_Period(CusID,SponsorID,CareerID,Period,CareerDate,Description)
Values(@SpID,@SponsorID,3,@Period,GETDATE(),'** Silver Oldu')
Set @SpID=(Select SponsorID From Customer Where ID=@SpID)
End
Set @SpID= (Select SponsorID From Customer Where ID=@SponsorID)
Set @Bronz = (Select COUNT(*) From Customer_Career_Period Where SponsorID=@SpID And CareerID=2 And Period=@Period)
if(@Bronz = 3 And @Sonuc >= 20)
Begin
Set @SponsorID=(Select SponsorID From Customer Where ID=@SpID)
Update Customer Set CareerID=4 Where ID=@SpID
INSERT INTO Customer_Career_Period(CusID,SponsorID,CareerID,Period,CareerDate,Description)
Values(@SpID,@SponsorID,@CarID,@Period,GETDATE(),'*** Gold Oldu')
Set @SpID=(Select SponsorID From Customer Where ID=@SpID)
End
Set @SpID= (Select SponsorID From Customer Where ID=@SponsorID)
if @SpID = 0 Break
End
END
