create trigger tr_tb1 on tb1 for update as DECLARE @name varchar(5), @desc varchar(5), @iqty varchar(5), @ddelaydate varchar(10), @vre varchar(5)
select @name=case when i.vname=d.vname then '' else 'vname' end, @desc=case when i.vdesc=d.desc then '' else 'vdesc' end, @iqty=case when i.iqty=d.iqty then '' else 'iqty' end, @ddelaydate = case when i.ddelaydate=d.ddelaydate else 'ddelaydate' end, @vre = case when i.vremar=d.vre then 'vre' end from inserted i,deleted d where i.id = d.id if (@name <> '') begin insert into tb2(idt,vfield,vmodif1,vmodif2) select i.id,@name,i.vname,d.vname from inserted i, deleted d where i.id=d.id end if (@desc <> '') begin insert into tb2(idt,vfield,vmodif1,vmodif2) select i.id,@desc,i.vdesc,d.vdesc from inserted i, deleted d where i.id=d.id end if (@iqty <> '') begin insert into tb2(idt,vfield,vmodif1,vmodif2) select i.id,@iqty,i.iqty,d.iqty from inserted i, deleted d where i.id=d.id end if (@ddelaydate <> '') begin insert into tb2(idt,vfield,vmodif1,vmodif2) select i.id,@ddelaydate,i.ddelaydate,d.ddelaydate from inserted i, deleted d where i.id=d.id end if (@vre <> '') begin insert into tb2(idt,vfield,vmodif1,vmodif2) select i.id,@vre,i.vre,d.vre from inserted i, deleted d where i.id=d.id end go
---删除时触发器if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tb1] GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tb2] GOCREATE TABLE [dbo].[tb1] ( [id] [int] IDENTITY (1, 1) NOT NULL , [vName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [vDesc] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [iQty] [int] NULL , [dDelayDate] [datetime] NULL , [vRe] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GOCREATE TABLE [dbo].[tb2] ( [id] [int] IDENTITY (1, 1) NOT NULL , [idt] [int] NULL , [vField] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [vModif1] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [vModif2] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GOCREATE TRIGGER ModifyData ON dbo.tb1 FOR DELETE AS declare @Id int declare @vName varchar(20) declare @vDesc varchar(60) declare @iQty int declare @dDelayDate datetime declare @vRe varchar(50) select @Id=[Id], @vName=vName,@vDesc=vDesc,@iQty=iQty ,@dDelayDate=dDelayDate, @vRe=vRe from Deleted insert into tb2( idt , vField , vModif1) values (@Id,'vName',@vName) insert into tb2( idt , vField , vModif1) values (@Id,'vDesc',@vDesc) insert into tb2( idt , vField , vModif1) values (@Id,'iQty',cast(@iQty as varchar(40))) insert into tb2( idt , vField , vModif1) values (@Id,'dDelayDate',cast(@dDelayDate as varchar(40))) insert into tb2( idt , vField , vModif1) values (@Id,'vRe',@vRe)GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
on tb1
for update
as
DECLARE @name varchar(5),
@desc varchar(5),
@iqty varchar(5),
@ddelaydate varchar(10),
@vre varchar(5)
select @name=case when i.vname=d.vname then '' else 'vname' end,
@desc=case when i.vdesc=d.desc then '' else 'vdesc' end,
@iqty=case when i.iqty=d.iqty then '' else 'iqty' end,
@ddelaydate = case when i.ddelaydate=d.ddelaydate else 'ddelaydate' end,
@vre = case when i.vremar=d.vre then 'vre' end
from inserted i,deleted d
where i.id = d.id if (@name <> '')
begin
insert into tb2(idt,vfield,vmodif1,vmodif2)
select i.id,@name,i.vname,d.vname from inserted i, deleted d
where i.id=d.id
end
if (@desc <> '')
begin
insert into tb2(idt,vfield,vmodif1,vmodif2)
select i.id,@desc,i.vdesc,d.vdesc from inserted i, deleted d
where i.id=d.id
end
if (@iqty <> '')
begin
insert into tb2(idt,vfield,vmodif1,vmodif2)
select i.id,@iqty,i.iqty,d.iqty from inserted i, deleted d
where i.id=d.id
end
if (@ddelaydate <> '')
begin
insert into tb2(idt,vfield,vmodif1,vmodif2)
select i.id,@ddelaydate,i.ddelaydate,d.ddelaydate from inserted i, deleted d
where i.id=d.id
end
if (@vre <> '')
begin
insert into tb2(idt,vfield,vmodif1,vmodif2)
select i.id,@vre,i.vre,d.vre from inserted i, deleted d
where i.id=d.id
end
go
drop table [dbo].[tb1]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tb2]
GOCREATE TABLE [dbo].[tb1] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[vName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vDesc] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iQty] [int] NULL ,
[dDelayDate] [datetime] NULL ,
[vRe] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[tb2] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[idt] [int] NULL ,
[vField] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vModif1] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vModif2] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE TRIGGER ModifyData ON dbo.tb1
FOR DELETE
AS
declare @Id int
declare @vName varchar(20)
declare @vDesc varchar(60)
declare @iQty int
declare @dDelayDate datetime
declare @vRe varchar(50)
select @Id=[Id], @vName=vName,@vDesc=vDesc,@iQty=iQty ,@dDelayDate=dDelayDate,
@vRe=vRe
from Deleted
insert into tb2( idt , vField , vModif1) values (@Id,'vName',@vName)
insert into tb2( idt , vField , vModif1) values (@Id,'vDesc',@vDesc)
insert into tb2( idt , vField , vModif1) values (@Id,'iQty',cast(@iQty as varchar(40)))
insert into tb2( idt , vField , vModif1) values (@Id,'dDelayDate',cast(@dDelayDate as varchar(40)))
insert into tb2( idt , vField , vModif1) values (@Id,'vRe',@vRe)GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
sqlserver不支持元组级触发.