sql触发器

添加，修改删除还是分开写好，我测试时候修改，删除里也有数据（DELETED），不在为啥

先建立一张表，然后在copy一份

USE [newsData]
GO

/****** Object:  Table [dbo].[op_weight_cemskind]    Script Date: 09/12/2020 17:05:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[op_weight_cemskind](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [cemskind] [varchar](30) NOT NULL,
    [icid] [int] NOT NULL,
    [isticket] [bit] NOT NULL,
    [isfuel] [bit] NOT NULL,
    [cinput] [varchar](30) NOT NULL,
    [ddate] [datetime] NOT NULL,
 CONSTRAINT [PK_op_weight_cemskind] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

然后建立触发器，我修改的时候Inserted和deleted都有数据，不知道为啥

USE [newsData]
GO

/****** Object:  Trigger [dbo].[copydata]    Script Date: 09/12/2020 17:08:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE TRIGGER 触发器名称 ON 触发器所在的表名 
   AFTER INSERT,UPDATE,DELETE --触发的操作
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for trigger here
     IF EXISTS (SELECT 1 FROM Inserted)--判断添加修改Inserted是否有数据
      BEGIN
        IF NOT EXISTS(SELECT TOP 1 1 FROM dbo.op_weight_cemskindcopy WHERE id in(SELECT id FROM Inserted))--关联查询是否有数据，没有就是添加操作
         BEGIN
           INSERT INTO dbo.op_weight_cemskindcopy
                   ( id, 
                     cemskind ,
                     icid ,
                     isticket ,
                     isfuel ,
                     cinput ,
                     ddate
                   )
           SELECT    a.id,
                     a.cemskind ,
                     a.icid ,
                     a.isticket ,
                     a.isfuel ,
                     a.cinput ,
                     a.ddate
           FROM INSERTED a
         END
        ELSE --修改操作
         BEGIN
           UPDATE b
           SET b.cemskind=a.cemskind,
               b.icid=a.icid ,
               b.isticket=a.isticket ,
               b.isfuel=a.isfuel ,
               b.cinput=a.cinput ,
               b.ddate=a.ddate
           FROM INSERTED a
           LEFT JOIN dbo.op_weight_cemskindcopy b  ON a.id=b.id
         END
      END

     ELSE IF EXISTS(SELECT 1 FROM DELETED)--删除操作
      BEGIN
        DELETE dbo.op_weight_cemskindcopy WHERE id in(SELECT id FROM deleted)
      END
    
END

GO