MS SQLSERVER 2000,写一个update触发器,当表vdate的列column1(int 类型)和column2(int 类型)中的值比执行update语句前小就不修改数据,否则就修改。
如:
id column1 column2
1 2323 222222执行update语句,update vdate set column1=2,column2=222223 where id=1 ,因为修改后的column1大于修改前的column1,所以此条语句将不执行,如果修改后的column2大于修改前的column2也做这样处理。
如:
id column1 column2
1 2323 222222执行update语句,update vdate set column1=2,column2=222223 where id=1 ,因为修改后的column1大于修改前的column1,所以此条语句将不执行,如果修改后的column2大于修改前的column2也做这样处理。
AS
BEGIN
.......
你举的例子有点不正确吧
if object_id('tb') is not null drop table tb
go
create table tb (id int, column1 int, column2 int )
insert into tb
select 1 , 2323 , 222222
if object_id('gg') is not null drop trigger gg
gocreate trigger gg on tb
for update
as
update tb
set column1=case when d.column1>i.column1 then d.column1 else i.column1 end,
column2=case when d.column2>i.column2 then d.column2 else i.column2 end
from tb a ,inserted i,deleted d
where a.id=i.id and i.id=d.id
go -------------------------
update tb
set column1=2, -----修改后小,不修改
column2=22222222 -----修改后大, 修改select * from tb id column1 column2
----------- ----------- -----------
1 2323 22222222(1 行受影响)
AS
BEGIN
DECLARE @COL1 INT,@COL2 INT
DECLARE @OLDCOL1 INT,@OLDCOL2 INT
SELECT @COL1=COLUMN1,@COL2=COLUMN2 FROM INSERTED
SELECT @OLDCOL1=COLUMN1,@OLDCOL2=COLUMN2 FROM VDATE WHERE ID IN( SELECT ID FROM INSERTED)
IF (@COL1<@OLDCOL1) OR (@COL2<@OLDCOL2)
..
ELSE
应该是类似这样吧
on vdate
instead of update
as
update a set column1=b.column1,column2=b.column2 from vdate a inner join inserted b on a.id=b.id
where a.column1>b.column1 and a.column2>b.column2