SQL SERVER 触发器

-- 触发器测试案例
CREATE TABLE [dbo].[CeShi](
    [Id] [int] PRIMARY KEY IDENTITY, 
    [Name] [varchar](50) NULL,
    [CreateTime] [datetime] NULL,
)
CREATE TABLE [dbo].[CeShi_DataTaskTDWP](
    [Id] [int] PRIMARY KEY IDENTITY, 
    [CId] [int] NOT NULL,
    [Name] [varchar](50) NULL,
    [State] [int]  NULL,
    [CreateTime] [datetime] NULL,
)

IF (EXISTS(SELECT * FROM sysobjects WHERE id=object_id(N'Trigger_CeShi') AND OBJECTPROPERTY(id, N'IsTrigger') = 1))
DROP TRIGGER Trigger_CeShi
GO
--创建触发器
Create trigger Trigger_CeShi
on CeShi
after update,delete,insert--这里也能判断是修改，删除，还是新增
as
declare @id int
declare @did int
select @id=Id from inserted
if exists(select 1 from inserted) and exists(select 1 from deleted)
  begin
      --修改
      insert into CeShi_DataTaskTDWP  VALUES (@id,'B01',0,GETDATE())
  end
else if (exists (select 1 from inserted) and not exists (select 1 from deleted))
  begin
      --新增
       insert into CeShi_DataTaskTDWP VALUES (@id,'B01',21,GETDATE())
  end
else if (not exists (select 1 from inserted) and exists (select 1 from deleted))
    begin
      --删除
      select @did=Id from deleted
      insert into CeShi_DataTaskTDWP  VALUES (@did,'B01',27,GETDATE())
    end

GO

测试

truncate table CeShi

SELECT * FROM CeShi
insert into CeShi  Values('测试01',getdate())
update CeShi Set CreateTime=getdate() where ID=1
delete from CeShi where Id=1