通过在表GoodsDoc(商品资料管理表)、GoodsAttr(商品核心属性管理表)及PgPrice(商品包装关系及价格管理表)添加触发，把数据插入接中问表zzerp_goodsdoc（郑州时空WMS）。
由于商品资料数据涉及到三个表，必须在三个表都建立触发，同时在后期维护时，记住要把三个触发都要同步维护。
触发中只考虑INSERT和UPDATE两种状态，基础资料只允许软删除，排除DELETE状态。
1 SET ANSI_NULLS ON 2 GO 3 SET QUOTED_IDENTIFIER ON 4 GO 5 /* ============================================= 6 Author: Blue Balze 7 Create date: 2019-06-15 8 Description: 商品资料插入、更新时,把相关数据插入接口表 9 维护本触发后，请同时维护表 GOODSATTR 和 PGPRICE 10 ============================================= */ 11 ALTER TRIGGER [dbo].[trg_GoodsDoc_InsertOrUpdate] 12 ON [dbo].[GOODSDOC] 13 FOR INSERT, UPDATE 14 AS 15 BEGIN 16 17 SET NOCOUNT ON ; 18 19 -- 表插入操作 20 IF EXISTS (SELECT 1 FROM inserted) 21 AND NOT EXISTS (SELECT 1 FROM deleted) 22 BEGIN 23 INSERT INTO dbo.zzerp_goodsdoc( ENTID, GOODSID, BEACTIVE, BARCODE, GOODSCODE, GOODSNAME, LOGOGRAM, GOODSSPEC, PLACE 24 , MANUFACTURER, APPROVALNO, APPROVALTO, STORAGETERM, FORMULA, GCATEGORY, UNIT, BIGPICK 25 , SQUANTITY, UEFFORTS, FCLGROUP, LCLGROUP, SHELFCODE, ISELEC, ISDOUCHK, LAYERS, PIECES 26 , FCLLENGTH, FCLWIDTH, FCLHEIGHT, CREATETIME, LASTMODIFYTIME, ZT 27 ) 28 SELECT a.EntId, a.GoodsId, a.Beactive, a.BarCode, a.GoodsCode, a.GoodsName, a.Logogram, a.GoodsSpec, a.Place 29 , a.Manufacturer, b.ApprovalNo, b.ApprovalTo, b.StorageTerm, b.Formula, b.GCategory, c.unit, b.LPack, b.MPack 30 , ‘‘ AS UEFFORTS, ‘‘ AS FCLGROUP, ‘‘ AS LCLGROUP, ‘‘ AS SHELFCODE, b.IsElec, b.IsDouChk, 0 AS LAYERS, 0 AS PIECES 31 , 0 AS FCLLENGTH, 0 AS FCLWIDTH, 0 AS FCLHEIGHT, CONVERT(CHAR(19), GETDATE(), 120) AS CREATETIME 32 , ‘‘ AS LASTMODIFYTIME, 0 AS ZT 33 FROM Inserted a 34 INNER JOIN GOODSATTR b(NOLOCK)ON a.GoodsId=b.GoodsId AND a.EntId=b.EntId 35 INNER JOIN PGPRICE c(NOLOCK)ON a.GoodsId=c.GoodsId AND a.EntId=c.EntId 36 WHERE a.Beactive=‘Y‘; 37 END; 38 39 -- 表更新操作 40 IF EXISTS (SELECT 1 FROM inserted)AND EXISTS (SELECT 1 FROM deleted) 41 BEGIN 42 INSERT INTO dbo.zzerp_goodsdoc( ENTID, GOODSID, BEACTIVE, BARCODE, GOODSCODE, GOODSNAME, LOGOGRAM, GOODSSPEC, PLACE 43 , MANUFACTURER, APPROVALNO, APPROVALTO, STORAGETERM, FORMULA, GCATEGORY, UNIT, BIGPICK 44 , SQUANTITY, UEFFORTS, FCLGROUP, LCLGROUP, SHELFCODE, ISELEC, ISDOUCHK, LAYERS, PIECES 45 , FCLLENGTH, FCLWIDTH, FCLHEIGHT, CREATETIME, LASTMODIFYTIME, ZT 46 ) 47 SELECT a.EntId, a.GoodsId, a.Beactive, a.BarCode, a.GoodsCode, a.GoodsName, a.Logogram, a.GoodsSpec, a.Place 48 , a.Manufacturer, b.ApprovalNo, b.ApprovalTo, b.StorageTerm, b.Formula, b.GCategory, c.unit, b.LPack, b.MPack 49 , ‘‘ AS UEFFORTS, ‘‘ AS FCLGROUP, ‘‘ AS LCLGROUP, ‘‘ AS SHELFCODE, b.IsElec, b.IsDouChk, 0 AS LAYERS, 0 AS PIECES 50 , 0 AS FCLLENGTH, 0 AS FCLWIDTH, 0 AS FCLHEIGHT, CONVERT(CHAR(19), GETDATE(), 120) AS CREATETIME 51 , ‘‘ AS LASTMODIFYTIME, 0 AS ZT 52 FROM Inserted a 53 INNER JOIN GOODSATTR b(NOLOCK)ON a.GoodsId=b.GoodsId AND a.EntId=b.EntId 54 INNER JOIN PGPRICE c(NOLOCK)ON a.GoodsId=c.GoodsId AND a.EntId=c.EntId 55 INNER JOIN deleted d(NOLOCK)ON a.GoodsId=d.GoodsId AND a.EntId=d.EntId 56 57 -- 修改维护资料时，只有接口用到的字段被修改时才触发 58 WHERE( a.EntId<>d.EntId 59 OR a.GoodsId<>d.GoodsId 60 OR a.Beactive<>d.Beactive 61 OR a.BarCode<>d.BarCode 62 OR a.GoodsCode<>d.GoodsCode 63 OR a.GoodsName<>d.GoodsName 64 OR a.Logogram<>d.Logogram 65 OR a.GoodsSpec<>d.GoodsSpec 66 OR a.Place<>d.Place) 67 END 68 END
1 SET ANSI_NULLS ON 2 GO 3 SET QUOTED_IDENTIFIER ON 4 GO 5 /* ============================================= 6 Author: Blue Balze 7 Create date: 2019-06-15 8 Description: 商品资料插入、更新时,把相关数据插入接口表 9 维护本触发后，请同时维护表 GoodsDoc 和 PgPrice 10 ============================================= */ 11 ALTER TRIGGER [dbo].[trg_GoodsAttr_InsertOrUpdate] 12 ON [dbo].[GOODSATTR] 13 FOR INSERT, UPDATE 14 AS 15 BEGIN 16 17 SET NOCOUNT ON ; 18 19 -- 表插入操作 20 IF EXISTS (SELECT 1 FROM inserted) 21 AND NOT EXISTS (SELECT 1 FROM deleted) 22 BEGIN 23 INSERT INTO dbo.zzerp_goodsdoc( ENTID, GOODSID, BEACTIVE, BARCODE, GOODSCODE, GOODSNAME, LOGOGRAM, GOODSSPEC, PLACE 24 , MANUFACTURER, APPROVALNO, APPROVALTO, STORAGETERM, FORMULA, GCATEGORY, UNIT, BIGPICK 25 , SQUANTITY, UEFFORTS, FCLGROUP, LCLGROUP, SHELFCODE, ISELEC, ISDOUCHK, LAYERS, PIECES 26 , FCLLENGTH, FCLWIDTH, FCLHEIGHT, CREATETIME, LASTMODIFYTIME, ZT 27 ) 28 SELECT a.EntId, a.GoodsId, a.Beactive, a.BarCode, a.GoodsCode, a.GoodsName, a.Logogram, a.GoodsSpec, a.Place 29 , a.Manufacturer, b.ApprovalNo, b.ApprovalTo, b.StorageTerm, b.Formula, b.GCategory, c.unit, b.LPack, b.MPack 30 , ‘‘ AS UEFFORTS, ‘‘ AS FCLGROUP, ‘‘ AS LCLGROUP, ‘‘ AS SHELFCODE, b.IsElec, b.IsDouChk, 0 AS LAYERS, 0 AS PIECES 31 , 0 AS FCLLENGTH, 0 AS FCLWIDTH, 0 AS FCLHEIGHT, CONVERT(CHAR(19), GETDATE(), 120) AS CREATETIME 32 , ‘‘ AS LASTMODIFYTIME, 0 AS ZT 33 FROM Inserted b 34 INNER JOIN GoodsDoc a(NOLOCK)ON a.GoodsId=b.GoodsId AND a.EntId=b.EntId 35 INNER JOIN PGPRICE c(NOLOCK)ON a.GoodsId=c.GoodsId AND a.EntId=c.EntId 36 WHERE a.Beactive=‘Y‘; 37 END; 38 39 -- 表更新操作 40 IF EXISTS (SELECT 1 FROM inserted)AND EXISTS (SELECT 1 FROM deleted) 41 BEGIN 42 INSERT INTO dbo.zzerp_goodsdoc( ENTID, GOODSID, BEACTIVE, BARCODE, GOODSCODE, GOODSNAME, LOGOGRAM, GOODSSPEC, PLACE 43 , MANUFACTURER, APPROVALNO, APPROVALTO, STORAGETERM, FORMULA, GCATEGORY, UNIT, BIGPICK 44 , SQUANTITY, UEFFORTS, FCLGROUP, LCLGROUP, SHELFCODE, ISELEC, ISDOUCHK, LAYERS, PIECES 45 , FCLLENGTH, FCLWIDTH, FCLHEIGHT, CREATETIME, LASTMODIFYTIME, ZT 46 ) 47 SELECT a.EntId, a.GoodsId, a.Beactive, a.BarCode, a.GoodsCode, a.GoodsName, a.Logogram, a.GoodsSpec, a.Place 48 , a.Manufacturer, b.ApprovalNo, b.ApprovalTo, b.StorageTerm, b.Formula, b.GCategory, c.unit, b.LPack, b.MPack 49 , ‘‘ AS UEFFORTS, ‘‘ AS FCLGROUP, ‘‘ AS LCLGROUP, ‘‘ AS SHELFCODE, b.IsElec, b.IsDouChk, 0 AS LAYERS, 0 AS PIECES 50 , 0 AS FCLLENGTH, 0 AS FCLWIDTH, 0 AS FCLHEIGHT, CONVERT(CHAR(19), GETDATE(), 120) AS CREATETIME 51 , ‘‘ AS LASTMODIFYTIME, 0 AS ZT 52 FROM Inserted b 53 INNER JOIN GoodsDoc a(NOLOCK)ON a.GoodsId=b.GoodsId AND a.EntId=b.EntId 54 INNER JOIN PGPRICE c(NOLOCK)ON a.GoodsId=c.GoodsId AND a.EntId=c.EntId 55 INNER JOIN deleted d(NOLOCK)ON a.GoodsId=d.GoodsId AND a.EntId=d.EntId 56 57 -- 修改维护资料时，只有接口用到的字段被修改时才触发 58 WHERE( a.EntId<>d.EntId 59 OR b.ApprovalNo<>d.ApprovalNo 60 OR b.ApprovalTo<>d.ApprovalTo 61 OR b.StorageTerm<>d.StorageTerm 62 OR b.Formula<>d.Formula 63 OR b.GCategory<>d.GCategory 64 OR b.LPack<>d.LPack 65 OR b.MPack<>d.MPack 66 OR b.IsElec<>d.IsElec 67 OR b.IsDouChk<>d.IsDouChk) 68 END 69 END
1 SET ANSI_NULLS ON 2 GO 3 SET QUOTED_IDENTIFIER ON 4 GO 5 /* ============================================= 6 Author: Blue Balze 7 Create date: 2019-06-15 8 Description: 商品资料插入、更新时,把相关数据插入接口表 9 维护本触发后，请同时维护表 GoodsDoc 和 GoodsAttr 10 ============================================= */ 11 ALTER TRIGGER [dbo].[trg_PgPrice_InsertOrUpdate] 12 ON [dbo].[PgPrice] 13 FOR INSERT, UPDATE 14 AS 15 BEGIN 16 17 SET NOCOUNT ON ; 18 19 -- 表插入操作 20 IF EXISTS (SELECT 1 FROM inserted) 21 AND NOT EXISTS (SELECT 1 FROM deleted) 22 BEGIN 23 INSERT INTO dbo.zzerp_goodsdoc( ENTID, GOODSID, BEACTIVE, BARCODE, GOODSCODE, GOODSNAME, LOGOGRAM, GOODSSPEC, PLACE 24 , MANUFACTURER, APPROVALNO, APPROVALTO, STORAGETERM, FORMULA, GCATEGORY, UNIT, BIGPICK 25 , SQUANTITY, UEFFORTS, FCLGROUP, LCLGROUP, SHELFCODE, ISELEC, ISDOUCHK, LAYERS, PIECES 26 , FCLLENGTH, FCLWIDTH, FCLHEIGHT, CREATETIME, LASTMODIFYTIME, ZT 27 ) 28 SELECT a.EntId, a.GoodsId, a.Beactive, a.BarCode, a.GoodsCode, a.GoodsName, a.Logogram, a.GoodsSpec, a.Place 29 , a.Manufacturer, b.ApprovalNo, b.ApprovalTo, b.StorageTerm, b.Formula, b.GCategory, c.unit, b.LPack, b.MPack 30 , ‘‘ AS UEFFORTS, ‘‘ AS FCLGROUP, ‘‘ AS LCLGROUP, ‘‘ AS SHELFCODE, b.IsElec, b.IsDouChk, 0 AS LAYERS, 0 AS PIECES 31 , 0 AS FCLLENGTH, 0 AS FCLWIDTH, 0 AS FCLHEIGHT, CONVERT(CHAR(19), GETDATE(), 120) AS CREATETIME 32 , ‘‘ AS LASTMODIFYTIME, 0 AS ZT 33 FROM Inserted c 34 INNER JOIN GoodsDoc a(NOLOCK)ON a.GoodsId=c.GoodsId AND a.EntId=c.EntId 35 INNER JOIN GoodsAttr b(NOLOCK)ON a.GoodsId=c.GoodsId AND a.EntId=c.EntId 36 WHERE a.Beactive=‘Y‘; 37 END; 38 39 -- 表更新操作 40 IF EXISTS (SELECT 1 FROM inserted)AND EXISTS (SELECT 1 FROM deleted) 41 BEGIN 42 -- 插入更新后beactive状态为“是”、“否”的资料(排除首营维护) 43 INSERT INTO dbo.zzerp_goodsdoc( ENTID, GOODSID, BEACTIVE, BARCODE, GOODSCODE, GOODSNAME, LOGOGRAM, GOODSSPEC, PLACE 44 , MANUFACTURER, APPROVALNO, APPROVALTO, STORAGETERM, FORMULA, GCATEGORY, UNIT, BIGPICK 45 , SQUANTITY, UEFFORTS, FCLGROUP, LCLGROUP, SHELFCODE, ISELEC, ISDOUCHK, LAYERS, PIECES 46 , FCLLENGTH, FCLWIDTH, FCLHEIGHT, CREATETIME, LASTMODIFYTIME, ZT 47 ) 48 SELECT a.EntId, a.GoodsId, a.Beactive, a.BarCode, a.GoodsCode, a.GoodsName, a.Logogram, a.GoodsSpec, a.Place 49 , a.Manufacturer, b.ApprovalNo, b.ApprovalTo, b.StorageTerm, b.Formula, b.GCategory, c.unit, b.LPack, b.MPack 50 , ‘‘ AS UEFFORTS, ‘‘ AS FCLGROUP, ‘‘ AS LCLGROUP, ‘‘ AS SHELFCODE, b.IsElec, b.IsDouChk, 0 AS LAYERS, 0 AS PIECES 51 , 0 AS FCLLENGTH, 0 AS FCLWIDTH, 0 AS FCLHEIGHT, CONVERT(CHAR(19), GETDATE(), 120) AS CREATETIME 52 , ‘‘ AS LASTMODIFYTIME, 0 AS ZT 53 FROM Inserted c 54 INNER JOIN GoodsDoc a(NOLOCK)ON a.GoodsId=c.GoodsId AND a.EntId=c.EntId 55 INNER JOIN GoodsAttr b(NOLOCK)ON a.GoodsId=c.GoodsId AND a.EntId=c.EntId 56 INNER JOIN deleted d(NOLOCK)ON a.GoodsId=d.GoodsId AND a.EntId=d.EntId 57 58 -- 修改维护资料时，只有接口用到的字段被修改时才触发 59 WHERE c.unit<>d.unit 60 END 61 END
时空智友实施（数据接口）——商品资料通过触发同步数据到接口中间表
原文：https://www.cnblogs.com/HHO2015/p/11049761.html
