--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[pair_dtl](
[c_code] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[jc_code] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[tdate] [smalldatetime] NULL,
[Level] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
script for CMPL:-
--------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[cmpl](
[m_group] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[join_date] [smalldatetime] NULL,
[c_code] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[c_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sp_code] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sp_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[flag] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
--------------------------------------------------------------------------------------------------------------
Triggers
Following Trigger are applied on CMPL table
--------------------------------------------------------------------------------------------------------------
This Trigger fires at the time of Insert Operation
------------------------------------------------
CREATE TRIGGER [dbo].[RahulTriger1] ON [dbo].[cmpl]
FOR Insert
AS
DECLARE @MaxID VARCHAR(50)
DECLARE @SPID VARCHAR(50)
DECLARE @PlacID VARCHAR(50)
DECLARE @MembID VARCHAR(50)
DECLARE @Plac VARCHAR(50)
DECLARE @RegDt smalldatetime
DECLARE @gDt smalldatetime
DECLARE @JC_CODE VARCHAR(50)
DECLARE @JOININGTYPE VARCHAR(50)
DECLARE @VR Numeric
DECLARE @FLAG VARCHAR(50)
DECLARE @LEVEL VARCHAR(50)
BEGIN
SET @FLAG='N'
select @RegDt=JOIN_DATE,@SPID=SP_CODE,@MaxID=C_CODE ,@JOININGTYPE=M_GROUP,@FLAG=FLAG from CMPL WHERE FLAG='Y'
IF @FLAG='Y'
BEGIN
DELETE FROM PAIR_DTL WHERE JC_CODE=@MaxID
UPDATE CMPL SET FLAG='N' WHERE C_CODE=@MaxID
SET @MembId = @MaxId
SET @JC_CODE = @MaxId
SET @LEVEL=1
WHILE @MembId<>'CM00000' /* At place of 'CM00000' you can use code you created
The code 'CM00000' is the top most code of Application */
BEGIN
select @PlacID=SP_CODE from CMPL where C_CODE=@MembId
INSERT INTO PAIR_DTL(JC_CODE,C_CODE,TDATE,Level)VALUES(@JC_CODE , @PlacID ,@RegDt,@LEVEL)
SET @MembId = @PlacID
SET @LEVEL=@LEVEL+1
END
END
END
--------------------------------------------------------------------------------------------------------------
This Trigger fires at the time of Insert Operation
------------------------------------------------
CREATE TRIGGER [dbo].[RahulTriger2] ON [dbo].[cmpl]
FOR UPDATE
AS
DECLARE @MaxID VARCHAR(50)
DECLARE @SPID VARCHAR(50)
DECLARE @PlacID VARCHAR(50)
DECLARE @MembID VARCHAR(50)
DECLARE @Plac VARCHAR(50)
DECLARE @RegDt smalldatetime
DECLARE @gDt smalldatetime
DECLARE @JC_CODE VARCHAR(50)
DECLARE @JOININGTYPE VARCHAR(50)
DECLARE @VR Numeric
DECLARE @FLAG VARCHAR(50)
DECLARE @LEVEL VARCHAR(50)
BEGIN
SET @FLAG='N'
select @RegDt=JOIN_DATE,@SPID=SP_CODE,@MaxID=C_CODE ,@JOININGTYPE=M_GROUP,@FLAG=FLAG from CMPL WHERE FLAG='Y'
IF @FLAG='Y'
BEGIN
DELETE FROM PAIR_DTL WHERE JC_CODE=@MaxID
UPDATE CMPL SET FLAG='N' WHERE C_CODE=@MaxID
SET @MembId = @MaxId
SET @JC_CODE = @MaxId
SET @LEVEL=1
WHILE @MembId<>'CM00000' /* At place of 'CM00000' you can use code you created
The code 'CM00000' is the top most code of Application */
BEGIN
select @PlacID=SP_CODE from CMPL where C_CODE=@MembId
INSERT INTO PAIR_DTL(JC_CODE,C_CODE,TDATE)VALUES(@JC_CODE , @PlacID ,@RegDt)
SET @MembId = @PlacID
SET @LEVEL=@LEVEL+1
END
END
END
--------------------------------------------------------------------------------------------------------------
The Following are the Queries use to show the effects of triggers
First insert into table cmpl entries
1. insert into CMPL(m_group,join_date,c_code,c_name,sp_code,sp_name,flag) values('CM','2012-11-24 00:00:00
','CM00000','ABC company','CM00000000','ABC Company','');
2. insert into CMPL(m_group,join_date,c_code,c_name,sp_code,sp_name,flag) values('FR','2012-11-24 00:00:00
','FR00001','ABC Kumar','CM000000','ABC Company','');
3. insert into CMPL(m_group,join_date,c_code,c_name,sp_code,sp_name,flag) values('EX','2012-11-24 00:00:00
','EX00001','ABC Kumar','FR00001','ABC Kumar','Y');
4. insert into CMPL(m_group,join_date,c_code,c_name,sp_code,sp_name,flag) values('EX','2012-11-24 00:00:00
','EX00002','ABC Kumar','EX00001','ABC Company','Y');
Then fire the querie in sql server
--------------------------------------------------------------------------------------------------------------
UPDATE CMPL SET FLAG='Y' WHERE C_CODE='EX00001'
SELECT * FROM PAIR_DTL
--------------------------------------------------------------------------------------------------------------
Conclusion
These Triggers specially use to get the downline members or uplink members in tree Application