create t1(pid int,subpid int,quantity int)
subpid 为推荐人
create trigger trig1 on T1
for insert
as
declare @t Table(Pid int,SubPid int,level2 int)
declare @m int,@n int,@SubPid int,@Pid int
set @m=1
select @Pid=Pid,@subPid=SubPid from inserted
if @SubPid>0
insert @t select Pid,subPid,@m from T1 where Pid=@SubPid
while @@rowcount>0
begin
set @m=@m+1
if @m<6
insert @t select Pid,SubPid,@m from T1 where Pid in(select subPid from @t where level2=@m-1)
end
update T1 set Quantity=Quantity+1 where Pid in (select Pid from @t)
create table t
(id int,j_id int,姓名 varchar(20),积分 int)
/*---只是初试数据,所以没有对积分进行考虑,只是考虑每插入一条数据,那么积分相应变化
--*/
insert t
select 1,0,'刘八',0
go
/*------------建立触发器-----------------*/
create trigger t_go
on t
for insert
as
set xact_abort on
begin tran
declare @t table (id int,j_id int,姓名 varchar(20),积分 int,level int)
declare @t1 table(id int,j_id int,姓名 varchar(20),积分 int)
declare @i int,@k int
insert @t1
select * from inserted
select @k=count(*) from @t1
while @k>0
begin
select @i=1
insert @t
select top 1 *,@i from @t1 order by id asc
delete from @t1 where id = (select top 1 id from @t1 order by id asc)
while exists (select * from t a,@t b where a.id=b.j_id and b.level=@i and @i<=5)
begin
set @i=@i+1
insert @t
select a.id,a.j_id,a.姓名,a.积分+1,@i from t a,@t b where a.id=b.j_id and b.level=@i-1
end
update t set 积分=b.积分
from t a,@t b where a.id=b.id
delete from @t
set @k=@k-1
end
commit tran
go
insert t
select 2,1,'李四',0 union all
select 3,2,'冯七',0 union all
select 4,3,'赵九',0 union all
select 5,4,'刘十',0 union all
select 6,5,'王五',0 union all
select 7,6,'周六',0 union all
select 8,3,'胡三',0 union all
select 9,8,'苟一',0
/*-----------显示结果---------------*/
select * from t
insert t
select 11,10,'123',0
select * from t
/*-----------删除环境---------------*/
drop trigger t_go
drop table t
/*--------建立环境---------*/
create table t
(id int,j_id int,姓名 varchar(20),积分 int)
/*---只是初试数据,所以没有对积分进行考虑,只是考虑每插入一条数据,那么积分相应变化
--*/
insert t
select 1,0,'刘八',0 union all
select 2,1,'李四',0 union all
select 3,2,'冯七',0 union all
select 4,3,'赵九',0 union all
select 5,4,'刘十',0 union all
select 6,5,'王五',0
/*------------该表的作用等同于触发器中的inserted,此时表示触发器中传入三条数据------------------*/
create table t1
(id int,j_id int,姓名 varchar(20),积分 int)
insert t1
select 7,6,'周六',0 union all
select 8,3,'胡三',0 union all
select 9,4,'苟一',0
/*---------------开始程序,你可以该成触发器就OK------------------——*/
declare @t table (id int,j_id int,姓名 varchar(20),积分 int,level int)
declare @i int,@k int
select @k=count(*) from t1
while @k>0
begin
select @i=1
insert @t
select top 1 *,@i from t1 order by id asc
insert t
select top 1 * from t1 order by id asc
delete from t1 where id = (select top 1 id from t1 order by id asc)
while exists (select * from t a,@t b where a.id=b.j_id and b.level=@i and @i<=5)
begin
set @i=@i+1
insert @t
select a.id,a.j_id,a.姓名,a.积分+1,@i from t a,@t b where a.id=b.j_id and b.level=@i-1
end
update t set 积分=b.积分
from t a,@t b where a.id=b.id
delete from @t
set @k=@k-1
end
/*-----------显示结果---------------*/
select * from t
/*-----------删除环境---------------*/
drop table t
drop table t1