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

I want to create this trigger in such a way that I am not repeating the same code in the first if statement and the second if statement. Here the similar insert statement is repeating in both the if statements, how can I manage that more efficiently?

CodePudding user response：

You can try CROSS APPLY to get 0..2 log rows for every updated row depending on condition

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;

CodePudding user response：

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;