34,838
社区成员




drop table a,b,ab
drop proc triab
go
create table a(id int,jianhao varchar(10),shuliang smallint,jiaohuoqi datetime,yes bit,remarks varchar(50))
insert into a select 1,'a',10,'2008-1-14',0,null
insert into a select 2,'b',15,'2008-1-15',0,null
insert into a select 3,'a',8,'2008-1-16',0,null
create table b(id int,jianhao varchar(10),shuliang int,oddshuliang int,riqi datetime)
create table ab(jianhao varchar(10))
go
create proc triab
as
begin
declare @t table(id int,jh varchar(10),sl int,sy int,yy int,rq datetime)
declare @z table(id int,jh varchar(10),sl int,rq datetime,bz varchar(50),yes bit)
insert @t select id,jianhao,shuliang,shuliang,0,riqi from b where jianhao in (select jianhao from ab) order by riqi
declare @id int,@jh varchar(10),@sl int,@rq datetime,@sy int,@bz varchar(50),@yes bit,@jh1 varchar(10),@c int
set @jh1 = ''
DECLARE triab_cursor CURSOR FOR
SELECT id,jianhao,shuliang,jiaohuoqi from a where jianhao in(select jianhao from ab) order by jianhao,jiaohuoqi
OPEN triab_cursor
FETCH NEXT FROM triab_cursor INTO @id,@jh,@sl,@rq
WHILE @@FETCH_STATUS = 0
BEGIN
if @jh1 <> @jh or @c = 0
begin
if @jh1 <> @jh
begin
set @jh1 = @jh
set @c = 0
end
update @t set yy = 0
set @sy = 0
select @sy = sum(sy) from @t where jh = @jh
if(@sy >@sl)
begin
set @sy = @sl
update @t set @sy = @sy - sy,yy = @sy where sy > 0 and jh = @jh
update @t set yy = sy,sy = 0 where yy > 0 and jh = @jh
update @t set yy = sy+yy,sy = 0 - yy where yy < 0 and jh = @jh
update @t set sy = sl ,yy = 0 where yy < 0 and jh = @jh
set @yes = 1
end
else
begin
set @c = 1
if (@sy = @sl)
begin
update @t set yy = sy,sy = 0 where sy> 0 and jh = @jh
set @yes = 1
end
if (@sy < @sl)
begin
update @t set yy = sy where sy> 0 and jh = @jh
set @yes = 0
end
end
set @bz = ''
select @bz = @bz + convert(varchar(10),rq,120)+'送交 '+ rtrim(yy) +'件 ' from @t where yy > 0
insert @z select @id,@jh,@sl,@rq,@bz,@yes
end
FETCH NEXT FROM triab_cursor INTO @id,@jh,@sl,@rq
END
CLOSE triab_cursor
DEALLOCATE triab_cursor
update a set yes = 0 ,remarks = null where jianhao in (select jianhao from ab)
update a set yes = z.yes,remarks = z.bz from a,@z z where a.id= z.id
update b set oddshuliang = 0 where jianhao in (select jianhao from ab)
update b set oddshuliang = (select sum(sy) from @t where jh = b.jianhao)
where id in (select max(id) from @t group by jh)
delete ab
end
go
create trigger tri_a on a
for insert ,update,delete
as
begin
if exists(select * from ab)
return
insert ab
select distinct jianhao from inserted union select distinct jianhao from deleted
exec triab
end
go
create trigger tri_b on b
for insert ,update,delete
as
begin
if exists(select * from ab)
return
insert ab
select distinct jianhao from inserted union select distinct jianhao from deleted
exec triab
end
go
insert b select 1,'a',8,0,'2008-1-1'
select * from a
select * from b
insert b select 1,'a',15,0,'2008-1-1'
select * from a
select * from b
insert b select 2,'a',1,0,'2008-1-1'
select * from a
select * from b
insert b select 3,'b',1,0,'2008-1-1'
select * from a
select * from b
/*
id jianhao shuliang jiaohuoqi yes remarks
----------- ---------- -------- ------------------------------------------------------ ---- --------------------------------------------------
1 a 10 2008-01-14 00:00:00.000 1 2008-01-01送交 10件
2 b 15 2008-01-15 00:00:00.000 0 NULL
3 a 8 2008-01-16 00:00:00.000 0 2008-01-01送交 5件
(所影响的行数为 3 行)
id jianhao shuliang oddshuliang riqi
----------- ---------- ----------- ----------- ------------------------------------------------------
1 a 15 5 2008-01-01 00:00:00.000
(所影响的行数为 1 行)
id jianhao shuliang jiaohuoqi yes remarks
----------- ---------- -------- ------------------------------------------------------ ---- --------------------------------------------------
1 a 10 2008-01-14 00:00:00.000 1 2008-01-01送交 10件
2 b 15 2008-01-15 00:00:00.000 0 NULL
3 a 8 2008-01-16 00:00:00.000 0 2008-01-01送交 5件 2008-01-01送交 1件
(所影响的行数为 3 行)
id jianhao shuliang oddshuliang riqi
----------- ---------- ----------- ----------- ------------------------------------------------------
1 a 15 0 2008-01-01 00:00:00.000
2 a 1 6 2008-01-01 00:00:00.000
(所影响的行数为 2 行)
id jianhao shuliang jiaohuoqi yes remarks
----------- ---------- -------- ------------------------------------------------------ ---- --------------------------------------------------
1 a 10 2008-01-14 00:00:00.000 1 2008-01-01送交 10件
2 b 15 2008-01-15 00:00:00.000 0 2008-01-01送交 1件
3 a 8 2008-01-16 00:00:00.000 0 2008-01-01送交 5件 2008-01-01送交 1件
(所影响的行数为 3 行)
id jianhao shuliang oddshuliang riqi
----------- ---------- ----------- ----------- ------------------------------------------------------
1 a 15 0 2008-01-01 00:00:00.000
2 a 1 6 2008-01-01 00:00:00.000
3 b 1 1 2008-01-01 00:00:00.000
(所影响的行数为 3 行)
*/
drop table a,b,ab
drop proc triab
go
create table a(id int,jianhao varchar(10),shuliang smallint,jiaohuoqi datetime,yes bit,remarks varchar(50))
insert into a select 1,'a',10,'2008-1-14',0,null
insert into a select 2,'b',15,'2008-1-15',0,null
insert into a select 3,'a',8,'2008-1-16',0,null
create table b(id int,jianhao varchar(10),shuliang int,oddshuliang int,riqi datetime)
create table ab(jianhao varchar(10))
go
create proc triab
as
begin
declare @t table(id int,jh varchar(10),sl int,sy int,yy int,rq datetime)
declare @z table(id int,jh varchar(10),sl int,rq datetime,bz varchar(50),yes bit)
insert @t select id,jianhao,shuliang,shuliang,0,riqi from b where jianhao in (select jianhao from ab) order by riqi
declare @id int,@jh varchar(10),@sl int,@rq datetime,@sy int,@bz varchar(50),@yes bit
DECLARE triab_cursor CURSOR FOR
SELECT id,jianhao,shuliang,jiaohuoqi from a where jianhao in(select jianhao from ab) order by jianhao,jiaohuoqi
OPEN triab_cursor
FETCH NEXT FROM triab_cursor INTO @id,@jh,@sl,@rq
WHILE @@FETCH_STATUS = 0
BEGIN
update @t set yy = 0
set @sy = 0
select @sy = sum(sy) from @t where jh = @jh
if(@sy >@sl)
begin
set @sy = @sl
update @t set @sy = @sy - sy,yy = @sy where sy > 0 and jh = @jh
update @t set yy = sy,sy = 0 where yy > 0 and jh = @jh
update @t set yy = sy+yy,sy = 0 - yy where yy < 0 and jh = @jh
update @t set sy = sl ,yy = 0 where yy < 0 and jh = @jh
set @yes = 1
end
else
begin
if (@sy = @sl)
begin
update @t set yy = sy,sy = 0 where sy> 0 and jh = @jh
set @yes = 1
end
if (@sy < @sl)
begin
update @t set yy = sy where sy> 0 and jh = @jh
set @yes = 0
end
end
set @bz = ''
select @bz = @bz + convert(varchar(10),rq,120)+'送交 '+ rtrim(yy) +'件 ' from @t where yy > 0
insert @z select @id,@jh,@sl,@rq,@bz,@yes
FETCH NEXT FROM triab_cursor INTO @id,@jh,@sl,@rq
END
CLOSE triab_cursor
DEALLOCATE triab_cursor
update a set yes = 0 ,remarks = null where jianhao in (select jianhao from ab)
update a set yes = z.yes,remarks = z.bz from a,@z z where a.id= z.id
update b set oddshuliang = 0 where jianhao in (select jianhao from ab)
update b set oddshuliang = (select sum(sy) from @t where jh = b.jianhao)
where id in (select max(id) from @t group by jh)
delete ab
end
go
create trigger tri_a on a
for insert ,update,delete
as
begin
if exists(select * from ab)
return
insert ab
select distinct jianhao from inserted union select distinct jianhao from deleted
exec triab
end
go
create trigger tri_b on b
for insert ,update,delete
as
begin
if exists(select * from ab)
return
insert ab
select distinct jianhao from inserted union select distinct jianhao from deleted
exec triab
end
go
drop table a,b,ab
drop proc triab
go
create table a(id int,jianhao varchar(10),shuliang smallint,jiaohuoqi datetime,yes bit,remarks varchar(50))
insert into a select 1,'a',10,'2008-1-14',0,null
insert into a select 2,'b',15,'2008-1-15',0,null
insert into a select 3,'a',8,'2008-1-16',0,null
create table b(id int,jianhao varchar(10),shuliang int,oddshuliang int,riqi datetime)
create table ab(jianhao varchar(10))
go
create proc triab
as
begin
declare @t table(id int,jh varchar(10),sl int,sy int,yy int,rq datetime)
declare @z table(id int,jh varchar(10),sl int,rq datetime,bz varchar(50))
insert @t select id,jianhao,shuliang,shuliang,0,riqi from b where jianhao in (select jianhao from ab) order by riqi
declare @id int,@jh varchar(10),@sl int,@rq datetime,@sy int,@bz varchar(50)
DECLARE triab_cursor CURSOR FOR
SELECT id,jianhao,shuliang,jiaohuoqi from a where jianhao in(select jianhao from ab) order by jianhao,jiaohuoqi
OPEN triab_cursor
FETCH NEXT FROM triab_cursor INTO @id,@jh,@sl,@rq
WHILE @@FETCH_STATUS = 0
BEGIN
set @sy = 0
select @sy = sum(sy) from @t where jh = @jh
if(@sy >=@sl)
begin
set @sy = @sl
update @t set yy = 0
update @t set @sy = @sy - sy,yy = @sy where sy > 0 and jh = @jh
update @t set yy = sy,sy = 0 where yy > 0 and jh = @jh
update @t set yy = sy+yy,sy = 0 - yy where yy < 0 and jh = @jh
update @t set sy = sl ,yy = 0 where yy < 0 and jh = @jh
set @bz = ''
select @bz = @bz + convert(varchar(10),rq,120)+'送交 '+ rtrim(yy) +'件 ' from @t where yy > 0
insert @z select @id,@jh,@sl,@rq,@bz
end
FETCH NEXT FROM triab_cursor INTO @id,@jh,@sl,@rq
END
CLOSE triab_cursor
DEALLOCATE triab_cursor
update a set yes = 0 ,remarks = null where jianhao in (select jianhao from ab)
update a set yes = 1 ,remarks = z.bz from a,@z z where a.id= z.id
update b set oddshuliang = x.sy from b,@t x where b.id = x.id
delete ab
end
go
create trigger tri_a on a
for insert ,update,delete
as
begin
if exists(select * from ab)
return
insert ab
select distinct jianhao from inserted union select distinct jianhao from deleted
exec triab
end
go
create trigger tri_b on b
for insert ,update,delete
as
begin
if exists(select * from ab)
return
insert ab
select distinct jianhao from inserted union select distinct jianhao from deleted
exec triab
end
go