sqlserver中
--触发器代码
Create trigger
T_Operation_PipeInfo On
PipeInfo for
insert,update,delete As declare @OpStatus
char(1); declare @Status
char(1); declare @ncount
int; declare @ocount
int; select @ncount= count(0) from
inserted; select @ocount=count(0) from
deleted; set
@Status=1;--1,准备上传;2,处理中;3,已成功。 if(@ocount>0 and
@ncount=0)--此逻辑表示删除操作 begin set
@OpStatus=2;--表示删除操作 insert into TransactionLog(TableName, A, AreaCode, RecordID,
Status, OpStatus) select 'PipeInfo',A,AreaCode,id,@Status,@OpStatus from
deleted; end else if(@ocount>0 or
@ncount>0)--c此逻辑表示修改或者添加 begin set
@OpStatus=1;--表示添加或者更新操作 insert into TransactionLog(TableName, A, AreaCode, RecordID,
Status,
OpStatus) select 'PipeInfo',A,AreaCode,id,@Status,@OpStatus from
inserted; end;
--涉及的表
CREATE TABLE [dbo].[TransactionLog](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TableName] [nvarchar](50) NULL,
[A] [int] NULL,
[AreaCode] [varchar](6) NULL,
[RecordID] [bigint] NULL,
[Status] [char](1) NULL,
[OpStatus] [char](1) NULL,
CONSTRAINT [PK_TransactionLog] 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
CREATE TABLE [dbo].[PipeInfo](
[ID] [int] IDENTITY(1,1) NOT NULL,
[A] [int] NULL,
[AreaCode] [varchar](6) NULL,
[Name] [varchar](100) NOT NULL,
[Code] [varchar](10) NOT NULL,
[Len] [decimal](6, 2) NULL,
[Caliber] [int] NULL,
[Material] [varchar](50) NULL,
[Depth] [decimal](6, 2) NULL,
[BuriedTime] [datetime] NULL,
[Remarks] [varchar](200) NULL,
[WorksID] [int] NULL,
[ParentId] [int] NULL,
CONSTRAINT [PK_PipeInfo] 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
------------------------------------------
mysql中
DELIMITER |
Create trigger
T_Operation_PipeInfo before insert
On PipeInfo
for each row
begin
declare OpStatus
char(1); declare Status
char(1); declare ncount
int; declare ocount
int; select count(0) into @ncount from
inserted; select count(0) into @ocount from
deleted; set
@Status=1; if(@ocount>0 and @ncount=0)
then set
@OpStatus=2; insert into
TransactionLog(TableName, A, AreaCode, RecordID, Status,
OpStatus) select
'PipeInfo',A,AreaCode,id,@Status,@OpStatus from deleted;
end
if; if(@ocount>0 or
@ncount>0)
then set
@OpStatus=1; insert into
TransactionLog(TableName, A, AreaCode, RecordID, Status,
OpStatus) select
'PipeInfo',A,AreaCode,id,@Status,@OpStatus from
inserted; end if;
end|
--------------
mysql中不支持多个触发事件,但是移植后没有注意SQlServer中的inserted、deleted两个表,移植失败
-----------
使用mysq的NEW、OLD来替代inserted、deleted
------------------------------------------
insert触发事件
DELIMITER |
Create trigger
T_Operation_PipeInfo before insert
On PipeInfo
for each row
begin
set @Status=1;--开始上传
set @OpStatus=1;--添加
insert into
TransactionLog(TableName, A, AreaCode, RecordID, Status,
OpStatus) values('pipeinfo',NEW.A,NEW.AreaCode,NEW.id,
@Status,
@OpStatus); end|
----------------
delete触发事件
DELIMITER |
Create trigger
T_Operation_PipeInfo_d before delete
On PipeInfo
for each row
begin
set @Status=2;--正在处理
set @OpStatus=2;--删除操作
insert into
TransactionLog(TableName, A, AreaCode, RecordID, Status,
OpStatus) values('pipeinfo',OLD.A,OLD.AreaCode,OLD.id,
@Status,
@OpStatus); end|
------------------------
update触发事件
DELIMITER |
Create trigger
T_Operation_PipeInfo_up before update
On PipeInfo
for each row
begin
set @Status=2;--正在处理
set @OpStatus=3;--更新操作
insert into
TransactionLog(TableName, A, AreaCode, RecordID, Status,
OpStatus) values('pipeinfo',NEW.A,NEW.AreaCode,NEW.id,
@Status, @OpStatus);
set @Status=3;--处理完成
set @OpStatus=3; --更新操作
insert into
TransactionLog(TableName, A, AreaCode, RecordID, Status,
OpStatus) values('pipeinfo',OLD.A,OLD.AreaCode,OLD.id,
@Status,
@OpStatus); end|
----------------------------------------------------------------------------------------OK
SqlServer关于inserted、deleted两个表的介绍
create trigger updateDeleteTime2 on user3 forupdate4 as5 begin6 update usersetUpdateTime=(getdate()) from user inner join inserted on user.UID=Inserted.UID7 end
上面的例子是在执行更新操作的时候同时更新,一下修改时间。
关键在于Inserted表
触发器语句中使用了两种特殊的表:deleted 表和 inserted 表。
Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。在执行 DELETE 或 UPDATE 语句时,行从触发器表中删除,并传输到
deleted 表中。Deleted 表和触发器表通常没有相同的行。
Inserted 表用于存储 INSERT
和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中,新建行被同时添加到 inserted
表和触发器表中。Inserted 表中的行是触发器表中新行的副本。
1.插入操作(Insert) Inserted表有数据,Deleted表无数据 2.删除操作(Delete) Inserted表无数据,Deleted表有数据 3.更新操作(Update) Inserted表有数据(新数据),Deleted表有数据(旧数据)
--------------------------------------------------------------------------
Mysql中关于NEW和OLd两个表的介绍
NEW和OLD记录的是"影响的结果集",可以理解为是变量
也可以理解为临时表,并不能当做表来select
OLD表示旧数据,NEW表示新数据
insert触发事例只对NEW有效
delete触发事例只对OLD有效
update触发事例对NEW和OLD都有效
---------------------------------------------------------------------------------