更新触发器问题
如果某个字段更新值为2，就执行这个触发器
[解决办法]
你应该在触发器里面判断是否有数据行某个字段更新为２，
如楼上所说.
[解决办法]
CREATE TRIGGER mytrig ON tbname FOR UPDATE
AS
if col = 2
...............
go
[解决办法]
/*------------------------
create table tb(nId int identity(1,1) primary key, colForUpdate int)
go
CREATE TRIGGER uTrigOntb ON tb
AFTER UPDATE
AS
BEGIN
declare @nHasUpdateTo2 int
select @nHasUpdateTo2=count(1) from inserted I join deleted D on I.nId=D.nId
where I.colForUpdate=2 and D.colForUpdate<>2
if @nHasUpdateTo2>0
begin
print '有' + rtrim(@nHasUpdateTo2) + '行数据的colForUpdate列被改为2'
--do something else
end
END
GO
insert tb select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
update tb set colForUpdate=2
where colForUpdate>=5
drop trigger uTrigOntb
drop table tb
------------------------*/
(6 row(s) affected)
有2行数据的colForUpdate列被改为2
(2 row(s) affected)
[解决办法]
create table tb(nId int identity(1,1) primary key, colForUpdate int)goCREATE TRIGGER uTrigOntb ON tb AFTER UPDATEAS BEGIN declare @nHasUpdateTo2 int select @nHasUpdateTo2=count(1) from inserted I join deleted D on I.nId=D.nId where I.colForUpdate=2 and D.colForUpdate<>2 if @nHasUpdateTo2>0 begin print '有' + rtrim(@nHasUpdateTo2) + '行数据的colForUpdate列被改为2' --do something else endENDGOinsert tb select 1union all select 2union all select 3union all select 4union all select 5union all select 6update tb set colForUpdate=2where colForUpdate>=5drop trigger uTrigOntbdrop table tb
[解决办法]
create table meixiaofeng(nId int identity(1,1) primary key, colForUpdate int)
go
CREATE TRIGGER uTrigOntb ON meixiaofeng
AFTER UPDATE
AS
BEGIN
declare @nHasUpdateTo2 int
select @nHasUpdateTo2=count(1) from inserted I join deleted D on I.nId=D.nId
where I.colForUpdate=2 and D.colForUpdate < >2
if @nHasUpdateTo2 >0
begin
print '有 ' + rtrim(@nHasUpdateTo2) + '行数据的colForUpdate列被改为2 '
--do something else
end
END
GO
insert meixiaofeng select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
update meixiaofeng set colForUpdate=2
where colForUpdate >=5
