触发器

2008-01-21 09:01:34 396 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

IC基础知识（十五）RS触发器、JK触发器、D触发器、T触发器 RS触发器、JK触发器、D触发器、T触发器介绍阅读详情

有2张表

交货表
件名交货数量交货期交货状态备注
A 10 2008-1-14 未交货
B 15 2008-1-15 未交货
A 8 2008-1-16 未交货
每天走件表
件号数量走件日期
A 18 2008-1-17
当我输入走件时
程序根据交货表的内容自动按照交货期的先后顺序把未交货变成已交货。

件名交货数量交货期交货状态备注
A 10 2008-1-14 已交货 2008-1-17 送交10件
B 15 2008-1-15 未交货
A 8 2008-1-16 已交货 2008-1-17 送交8件
程序我已经写完了，是在输入数据时,用asp自动算出来，修改表内容的（当然实际走件表的值不是完全按照交货期的数量送交的，这不影响程序，只是细节问题）。没用触发器或储存过程。
不过，如果数据输入有误的话，我就要进入管理器再手动修改（倒推过去），比较麻烦。
我想做个触发器，当交货表或走件表修改时，自动算一遍。

...全文

触发器（基本的SR触发器、同步触发器、D触发器）一、能够存储1位二值信号的基本单元电路统称为触发器（Filp-Flop）　　触发器是构成时序逻辑电路的基本逻辑部件。它有两个稳定状态：“0”和“1”。在不同的输入情况下，它可以被置0状态或1状态，当输入信号消失后，所置成的状态能够保持不变。所以触发器可以记忆1位二值的信号。根据逻辑功能的不同，触发器可以分为SR触发器、D触发器、JK触发器、T和T'触发器。按照结构形式的不同，又可分基... 阅读详情

wolflove23

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

wolflove23 2008-01-23

打赏
举报

好，不过，我还没有发现不换的时候，什么时候不对。

JiangHongTao 2008-01-23

打赏
举报

--update @t set @sy = @sy - sy,yy = @sy where sy > 0 and jh = @jh
--换成
update @t set @sy = @sy - sy,yy = @sy+sy where sy > 0 and jh = @jh

wolflove23 2008-01-23

打赏
举报

作为回报，兄弟给你150分，万分感谢

JiangHongTao 2008-01-23

打赏
举报

剩余数量都放在相应件号最大得ID里面。
LZ加分结贴吧，累死我了。

JiangHongTao 2008-01-23

打赏
举报

有个错误，修改了一下：

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

JiangHongTao 2008-01-23

打赏
举报



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 行）

*/

JiangHongTao 2008-01-23

打赏
举报

已经是按照交货期匹配了。
不过前面得问题存在，修改了一下.

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

wolflove23 2008-01-23

打赏
举报

不好意思，送交的数量应该按照交货期的先后顺序，比如：2008-1-14 交货数量15件 2008-1-15交货数量 12件，当我今天走件数量是12件的话，应该是2008-1-14走了12件，还差3件，而2008-1-15的12件依然是未交货的，这是实际情况。不应该走的12件去匹配和他一样的交货数量。

wolflove23 2008-01-23

打赏
举报

又遇到一个问题，当我输入的走件数量和交货数量一样时，怎么订单表里面的备注还是空的，虽然变成已交货了。只有当再在输入时，以前的备注才有说明。

wolflove23 2008-01-23

打赏
举报

还有一点小问题，当我输入送交数量时如果不满足一个交货数量，这时候能不能在备注上先写上某年某月送交几件。我看到这个程序是当交货状态变成已交货时，才写上具体那天送交几件的。

JiangHongTao 2008-01-23

打赏
举报

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

wolflove23 2008-01-23

打赏
举报

订单表结构
名称数据类型大小
ID int 自动编号主键
dingdanhao varchar 50 //订单号
jianhao varchar 50 //件号
jianming nvarchar 100 //件名
weight float 8 //重量
price float 8 //价格
shuliang smallint 2 //数量
jiaohuoqi datetime 8 //交货期
yes bit 1 //是否交货
remarks nvarchar 4000 //备注
qianshou bit 1 //是否签收
走件表结构
ID int 4 //自动编号
jianhao varchar 50 //件号
shuliang smallint 2 //走件数量
oddshuliang smallint 2 //剩余数量
riqi datetime 8 //走件日期
remarks nvarchar 200

wolflove23 2008-01-23

打赏
举报

还是有问题，当我把走件表的东西全删除时，怎么交货状态为NULL ，应该为0才对。
我把你的触发器应用到我实际表时就是把a 表变成product， b表变成songjiao时，我在送交表里面修改了一个值，情况发生了，我的以前交货状态是1的全变成NULL ，送交表这行的剩余数量为以前所有的累积值。看来还是有BUG 。

wolflove23 2008-01-22

打赏
举报

