求一个条件比较复杂的SQL。

Question

"在表头t_a中增加一个触发器，在更改除a1=30或者 a1=40 并且 除a1=40 a2=44 之外的行的a3列=‘y’时触发，将和表头相关的表身中 b4-b5 的值 (条件是 b4-b5>0 and b5 >0 and b1=40 or b1=30 and b3不包括'c'开头的物料 )
更新到表身t_b 中 指定行： b1=40 and b2=44的单据中去， 如果b3相同则将b4-b5的数量增加到b4中，否则新增一行"

create table t_a(
    a1  char(4) primary key ,
    a2  char(4) primary key,
    a3  char(1)
  
)create table t_b(
    b1  char(4) primary key,
    b2  char(4)primary key,
    b3  char(20)primary key,
    b4   numeric(15,6),
    b5   numeric(15,6)
  
)
insert into t_a(a1,a2,a3)values('30','31','y')
insert into t_a(a1,a2,a3)values('30','32','y')
insert into t_a(a1,a2,a3)values('40','43','y')
insert into t_a(a1,a2,a3)values('40','42','y')
insert into t_a(a1,a2,a3)values('40','44','y')
insert into t_a(a1,a2,a3)values('50','55','y')
insert into t_a(a1,a2,a3)values('60','66','y')insert into t_b(b1,b2,b3,b4,b5)values('30','31','a100',30,20)
insert into t_b(b1,b2,b3,b4,b5)values('30','31','b22',30,10)
insert into t_b(b1,b2,b3,b4,b5)values('30','32','b33',30,-20)
insert into t_b(b1,b2,b3,b4,b5)values('30','32','c01',20,10)
insert into t_b(b1,b2,b3,b4,b5)values('40','43','d08',50,50)
insert into t_b(b1,b2,b3,b4,b5)values('40','42','e99',60,30)
insert into t_b(b1,b2,b3,b4,b5)values('50','55','e98',70,20)
insert into t_b(b1,b2,b3,b4,b5)values('60','66','e65',10,6)
insert into t_b(b1,b2,b3,b4,b5)values('40','44','a100',10,2)
insert into t_b(b1,b2,b3,b4,b5)values('40','44','b22',20,20)
insert into t_b(b1,b2,b3,b4,b5)values('40','44','d08',30,20)

Answer 1

表头  t_a
a1(单据类型)主键 a2(单号)主键 a3(状态码)
30 31 n
30 32 n
40 43 n
40 42 n
40 44 n
50 55 n
60 66 n

表身 t_b
b1(单据类型)主键 b2(单号)主键 b3(物料号)主键 b4(备料数量) b5(已发数量)
30 31 a100 30 20
30 31  b22  30 10
30 32 b33 30 -20
30 32 c01 20 10
40 43 d08 50 50
40 42 e99 60 30
50 55 e98 70 20
60 66 e65 10 6
40 44 a100 10 2
40 44 b22 20 20
40 44 d08 30 20
要求如下：
"在表头t_a中增加一个触发器，在更改除a1=30或者 a1=40 并且 除a1=40 a2=44 之外的行的a3列=‘y’时触发，将和表头相关的表身中 b4-b5 的值 (条件是 b4-b5>0 and b5 >0 and b1=40 or b1=30 and b3不包括'c'开头的物料 )
更新到表身t_b 中 指定行： b1=40  b2=44中去， 如果b3相同则将b4-b5的数量增加到b4中，否则新增一行"



要求结果如下：
表头  t_a
a1(单据类型)主键 a2(单号)主键 a3(状态码)
30 31 y 触发
30 32 y 触发
40 43 y 触发
40 42 y 触发
40 44 y 不触发
50 55 y 不触发
60 66 y 不触发

表身 t_b
b1(单据类型)主键 b2(单号)主键 b3(物料号)主键 b4(备料数量) b5(已发数量)
30 31 a100 30 20
30 31  b22  30 10
30 32 b33 30 -20
30 32 c01 20 10
40 43 d08 50 50
40 42 e99 60 30
50 55 e98 70 20
60 66 e65 10 6
40 44 a100 20 2
40 44 b22 40 20
40 44 d08 30 20
40 44 e99 30 0

