易飞多角订单，维护采购单

/* 订单单身多角采购单价
1.首先将客户订单做自定义画面设置，单身处添加一个数值型自定义字段，并命名为多角采购单价
  这样业务员可以节省很多的时间
2.接下来就是触发器了，首先要考虑触发器不能够影响到正常的单据抛转且正常完成该动作
3.以下为触发器了，仅供参(备份好数据库)
4.触发器问题点：多角采购单价这一步不能少的，还是要生成的
5.执行措施
UPDATE A SET A.UDF51=B.TD006 FROM COPTD A 
LEFT JOIN MTPTD B ON B.TD001=A.TD001 AND B.TD002=A.TD002 AND B.TD003=A.TD003 AND B.TD006<>0
WHERE A.TD001='2710' AND B.TD006 IS NOT NULL
UPDATE B SET B.TD006=A.UDF51 FROM COPTD A 
LEFT JOIN MTPTD B ON B.TD001=A.TD001 AND B.TD002=A.TD002 AND B.TD003=A.TD003 AND B.TD006<>0
WHERE A.TD001='2710' AND B.TD006 IS NOT NULL
*/
USE [数据库名]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tr_INSERTCOPTD] ON [dbo].[COPTD] 
FOR INSERT
AS
DECLARE @TD001 CHAR(4),--订单单别
        @TD002 CHAR(11),--订单单号
        @TD003 CHAR(4),--序号
        @TD004 CHAR(21),--品号
        @UDF51 NUMERIC(17,8),--单价
        @TD021 CHAR(1)
              
BEGIN TRANSACTION
 SET NOCOUNT ON
 
SELECT @TD001=TD001,@TD002=TD002,@TD003=TD003,@UDF51=UDF51,@TD021=TD021,@TD004=TD004 FROM INSERTED
 IF @TD001='2710' AND @UDF51=0
 BEGIN
 RAISERROR('单身品号:%s的多角采购单 为空不允许下',16,1,@TD004)
 END
 IF @TD001='2710' AND @UDF51<>0
 BEGIN
 UPDATE MTPTD SET TD006=@UDF51 WHERE TD001=@TD001 AND TD002=@TD002 AND TD003=@TD003 AND TD004='抛转数据库名'
 END
 IF @TD001='2710' AND @UDF51<>0 AND @TD021='Y'
 BEGIN
 UPDATE MTPTD SET TD006=@UDF51 WHERE TD001=@TD001 AND TD002=@TD002 AND TD003=@TD003 AND TD004='抛转数据库名'
 END
 SET NOCOUNT OFF
COMMIT TRANSACTION
GO
---------------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[tr_UPDATECOPTD] ON [dbo].[COPTD] 
FOR UPDATE
AS
DECLARE @TD001 CHAR(4),--订单单别
        @TD002 CHAR(11),--订单单号
        @TD003 CHAR(4),--序号
        @TD004 CHAR(21),--品号
        @UDF51 NUMERIC(17,8),--单价
        @TD021 CHAR(1)
        
BEGIN TRANSACTION
 SET NOCOUNT ON
 
SELECT @TD001=TD001,@TD002=TD002,@TD003=TD003,@UDF51=UDF51,@TD021=TD021,@TD004=TD004 FROM INSERTED
 IF @TD001='2710'
 BEGIN
 UPDATE MTPTD SET TD006=@UDF51 WHERE TD001=@TD001 AND TD002=@TD002 AND TD003=@TD003 AND TD004='抛转数据库名'
 END
 SET NOCOUNT OFF
COMMIT TRANSACTION
GO

📱 扫码关注公众号

扫描二维码关注我们，获取更多精彩内容
实时更新 · 干货满满