sql server2005中的一个问题
假如我现在有一个表info:属性列是
no(非主键) grade
现在已经有一条记录:
12 88
如果我现在做 “插入”(不是更新操作)时:
insert into info values(12,99)
我希望这条记录覆盖刚才的那条记录,即最终数据库中数据应该是这样
no grade
12 99
这个时候我该怎么做,需要用触发器还什么?谢谢!
[解决办法]
用触发器实现,
create table info
(no int,grade int)
insert into info
select 12,88
-- 建触发器
create trigger tr_info
on info instead of insert
as
begin
delete a
from info a
inner join inserted b on a.no=b.no
insert into info(no,grade)
select no,grade from inserted
end
-- 插入
insert into info values(12,99)
-- 结果
select * from info
/*
no grade
----------- -----------
12 99
(1 row(s) affected)
*/
create TRIGGER t ON info
INSTEAD OF INSERT
as
IF EXISTS (SELECT 1 FROM INSERTED a INNER JOIN info b ON a.[NO]=b.[no] AND a.grade<>b.grade)
UPDATE info SET info.grade=b.grade FROM info INNER JOIN INSERTED b ON info.[NO]=b.[no] AND info.grade<>b.grade
ELSE
INSERT INTO info SELECT * FROM INSERTED