Answer 2

A表
单别 单号  状态
1    100    n
1    100   n
3     300   n
4     400    n
5     500    nB表
单别 单号  物料号    备料数   已发数
1    100    a-001      100     30
1    100    a-002      30      10
3    300    b-001      30      10 
3     300   a-002      30      -10
4     400    a-002       50      20
4     400    a-003       10      10
5     500    b-001       100     50
求一MSSQL2000 触发器 ,on  A表的update
更新A表的状态列=y时触发。
将B表中 单别为 1 和 3 并且 物料号以'a' 和 'b'开头 并且 已发数量大于0 的 未发数（即备料数-已发数）增加到 单别=4 单号=400的单据中，目标单据中有相同物料号的则 把未发数增加到备料数上，否则就新增加一行单别 单号  物料号    备料数   已发数
1    100    a-001      100     30
1    100    a-002      30      10
3    300    b-001      30      10 
3     300   a-002      30      -10
3     300   c-003       30     10
4     400    a-002       70      20
4     400    a-003       10      10
4    400    b-001      30      10 
4     400   a-001       70       0
5     500    b-001       100     50多谢各位回复，改了下。

Answer 3

create table A (
    单别 char(4) not null,
    单号  char(4) not null,
    状态 char(1) 
)
alter table A add primary key(单别,单号)create table B(
    单别 char(4) not null,
    单号 char(4) not null,
    物料号 char(10)not null,
   备料数 numeric(15,6),
   已发数 numeric(15,6)
)
alter table B add primary key(单别,单号,物料号)insert into A(单别,单号,状态)values('1','100','n')insert into A(单别,单号,状态)values('3','300','n')
insert into A(单别,单号,状态)values('5','500','n') 
insert into B(单别,单号,物料号,备料数,已发数)values('1','100','a-100',100,30)
insert into B(单别,单号,物料号,备料数,已发数)values('1','100','a-002',30,10)
insert into B(单别,单号,物料号,备料数,已发数)values('3','300','b-001',30,10)
insert into B(单别,单号,物料号,备料数,已发数)values('3','300','a-002',30,-10)
insert into B(单别,单号,物料号,备料数,已发数)values('4','400','a-002',50,20)
insert into B(单别,单号,物料号,备料数,已发数)values('4','400','a-003',10,10)
insert into B(单别,单号,物料号,备料数,已发数)values('5','500','b-001',100,50)

Answer 4

建表的SQL 写漏了一条
insert into A(单别,单号,状态)values('4','400','n')

Answer 5

create     trigger tr_a 
on A
for update  
as
 if update(状态)
create table #t01
(
物料号 char(10),
未发数 numeric(15,6)
)
insert into #t01(物料号, 未发数)
(select B.物料号, B.备料数 - B.已发数
 from B 
 inner join inserted on B.单别 = inserted.单别 and B.单号 = inserted.单号 and inserted.状态 = 'y'
  where inserted.单别 in ('1', '3') and left(B.物料号,1) in ('a', 'b') and B.备料数 - B.已发数 > 0 and B.已发数 > 0
) update B set 备料数 = isnull(备料数,0) + #t01.未发数
from B
inner join #t01 on B.物料号 = #t01.物料号
where B.单别 = '4' and B.单号 = '400'
insert into B (单别, 单号,  物料号,    备料数,   已发数)
(select '4', '400', b2.物料号, b2.备料数 - b2.已发数, 0
 from B b2
 inner join inserted on b2.单别 = inserted.单别 and b2.单号 = inserted.单号
 where inserted.单别 in ('1', '3') and left(b2.物料号,1) in ('a', 'b') and b2.备料数 - b2.已发数 > 0 and b2.已发数 > 0 
   and not exists (select 1 from B b3 
  where b3.单别 = '4' and b3.单号 = '400' and b3.物料号 = b2.物料号
 )
)