kreta/Kreta.DataAccess.Migrations/DBScripts/Database/sp_Global_CreateTriggers.sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('dbo.sp_Global_CreateTriggers') IS NOT NULL BEGIN
  DROP PROCEDURE [dbo].[sp_Global_CreateTriggers]
END
GO

CREATE PROCEDURE [dbo].[sp_Global_CreateTriggers]
  @tableName nvarchar(30)
AS BEGIN
  DECLARE 
    @sel nvarchar(max),
    @unpvt nvarchar(max),
    @sql nvarchar(max)
    
  SELECT 
    @unpvt = ISNULL(@unpvt + ', ', '') + c.COLUMN_NAME,
    @sel = ISNULL(@sel + ', ', '') + IIF(c.DATA_TYPE IN ('date', 'datetime'),'CONVERT(nvarchar(MAX), ' + c.COLUMN_NAME + ', 121)', 'CAST( ' + c.COLUMN_NAME + ' AS nvarchar(MAX))') + ' AS ' + c.COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS c 
  WHERE c.TABLE_NAME = @tableName 
    AND c.COLUMN_NAME NOT IN (N'ID', N'C_INTEZMENYID', N'C_ALINTEZMENYID', N'C_TANEVID', N'C_ALTANEVID', N'CREATED', N'LASTCHANGED', N'SERIAL', N'CREATOR', N'MODIFIER')
    AND c.TABLE_SCHEMA = 'dbo'
    AND c.DATA_TYPE NOT IN ('binary', 'varbinary');
    
  DECLARE @tanevCol nvarchar(30),
    @intezmenyCol nvarchar(30)

  SELECT @intezmenyCol = c.COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS c 
  WHERE c.COLUMN_NAME LIKE '%INTEZMENYID' 
    AND c.TABLE_NAME = @tableName 
    AND c.TABLE_SCHEMA = 'dbo'

  SELECT @tanevCol = c.COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS c 
  WHERE c.COLUMN_NAME LIKE '%TANEVID' 
    AND c.TABLE_NAME = @tableName 
    AND c.TABLE_SCHEMA = 'dbo'
  
  SET @sql = '
  IF OBJECT_ID(''tr_' + LOWER(SUBSTRING(@tableName, 3, 100)) + 'Log'') IS NOT NULL BEGIN
    DROP TRIGGER tr_' + LOWER(SUBSTRING(@tableName, 3, 100)) + 'Log
  END'
  EXEC sp_executesql @sql;
  
  SET @sql = '
  CREATE TRIGGER tr_' + LOWER(SUBSTRING(@tableName, 3, 100)) + 'Log ON ' + @tableName + ' 
  FOR INSERT, DELETE, UPDATE AS
  BEGIN
    SET NOCOUNT ON
    DECLARE
      @insTable TABLE (objectId int, intezmenyId int, tanevId int, creator int, modifier int, col nvarchar(30), val varchar(max))
    DECLARE  
      @delTable TABLE (objectId int, intezmenyId int, tanevId int, creator int, modifier int, col nvarchar(30), val varchar(max))
    DECLARE 
      @linkTable TABLE (id int, objectId int, intezmenyId int, tanevId int)
    DECLARE  
      @action char(1),
      @xml xml    
    
    SET @action =
      CASE 
        WHEN NOT EXISTS (SELECT 1 FROM DELETED) THEN ''I''
        WHEN NOT EXISTS (SELECT 1 FROM INSERTED) THEN ''D''
        ELSE ''U''
      END

    INSERT INTO @insTable (objectId, intezmenyId, tanevId, creator, modifier, col, val)
    SELECT ID, C_INTEZMENYID, C_TANEVID, CREATOR, MODIFIER, col, val
      FROM 
       (
       SELECT  ID, ' + @intezmenyCol + ' AS C_INTEZMENYID, ' + @tanevCol + ' AS C_TANEVID, CREATOR, MODIFIER, ' + @sel + '
       FROM INSERTED) p
       UNPIVOT (val FOR col IN (' + @unpvt +')) AS unpvt

    INSERT INTO @delTable (objectId, intezmenyId, tanevId, creator, modifier, col, val)
    SELECT ID, C_INTEZMENYID, C_TANEVID, CREATOR, MODIFIER, col, val
      FROM 
       (
       SELECT  ID, ' + @intezmenyCol + ' AS C_INTEZMENYID, ' + @tanevCol + ' AS C_TANEVID, CREATOR, MODIFIER, ' + @sel + '
       FROM DELETED) p
       UNPIVOT (val FOR col IN (' + @unpvt +')) AS unpvt
    
    IF @action = ''I'' BEGIN
      INSERT INTO T_ENTITYHISTORY (C_ALTERATIONDATE, C_ENTITYID, C_ENTITYNAME, C_REASON, C_FELHASZNALOID, C_INTEZMENYID, C_TANEVID)
      OUTPUT INSERTED.ID, INSERTED.C_ENTITYID, INSERTED.C_INTEZMENYID, INSERTED.C_TANEVID INTO @linkTable
      SELECT DISTINCT GETDATE(), objectId, ''' + @tableName + ''', ''New'', creator, intezmenyId, tanevId FROM @insTable
      
      INSERT INTO T_ENTITYATTRIBUTEHISTORY (C_CURRENTVALUE, C_ORIGINALVALUE, C_PROPERTYNAME, C_ENTITYHISTORYID)
      SELECT val, NULL, col, lk.id 
      FROM @insTable i
      INNER JOIN @linkTable lk ON i.objectId = lk.objectId AND i.intezmenyId = lk.intezmenyId AND i.tanevId = lk.tanevId
      
    END

    IF @action = ''D'' BEGIN
      INSERT INTO T_ENTITYHISTORY (C_ALTERATIONDATE, C_ENTITYID, C_ENTITYNAME, C_REASON, C_FELHASZNALOID, C_INTEZMENYID, C_TANEVID)
      OUTPUT INSERTED.ID, INSERTED.C_ENTITYID, INSERTED.C_INTEZMENYID, INSERTED.C_TANEVID INTO @linkTable
      SELECT DISTINCT GETDATE(), objectId, ''' + @tableName + ''', ''Removed'', modifier, intezmenyId, tanevId FROM @delTable
      
      INSERT INTO T_ENTITYATTRIBUTEHISTORY (C_CURRENTVALUE, C_ORIGINALVALUE, C_PROPERTYNAME, C_ENTITYHISTORYID)
      SELECT val, NULL, col, lk.id 
      FROM @delTable i
      INNER JOIN @linkTable lk ON i.objectId = lk.objectId AND i.intezmenyId = lk.intezmenyId AND i.tanevId = lk.tanevId
      
    END

    IF @action = ''U'' BEGIN
      INSERT INTO T_ENTITYHISTORY (C_ALTERATIONDATE, C_ENTITYID, C_ENTITYNAME, C_REASON, C_FELHASZNALOID, C_INTEZMENYID, C_TANEVID)
      OUTPUT INSERTED.ID, INSERTED.C_ENTITYID, INSERTED.C_INTEZMENYID, INSERTED.C_TANEVID INTO @linkTable
      SELECT DISTINCT GETDATE(), i.objectId, ''' + @tableName + ''', 
        CASE 
          WHEN i.val = ''T'' AND d.val = ''F'' THEN ''Logical Removed''
          WHEN i.val = ''F'' AND d.val = ''T'' THEN ''Restored''
          ELSE ''Modified'' 
        END, i.modifier, i.intezmenyId, i.tanevId 
      FROM @insTable i
      INNER JOIN @delTable d ON i.intezmenyId = d.intezmenyId AND i.objectId = d.objectId AND i.tanevId = d.tanevId AND i.col = d.col
      WHERE i.col = ''TOROLT'' AND d.COL = ''TOROLT''
      
      INSERT INTO T_ENTITYATTRIBUTEHISTORY (C_CURRENTVALUE, C_ORIGINALVALUE, C_PROPERTYNAME, C_ENTITYHISTORYID)
      SELECT i.val, d.val, ISNULL(i.col, d.col), lk.id 
      FROM @delTable d
        FULL JOIN @insTable i ON i.objectId = d.objectId AND i.intezmenyId = d.intezmenyId AND i.tanevId = d.tanevId AND i.col = d.col
        INNER JOIN @linkTable lk ON ISNULL(i.objectId, d.objectId) = lk.objectId AND ISNULL(i.intezmenyId, d.intezmenyId) = lk.intezmenyId AND ISNULL(i.tanevId, d.tanevId) = lk.tanevId
      WHERE (i.val <> d.val OR ((i.val IS NULL OR d.val IS NULL) AND (i.val IS NOT NULL OR d.val IS NOT NULL)))
    END  
  END'

  EXEC sp_executesql @sql;
  
END
GO