曾經(jīng)以為SQL SERVER的觸發(fā)器只能觸發(fā)單行，也就是說(shuō)如果一個(gè)delete觸發(fā)器，如果同時(shí)刪除多行時(shí)，只會(huì)對(duì)第一條記錄觸發(fā)，后來(lái)發(fā)現(xiàn)了不是人家SQL SERVER不支持，而是偶腦子笨沒(méi)發(fā)現(xiàn)。

其實(shí)inserted和deleted兩張內(nèi)部表存放了所有要插入或要?jiǎng)h除的記錄，可以用cursor逐次訪問(wèn)里面的每條記錄，下面是一個(gè)示例，該觸發(fā)器將要?jiǎng)h除的記錄轉(zhuǎn)移到另一張表中：

第一步，創(chuàng)建這兩張表

create table table1([id] int primary key, [value] varchar(100))create table table2([id] int primary key, [value] varchar(100))第二步，插入測(cè)試數(shù)據(jù)

declare @i intset @i = 1while @i <= 100begin;insert into table1([id], [value]);values(@i, cast(@i as varchar));set @i = @i + 1end創(chuàng)建table1的delete觸發(fā)器

create trigger tr_d_table1 on table1 for deleteasbegin;declare @id int, @value varchar(100);declare cur_del cursor local forward_only for;select [id], [value];from deleted;open cur_del;fetch next from cur_del into @id, @value;while @@fetch_status = 0;begin;;;;;insert into table2([id], [value]);;;;;values(@id, @value);fetch next from cur_del into @id, @value;end;close cur_del;deallocate cur_del;end現(xiàn)在對(duì)table1執(zhí)行delete語(yǔ)句，發(fā)現(xiàn)所有被刪除的記錄都記錄在在table2中了

delete from table1