求助，写一个触发器+事务

[relax_xi]

表依次为[dingdan][dingdanmingxi][jifen][kucun][youhuijuan]


根据dingdan.ddStatus

当dingdan.ddStatus=2时
如果dingdan.ddType=1
将youhuijuan表yhuID等于dingdan表yhuID的yhjStatus改为0
(dingdan.yhjid=youhuijuan.yhjid)
将kucun表spID和dingdanmingxi表spID相同的并且ddID=1的kcNum加上ddmxNum
(kucun.spID=dingdanmingxi.spID and kucun.kcID=dingdanmingxi.kcID and dingdan.ddID=dingdanmingxi.ddID)

执行这种之后的数据

当dingdan.ddStatus=3时
如果dingdan.ddType=2
将jifen表新增一条jfPoint为dingdan表ddPoint相反的数据
(jifen.userid=dingdan.userid)
将kucun表spID和dingdanmingxi表spID相同的并且ddID=1的kcNum减去ddmxNum
(kucun.spID=dingdanmingxi.spID and kucun.kcID=dingdanmingxi.kcID and dingdan.ddID=dingdanmingxi.ddID)

执行这种之后的数据
userID和ddID都是从被更改了status的这条数据记录里拿出来的，最好把业务写成事务在调用

表和数据的脚本

if exists (select * from sysobjects where id = OBJECT_ID('[dingdan]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [dingdan]

CREATE TABLE [dingdan] (
[ddID] [int]  IDENTITY (1, 1)  NOT NULL,
[ddStatus] [int]  NULL,
[ddPoint] [int]  NULL,
[yhjID] [int]  NULL,
[ddType] [int]  NULL,
[ddPrice] [decimal]  (18,4) NULL,
[userID] [int]  NULL)

ALTER TABLE [dingdan] WITH NOCHECK ADD  CONSTRAINT [PK_dingdan] PRIMARY KEY  NONCLUSTERED ( [ddID] )
SET IDENTITY_INSERT [dingdan] ON

INSERT [dingdan] ([ddID],[ddStatus],[ddPoint],[yhjID],[ddType],[ddPrice],[userID]) VALUES ( 1,1,10,1,1,20.0000,1)
INSERT [dingdan] ([ddID],[ddStatus],[ddPoint],[yhjID],[ddType],[ddPrice],[userID]) VALUES ( 2,1,10,1,1,20.0000,1)

SET IDENTITY_INSERT [dingdan] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[dingdanmingxi]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [dingdanmingxi]

CREATE TABLE [dingdanmingxi] (
[ddmxID] [int]  IDENTITY (1, 1)  NOT NULL,
[ddID] [int]  NULL,
[spID] [int]  NULL,
[kcID] [int]  NULL,
[ddmxNum] [int]  NULL)

ALTER TABLE [dingdanmingxi] WITH NOCHECK ADD  CONSTRAINT [PK_dingdanmingxi] PRIMARY KEY  NONCLUSTERED ( [ddmxID] )
SET IDENTITY_INSERT [dingdanmingxi] ON

INSERT [dingdanmingxi] ([ddmxID],[ddID],[spID],[kcID],[ddmxNum]) VALUES ( 1,1,1,1,5)
INSERT [dingdanmingxi] ([ddmxID],[ddID],[spID],[kcID],[ddmxNum]) VALUES ( 2,1,2,2,5)

SET IDENTITY_INSERT [dingdanmingxi] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[jifen]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [jifen]

CREATE TABLE [jifen] (
[jfID] [int]  IDENTITY (1, 1)  NOT NULL,
[userID] [int]  NULL,
[jfPoint] [int]  NULL)

ALTER TABLE [jifen] WITH NOCHECK ADD  CONSTRAINT [PK_jifen] PRIMARY KEY  NONCLUSTERED ( [jfID] )
SET IDENTITY_INSERT [jifen] ON


SET IDENTITY_INSERT [jifen] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[kucun]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [kucun]

CREATE TABLE [kucun] (
[kcID] [int]  IDENTITY (1, 1)  NOT NULL,
[spID] [int]  NULL,
[kcNum] [int]  NULL)

ALTER TABLE [kucun] WITH NOCHECK ADD  CONSTRAINT [PK_kucun] PRIMARY KEY  NONCLUSTERED ( [kcID] )
SET IDENTITY_INSERT [kucun] ON

INSERT [kucun] ([kcID],[spID],[kcNum]) VALUES ( 1,1,200)
INSERT [kucun] ([kcID],[spID],[kcNum]) VALUES ( 2,2,200)

SET IDENTITY_INSERT [kucun] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[shangpin]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [shangpin]


SET IDENTITY_INSERT [shangpin] OFF
if exists (select * from sysobjects where id = OBJECT_ID('[youhuijuan]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [youhuijuan]

CREATE TABLE [youhuijuan] (
[yhjID] [int]  IDENTITY (1, 1)  NOT NULL,
[yhjStatus] [int]  NULL)

ALTER TABLE [youhuijuan] WITH NOCHECK ADD  CONSTRAINT [PK_youhuijuan] PRIMARY KEY  NONCLUSTERED ( [yhjID] )
SET IDENTITY_INSERT [youhuijuan] ON

INSERT [youhuijuan] ([yhjID],[yhjStatus]) VALUES ( 1,1)

SET IDENTITY_INSERT [youhuijuan] OFF

[以学习为目的]

先帮顶一下，再来看看

[以学习为目的]

UPDATE  youhuijuan SET yhjStatus='0'
FROM dingdan 
WHERE     dingdan.ddStatus=2 
      AND dingdan.ddType=1
      AND youhuijuan.yhuID=dingdan.yhuID

INSERT  INTO jifen(ddID,ddStatus,jfPoint,yhjID,ddType,ddPrice,userID)
        SELECT ddID,ddStatus,-1*ddPoint jfPoint,yhjID,ddType,ddPrice,userID
        FROM dingdan 
        WHERE dingdan.ddStatus=3 AND dingdan.ddType=2
        
UPDATE kucun SET kucun.kcNum=kucun.kcNum-dingdanmingxi.ddmxNum
FROM dingdanmingxi
WHERE   kucun.spID=dingdanmingxi.spID 
    and kucun.kcID=dingdanmingxi.kcID 
    and dingdan.ddID=dingdanmingxi.ddID

[asd613613]

学习一下，我还没看懂

[Steven-Xu]

表示没看懂，帮顶一下吧。

[卖水果的net]

东西太多，没怎么看明白。