判断数据库表dml操作的类型

 判断数据库dml操作的类型
-- 演示准备
if object_id (N ' testing ' , ' U ' ) is not null
drop table testing
go
create table testing
(id smallint
)
go
if object_id (N ' tr_dml_type_testing ' , ' TR ' ) is not null
drop trigger tr_dml_type_testing
go
create trigger tr_dml_type_testing on testing
for insert , delete , update
as
if exists ( select 1 from inserted) and not exists ( select 1 from deleted)
print ' the action is insert '
if exists ( select 1 from inserted) and   exists ( select 1 from deleted)
print ' the action is update '
if not exists ( select 1 from inserted) and   exists ( select 1 from deleted)
print ' the action is delete '
-- 测试
insert into testing select 1

/*
the action is insert

(1 行受影响)
*/
update testing set id = 2
/*
the action is update

(1 行受影响)
*/

delete from testing
/*
the action is delete

(1 行受影响)
*/

-- 删除测试
drop table testing