时空智友实施（数据接口）——客商资料通过触发同步数据到接口中间表

 1 SET ANSI_NULLS ON
 2 GO
 3 SET QUOTED_IDENTIFIER ON
 4 GO
 5 -- =============================================
 6 --      Author:    Blue Balze
 7 -- Create date: 2019-06-18
 8 -- Description:    客商资料插入、更新时，把数据插入接口表
 9 -- =============================================
10 ALTER TRIGGER [dbo].[trg_BusinessDoc_InsertOrUpdate]
11 ON [dbo].[BUSINESSDOC]
12 FOR INSERT, UPDATE
13 AS
14 BEGIN
15 
16   SET NOCOUNT ON ;
17 
18   -- 表插入操作
19   IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
20   BEGIN
21     INSERT INTO dbo.zzwms_businessdoc
22     SELECT   a.ENTID, a.BUSINESSID, a.BUSINESSCODE, a.BUSINESSNAME, a.LOGOGRAM, a.CONTACT, a.TELEPHONE
23           , a.ADDRESS, ‘‘ AS BUSINESSCONT, ‘‘ AS ROUTE, a.IS_SUPP, ‘‘ AS CLIENTS, a.beactive, CONVERT(VARCHAR(19),GETDATE(),120) AS CREATETIME
24           , ‘‘ AS LASTMODIFYTIME, 0 AS ZT
25     FROM   Inserted a(NOLOCK)
26     WHERE  a.beactive=‘Y‘ ;
27   END ;
28 
29   -- 表更新操作
30   IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
31   BEGIN
32     IF ( UPDATE(ENTID) OR UPDATE(BUSINESSID) OR UPDATE(BUSINESSCODE) OR UPDATE(BUSINESSNAME) OR UPDATE(LOGOGRAM) OR 
33          UPDATE(TELEPHONE) OR UPDATE(TELEPHONE) OR UPDATE(BUSINESSCONT) OR UPDATE(IS_SUPP) OR UPDATE(beactive)
34        )
35     BEGIN       
36       -- 插入前清除未上传的资料
37       DELETE b 
38       FROM Inserted a 
39            INNER JOIN dbo.zzwms_businessdoc b ON a.BusinessId=b.BusinessId AND a.EntId=b.ENTID
40       WHERE b.ZT=0
41 
42       INSERT INTO dbo.zzwms_businessdoc
43       SELECT   a.ENTID, a.BUSINESSID, a.BUSINESSCODE, a.BUSINESSNAME, a.LOGOGRAM, a.CONTACT, a.TELEPHONE
44             , a.ADDRESS, ‘‘ AS BUSINESSCONT, ‘‘ AS ROUTE, a.IS_SUPP, ‘‘ AS CLIENTS, a.beactive, CONVERT(VARCHAR(19),GETDATE(),120) AS CREATETIME
45             , ‘‘ AS LASTMODIFYTIME, 0 AS ZT
46       FROM   Inserted a(NOLOCK)
47              INNER JOIN deleted b(NOLOCK)ON a.BusinessId=b.BusinessId
48 
49     END
50   END 
51 END