create table t
(col varchar(20))insert t
select '2' union all
select '3' union all
select '4' union all
select '5' union all
select '6'
go
create trigger t_update
on t
for update
as
set xact_abort on
begin tran
select * from inserted
commit tran
goupdate t set col=case col when '2' then '4'
when '3' then '5'
when '4' then '6'
else col end
drop trigger t_update
drop table t
col
--------------------
4
5
6
5
6(所影响的行数为 5 行)
(所影响的行数为 5 行)
(col varchar(20))insert t
select '2' union all
select '3' union all
select '4' union all
select '5' union all
select '6'
go
create trigger t_update
on t
for update
as
set xact_abort on
begin tran
select * from inserted
commit tran
goupdate t set col=case col when '2' then '4'
when '3' then '5'
when '4' then '6'
else col end
drop trigger t_update
drop table t
col
--------------------
4
5
6
5
6(所影响的行数为 5 行)
(所影响的行数为 5 行)
create table t
(col varchar(20))insert t
select '2' union all
select '3' union all
select '4' union all
select '5' union all
select '6'
go
create trigger t_update
on t
for update
as
set xact_abort on
begin tran
select * from deleted
commit tran
goupdate t set col=case col when '2' then '4'
when '3' then '5'
when '4' then '6'
else col end
drop trigger t_update
drop table tcol
--------------------
2
3
4
5
6(所影响的行数为 5 行)
2. 如果你的表有标识列, 可以用@@identity返回刚插入记录的标识值, 通过这个标识值可以查到插入的记录
GOCREATE TABLE dbo.ta
(
ID int
)
INSERT dbo.ta SELECT TOP (CAST(RAND()*100 as int)) id FROM sysobjects
CREATE TABLE dbo.ta_deletelog
(
ID int,
userid int
)
GODECLARE @deleted TABLE(ID int)
DELETE dbo.ta
OUTPUT deleted.* INTO @deleted
INSERT dbo.ta_deletelog SELECT ID, @@SPID FROM @deleted
SELECT * FROM dbo.ta_deletelog
GODROP TABLE dbo.ta_deletelog, dbo.ta
delete 操作会保存在 deleted 里
update 操作其实就是一个先delete操作,后insert操作的过程!