CREATE OR ALTER TRIGGER TR_OAE_KOMPONENT_cap ON DBO.OAE_KOMPONENT FOR DELETE, UPDATE, INSERT AS 
BEGIN 
  DECLARE @l_function VARCHAR( 99 ); 
  SET @l_function = 'UPDATE'; 
  if NOT EXISTS (SELECT * FROM deleted) 
    SET @l_function = 'INSERT'; 
  if NOT EXISTS (SELECT * FROM inserted) 
    SET @l_function = 'DELETE'; 
  if NOT EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
    SET @l_function = 'NONE'; 
  --//. 
  if @l_function = 'INSERT' 
    insert into cap_4_dwh( tablename,  "function", type, cap_status,  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate )     
    select 'OAE_KOMPONENT',  @l_function, 'AFTER', 'OPEN',  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate
      from inserted ; 
  else if @l_function = 'DELETE' 
    insert into cap_4_dwh( tablename,  "function", type, cap_status,  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate ) 
    select 'OAE_KOMPONENT',  @l_function, 'PRE', 'OPEN',  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate
      from deleted ; 
  else if @l_function = 'UPDATE' 
  BEGIN
    insert into cap_4_dwh ( tablename,  "function", type, cap_status,  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate ) 
    select 'OAE_KOMPONENT',  @l_function, 'PRE', 'OPEN', d. cmpCode, d.code, d.kmplevel, d.name, d.sName, d.accountType, d.statUser, d.delDate
      from deleted d, inserted i 
     where d.cmpcode = i.cmpcode and d.code = i.code and d.kmplevel = i.kmplevel
       and (  d.sname != i.sname or  d.accounttype != i.accounttype or  d.statuser != i.statuser or 
              d.name != i.name or  d.deldate != i.deldate or ( d.deldate is NULL and i.deldate is NOT null ) or ( d.deldate is NOT null and i.deldate is NULL )  );
    insert into cap_4_dwh ( tablename,  "function", type, cap_status,  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate ) 
    select 'OAE_KOMPONENT',  @l_function, 'AFTER', 'OPEN', i. cmpCode, i.code, i.kmplevel, i.name, i.sName, i.accountType, i.statUser, i.delDate
      from deleted d, inserted i 
     where d.cmpcode = i.cmpcode and d.code = i.code and d.kmplevel = i.kmplevel
       and (  d.sname != i.sname or  d.accounttype != i.accounttype or  d.statuser != i.statuser or 
              d.name != i.name or  d.deldate != i.deldate or ( d.deldate is NULL and i.deldate is NOT null ) or ( d.deldate is NOT null and i.deldate is NULL )  );
  END;
END; 

3 rows updated

6 rows inserted

3 rows updated

0 rows inserted

set nocount on;

set nocount on;

with l as ( select l_function = case when EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) then 'UPDATE' 
                                     when EXISTS (SELECT * FROM inserted) then 'INSERT'
                                     when EXISTS (SELECT * FROM deleted ) then 'DELETE'
									 else 'NONE'
								end
          )
insert into cap_4_dwh ( tablename,  "function", cap_status, type,  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate ) 
   select 'OAE_KOMPONENT',  (select l_function from l),  'OPEN', *
      from (
			   select 'PRE',  *
				 from (
						select  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate from deleted
						except 
						select  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate from inserted
					  ) as x
			   union all
			   select 'AFTER', *
				 from (
						select  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate from inserted
						except 
						select  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate from deleted
					  ) as x
		   ) as y

use tempdb;
go

create table dbo.t (id int primary key, a int, b int, c int);
create table dbo.t_log (id int identity primary key, [function] varchar(30), [type] varchar(30), t_id int, a int, b int, c int, dt datetime2 default sysdatetime());
go

create trigger dbo.tr_t__log
on dbo.t
after insert, update, delete
as
begin
 set nocount on;

 insert into dbo.t_log
  ([function], [type], t_id, a, b, c)
 select
  t.[function], t.[type], t.id, t.a, t.b, t.c
 from
  inserted i full join
  deleted d on d.id = i.id cross apply
  (
   select 'after', 'open', i.id, i.a, i.b, i.c where i.id is not null
   union all
   select 'pre', 'open', d.id, d.a, d.b, d.c where d.id is not null
  ) t([function], [type], id, a, b, c)
 where
  not exists(
   select i.id, i.a, i.b, i.c
   intersect
   select d.id, d.a, d.b, d.c
  );
end;
go

insert into dbo.t (id, a, b, c) values (1, 1, 1, 1);
insert into dbo.t (id, a, b, c) values (2, 2, 2, 2);

delete from dbo.t where id = 1;

update dbo.t set b = 40 where id = 2;
update dbo.t set b = 40 where id = 2;
update dbo.t set id = 3 where id = 2;

select * from dbo.t_log order by id;
go

drop table dbo.t, dbo.t_log;
go