CREATE TRIGGER updateAmount
ON 送货从表
DECLARE @DeliveryNo NVARCHAR(12)
DELCARE @Amount INT
DECLARE @CusId NVARCHAR(12)
SELECT @DeliveryNo =DeliveryNo ,@Amount=Amount From Inserted
SELECT @CusId=CusId FROM 送货主表 WHERE DeliveryNo=@DeliveryNo
UPDATE 客户表 SET DeliveryAmount(+)-Amount WHERE CusId =CusId
ON 送货从表
DECLARE @DeliveryNo NVARCHAR(12)
DELCARE @Amount INT
DECLARE @CusId NVARCHAR(12)
SELECT @DeliveryNo =DeliveryNo ,@Amount=Amount From Inserted
SELECT @CusId=CusId FROM 送货主表 WHERE DeliveryNo=@DeliveryNo
UPDATE 客户表 SET DeliveryAmount(+)-Amount WHERE CusId =CusId
解决方案 »
- 我要将xml转成数据库,在java中实现了xml数据的获取,遇到一个问题!!!
- 可更新订阅,在使用订阅服务器更新数据
- 启动不了服务管理器
- SSRS 中如何获得一个data instance下面的report server DB 的名字??
- A数据库的存储过程能将B数据库的数据存取过来吗?AB是局域网内不同的MSSQL数据库
- 还是数据重复
- 急:象“1A”这样的十六进制字符串如何转换成整数0x1A,即26
- 怎样选择表中某列的数据,并直接赋给变量?
- 怎样把我数据库中的所有的数据一次性从简体该为繁体?(数据库Sql server 7)
- nononono(null,null)大侠请进!
- sql查询语句,化繁为简——求教高手
- 如何合并两个数据表的数据
ON 送货从表
FOR update ,insert
DECLARE @DeliveryNo NVARCHAR(12)
DELCARE @Amount INT
DECLARE @CusId NVARCHAR(12)
SELECT @DeliveryNo =DeliveryNo ,@Amount=Amount From Inserted
SELECT @CusId=CusId FROM 送货主表 WHERE DeliveryNo=@DeliveryNo
UPDATE 客户表 SET DeliveryAmount(+)-Amount WHERE CusId =CusId
ON 送货从表
FOR INSERT,DELETE,UPDATE
AS
BEGIN
IF EXISTS(SELECT 1 FROM INSERTED WHERE 1=1)
UPDATE 客户表
SET DeliveryAmount = ISNULL(DeliveryAmount,0) + D.Amount
FROM (SELECT A.CusID,SUM(B.Amount) AS Amount
FROM 送货主表 AS A INNER JOIN INSERTED AS B
ON A.DeliveryNo = B.DeliveryNo
GROUP BY A.CusID) AS D
WHERE 客户表.CusID = D.CusID
ELSE
UPDATE 客户表
SET DeliveryAmount = ISNULL(DeliveryAmount,0) - D.Amount
FROM (SELECT A.CusID,SUM(B.Amount) AS Amount
FROM 送货主表 AS A INNER JOIN DELETED AS B
ON A.DeliveryNo = B.DeliveryNo
GROUP BY A.CusID) AS D
WHERE 客户表.CusID = D.CusID
END