订单表：
订单号件号件名重量数量交货日期交货状态备注 //按交货期升序排列
215628 22M7121111XC 推铲 216 6 2007-12-26 已交货 2008-1-16 送交 1件2008-1-17 送交 5件
215690 22M7121111XC 推铲 216 6 2008-1-4 已交货 2008-1-17 送交 3件2008-1-18 送交 3件
215690 22M7121111XC 推铲 216 6 2008-1-5 未交货 2008-1-18 送交 3件
215690 22M7121111XC 推铲 216 6 2008-1-6 未交货 NULL
走件表：
件号件名重量交货数量剩余数量走件日期备注 //降序排列
22M7121111XC 推铲 216 6 3 2008-1-18 215690订单 215690订单
22M7121111XC 推铲 216 8 0 2008-1-17 215628订单 215690订单
22M7121111XC 推铲 216 1 0 2008-1-16 215628订单 215628订单

所以我的没有主键。订单就是客户发过来的电子邮件，导入到我的数据库。走件表就是每天送出的实际数量，这么一个流水帐。程序根据我每天送出的产品名和数量自动计算哪个订单的产品是否交货，什么时候交货的。所以我用表单做了一个，成品库人员只需要输入件号和数量，日期。就可以了。但是有时候人员的手误，可能输错件号或者数量。我一般就去数据库手动修改过来。
说明一点：我的剩余数量只在该件号最后一条记录记录是否有剩余

比如今天我又走了22M7121111XC 7件那么就变成了
订单号件号件名重量数量交货日期交货状态备注 //按交货期升序排列
215628 22M7121111XC 推铲 216 6 2007-12-26 已交货 2008-1-16 送交 1件2008-1-17 送交 5件
215690 22M7121111XC 推铲 216 6 2008-1-4 已交货 2008-1-17 送交 3件2008-1-18 送交 3件
215690 22M7121111XC 推铲 216 6 2008-1-5 已交货 2008-1-18 送交 3件2008-1-22 送交 3件
215690 22M7121111XC 推铲 216 6 2008-1-6 未交货 2008-1-22 送交 4件

件号件名重量交货数量剩余数量走件日期备注//降序排列
22M7121111XC 推铲 216 7 4 2008-1-22 215690订单
22M7121111XC 推铲 216 6 0 2008-1-18 215690订单 215690订单
22M7121111XC 推铲 216 8 0 2008-1-17 215628订单 215690订单
22M7121111XC 推铲 216 1 0 2008-1-16 215628订单

JiangHongTao 2008-01-22

打赏
举报

我也没看到你的自动编号字段呀，你可以贴出你的表定义
我改一下代码，你可以马上测试。

JiangHongTao 2008-01-22

打赏
举报

我上面的代码应该能够满足你的需要。

cdqmjp 2008-01-21

打赏
举报

7楼的代码很精典！学习...

wzy_love_sly 2008-01-21

打赏
举报

不好弄，我觉的你该加个列，保存总的库存
这样好弄多了！我今天有点晕，sorry

wolflove23 2008-01-21

打赏
举报

上面的错行了，再写一次
当我再送交1件时：
件号数量剩余数量走件日期
A 17 0 2008-1-17 //原来剩余数量7 加上这次的1件共8件
A 1 0 2008-1-18 //正好够1-16那天的交货数量

wolflove23 2008-01-21

打赏
举报

7楼写的是插入型触发器吧。如果，修改，删除时呢？是不是
create trigger tri_a on b
for insert ,update,delete。
为什么会有a.交货期<=inserted.走件日期这句话呢？走件日期和交货期没有任何关系，只是交货期按照升序排列，依次修改交货状态，加上备注。备注内容依次按照走件日期升序填写。如果我走件数量是17件，而不是18件。我的走件表还有一列
create table b(件号 varchar(10),数量 int,剩余数量 int,走件日期 datetime)

件号数量剩余数量走件日期
A 17 7 2008-1-17
这样的话我的交货表的第3行会是这样的

件号交货数量交货期交货状态备注
a 10 2008-01-14 00:00:00.000 已交货 2008-01-17送交10件
b 15 2008-01-15 00:00:00.000 未交货 NULL
a 8 2008-01-16 00:00:00.000 未交货 2008-01-17送交7件
当我再送交1件时：
件号数量剩余数量走件日期
A 17 0 2008-1-17 //原来剩余数量7 加上这次的1件共8件A 1 0 2008-1-18//正好够1-16那天的交货数量
走件表变为：
件号交货数量交货期交货状态备注
a 10 2008-01-14 00:00:00.000 已交货 2008-01-17送交10件
b 15 2008-01-15 00:00:00.000 未交货 NULL
a 8 2008-01-16 00:00:00.000 已交货 2008-01-17送交7件 2008-1-18送交1件

加载更多回复（18）

触发器

相关推荐