我如何以更有效的方式创建这个触发器?

Question

create trigger dbo.trg_active_problem
on dbo.active_problem
after update
not for replication
as 
begin
if @@ROWCOUNT = 0 return
set nocount on;
if update (diagnosis)
begin
insert into dbo.dmlactionlog(
schemaname, 
tablename, 
affectedcolumn,
oldvalue,
newvalue
) 
select  
OBJECT_SCHEMA_NAME(@@PROCID, DB_ID()),
OBJECT_NAME(Object_id('active_problem'), DB_ID()),
'diagnosis',
d.diagnosis,
i.diagnosis
from inserted i
join deleted d on i.active_problem_id = d.active_problem_id
and coalesce(i.diagnosis, '') != coalesce(d.diagnosis, '')
end
if update (type)
begin
insert into dbo.dmlactionlog(
schemaname, 
tablename, 
affectedcolumn,
oldvalue,
newvalue
) 
select  
OBJECT_SCHEMA_NAME(@@PROCID, DB_ID()),
OBJECT_NAME(Object_id('active_problem'), DB_ID()),
'type',
d.[type],
i.[type]
from inserted i
join deleted d on i.active_problem_id = d.active_problem_id
and coalesce(i.type, '') != coalesce(d.type, '')
end
end;
go

我想以这样一种方式创建这个触发器，即我不会在第一个if语句和第二个if语句中重复相同的代码。这里，类似的插入语句在两个if语句中重复，我如何更有效地管理它?

Answer 1

你可以尝试交叉应用得到0..根据条件

，每更新一行对应2个日志行

create trigger dbo.trg_active_problem
on dbo.active_problem
after update
not for replication
as 
begin
set nocount on;
if update (diagnosis) or update (type)
begin
insert into dbo.dmlactionlog(
schemaname, 
tablename, 
affectedcolumn,
oldvalue,
newvalue
) 
select  
OBJECT_SCHEMA_NAME(@@PROCID, DB_ID()),
OBJECT_NAME(Object_id('active_problem'), DB_ID()),
upd.cn,
upd.oldv,
upd.newv
from inserted i
join deleted d on i.active_problem_id = d.active_problem_id
cross apply (
select 
'diagnosis' cn ,
d.diagnosis oldv,
i.diagnosis newv
where coalesce(i.diagnosis, '') != coalesce(d.diagnosis, '')
union all
select 
'type',
d.[type],
i.[type]
where coalesce(i.type, '') != coalesce(d.type, '')
) upd
end;

Answer 2

create trigger dbo.trg_active_problem
on dbo.active_problem
after update
not for replication
as 
begin
if @@ROWCOUNT = 0 return
set nocount on;
WITH
T1 AS (select OBJECT_SCHEMA_NAME(@@PROCID, DB_ID()) AS S,
OBJECT_NAME(Object_id('active_problem'), DB_ID()) AS O
),
T2 AS ( select  
'diagnosis',
d.diagnosis,
i.diagnosis
from inserted i
join deleted d on i.active_problem_id = d.active_problem_id
WHERE update (diagnosis)
UNION ALL 
select  
'type',
d.[type],
i.[type]
from inserted i
join deleted d on i.active_problem_id = d.active_problem_id
and coalesce(i.type, '') != coalesce(d.type, '')
WHERE update (type))
insert into dbo.dmlactionlog(
schemaname, 
tablename, 
affectedcolumn,
oldvalue,
newvalue
) 
SELECT T1.*, T2.*
FROM   T1 CROSS JOIN T2;