Trace Modification Trigger


ALTER TRIGGER [dbo].[Object_TraceModifications_1200]
   ON [dbo].[Object]
   AFTER INSERT,DELETE,UPDATE
AS
BEGIN
  SET NOCOUNT ON

  DECLARE @SetupSQLCheckObjectLockType INTEGER = 0;
  DECLARE @SkipSQLTrigger INTEGER = 0;
  DECLARE @CheckObjectValid INTEGER = 0;
  DECLARE @DatabaseLocked INTEGER = 0;
  DECLARE @TraceModifications INTEGER = 0;
  DECLARE @LockedBy NVARCHAR(100);
  DECLARE @IsLocked INTEGER;
  DECLARE @IsModification INTEGER;
  DECLARE @CheckIsLocked INTEGER;
  DECLARE @CalledFromRepository INTEGER;
  DECLARE @ObjectType INTEGER = 0;
  DECLARE @ObjectId INTEGER = 0;
  DECLARE @ObjectName NVARCHAR(100);
  DECLARE @ObjectDate DATETIME
  DECLARE @ObjectTime DATETIME
  DECLARE @ObjectTypeText NVARCHAR(100);
  DECLARE @ObjectIdText NVARCHAR(100);
  DECLARE @ObjectVersionList NVARCHAR(100);
  DECLARE @Message NVARCHAR(100);
  DECLARE @NoOfInserts INTEGER;
  DECLARE @NoOfDeletes INTEGER;
  DECLARE @NoOfModifies INTEGER;
  DECLARE @Action INTEGER;
  DECLARE @TokenNo INTEGER;
  DECLARE @LockObjectAtSaving INTEGER;
  DECLARE @Username NVARCHAR(100);
  DECLARE @ShortUsername NVARCHAR(100);
  DECLARE @LockedAdded INTEGER = 0;
  DECLARE @ComputerName NVARCHAR(100);
  DECLARE @IgnoreDatabaseLocked INTEGER = 0;

  SET @Username = SYSTEM_USER;
  SET @ShortUsername = UPPER(SYSTEM_USER);
  SET @ComputerName = HOST_NAME();

  IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'OM - Expected Modification')
    RETURN;

  SELECT @Username = em.[User Id], @ShortUsername = UPPER(em.[User Id]), @ComputerName = em.[Computer Name]
  FROM
    [OM - Expected Modification] em,
    (
      SELECT [Type], [ID]
      FROM inserted
      UNION
      SELECT [Type], [ID]
      FROM deleted
    ) s
  WHERE (em.[Object Type] = s.[Type] OR em.[Object Type] = 0)
  AND (em.[Object No_] = s.[ID] OR em.[Object No_] = 0)
  AND em.[Date Added] = CAST(CONVERT(VARCHAR(20), GETDATE(), 112) + ' 00:00:00' AS DATETIME)
  AND em.[Time Added] > CAST('17540101 ' + CONVERT(VARCHAR(20), DATEADD(SECOND, -5, GETDATE()), 108) AS DATETIME);

  IF (CHARINDEX('\', @ShortUsername) > 0)
    SET @ShortUsername = SUBSTRING(@ShortUsername, CHARINDEX('\', @ShortUsername) + 1, 100);

  SELECT TOP 1 @TraceModifications = [Boolean Value]
  FROM [OM - Setup Value]
  WHERE [User Id] IN ('', @ShortUsername)
  AND [Computer Name] IN ('', @ComputerName)
  AND [Department] = 3
  AND [Setting] = 1
  ORDER BY [Priority];

  SELECT TOP 1 @SetupSQLCheckObjectLockType = [Integer Value]
  FROM [OM - Setup Value]
  WHERE [User Id] IN ('', @ShortUsername)
  AND [Computer Name] IN ('', @ComputerName)
  AND [Department] = 5
  AND [Setting] = 3
  ORDER BY [Priority];

  SELECT TOP 1 @SkipSQLTrigger = [Boolean Value]
  FROM [OM - Setup Value]
  WHERE [User Id] IN ('', @ShortUsername)
  AND [Computer Name] IN ('', @ComputerName)
  AND [Department] = 9
  AND [Setting] = 3
  ORDER BY [Priority];

  SELECT TOP 1 @IgnoreDatabaseLocked = [Boolean Value]
  FROM [OM - Setup Value]
  WHERE [User Id] IN ('', @ShortUsername)
  AND [Computer Name] IN ('', @ComputerName)
  AND [Department] = 9
  AND [Setting] = 5
  ORDER BY [Priority];

  SELECT TOP 1 @LockObjectAtSaving = [Boolean Value]
  FROM [OM - Setup Value]
  WHERE [User Id] IN ('', @ShortUsername)
  AND [Computer Name] IN ('', @ComputerName)
  AND [Department] = 5
  AND [Setting] = 4
  ORDER BY [Priority];

  SELECT TOP 1 @DatabaseLocked = [Integer Value]
  FROM [OM - Setup Value]
  WHERE [User Id] IN ('', @ShortUsername)
  AND [Computer Name] IN ('', @ComputerName)
  AND [Department] = 5
  AND [Setting] = 6
  ORDER BY [Priority];

  IF @SkipSQLTrigger = 0
  BEGIN

    IF @DatabaseLocked = 1 OR (@DatabaseLocked = 2 AND @IgnoreDatabaseLocked = 0)
    BEGIN
      RAISERROR('Database is locked.', 16, 1);
      ROLLBACK TRANSACTION;
    END;

    SELECT
      @ObjectType = d.[Type],
      @ObjectId = d.[ID],
      @ObjectName = d.[Name],
      @ObjectDate = d.[Date],
      @ObjectTime = d.[Time],
      @ObjectVersionList = d.[Version List]
    FROM Deleted d
    WHERE d.[Type] > 0;

    IF @ObjectVersionList = '! CHECK OBJECT VALID !'
      SET @CheckObjectValid = 1;

    SELECT
      @ObjectType = i.[Type],
      @ObjectId = i.[ID],
      @ObjectName = i.[Name],
      @ObjectDate = i.[Date],
      @ObjectTime = i.[Time],
      @ObjectVersionList = i.[Version List]
    FROM Inserted i
    WHERE i.[Type] > 0;

    IF @ObjectVersionList = '! CHECK OBJECT VALID !'
      SET @CheckObjectValid = 1;

    SET @CalledFromRepository = 0;
    SELECT @CalledFromRepository = 1
    FROM [OM - Repository Log] rl
    WHERE rl.[Object Type] = @ObjectType
    AND rl.[Object No_] = @ObjectId
    AND rl.Status = 1;

    IF @ObjectType > 0 AND (@ObjectId < 1000000000 OR @ObjectId >= 2000000000) AND @CalledFromRepository = 0 AND @CheckObjectValid = 0
    BEGIN
      SET @LockedBy = '';
      SET @TokenNo = 0;
      SET @IsLocked = 0;

      SELECT
        @LockedBy = ol.[Locked By],
        @TokenNo = ol.[Token No_],
        @IsLocked = 1
      FROM [OM - Object Lock] ol
      WHERE ol.[Object Type] = @ObjectType
      AND ol.[Object No_] = @ObjectId;

      SELECT @NoOfInserts = COUNT([Type]) FROM Inserted;
      SELECT @NoOfDeletes = COUNT([Type]) FROM Deleted;
      SELECT @NoOfModifies = COUNT(i.[Type])
      FROM Inserted i
        INNER JOIN Deleted d
          ON i.[Type] = d.[Type] AND i.[ID] = d.[ID];

      SET @IsModification = 0;
      SET @CheckIsLocked = 0;

      -- INSERT
      IF @NoOfModifies = 0 AND @NoOfInserts > 0
      BEGIN
        SET @Action = 1;
        SET @IsModification = 1;
      END;

      -- MODIFY
      IF @NoOfModifies > 0
        SET @Action = 2;

      -- DELETE
      IF @NoOfDeletes > 0 AND @NoOfInserts = 0
      BEGIN
        SET @Action = 3;
        SET @IsModification = 1;
        SET @CheckIsLocked = 1;
      END;

      -- RENAME
      IF @NoOfModifies = 0 AND @NoOfDeletes > 0 AND @NoOfInserts > 0
      BEGIN
        SET @Action = 4
        SET @IsModification = 1;
      END;

      -- MODIFY
      IF @Action = 2
        SELECT
          @IsModification = 1,
          @CheckIsLocked = 1
        FROM Inserted i
          INNER JOIN Deleted d
            ON i.[Type] = d.[Type]
            AND i.[ID] = d.[ID]
        WHERE
        (
          i.[Date] <> d.[Date]
          OR CONVERT(VARCHAR(20), i.[Time], 108) <> CONVERT(VARCHAR(20), d.[Time], 108)
          OR i.[Name] <> d.[Name]
          OR REPLACE(REPLACE(i.[Version List], ',', ''), '#', '') <>
             REPLACE(REPLACE(d.[Version List], ',', ''), '#', '')
        );

      IF @SetupSQLCheckObjectLockType <> 0 AND @CheckIsLocked = 1 AND
         UPPER(@LockedBy) <> UPPER(@Username) AND @LockedBy <> ''
      BEGIN
        SELECT
          @ObjectTypeText =
            CASE @ObjectType
              WHEN 1 THEN 'Table'
              WHEN 2 THEN 'Form'
              WHEN 3 THEN 'Report'
              WHEN 4 THEN 'Dataport'
              WHEN 5 THEN 'Codeunit'
              WHEN 6 THEN 'XMLport'
              WHEN 7 THEN 'MenuSuite'
              WHEN 8 THEN 'Page'
              WHEN 9 THEN 'Query'
              ELSE ''
            END,
          @ObjectIdText = @ObjectId;

        SET @Message =
          CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
          'OBJECT MANAGER ERROR:' + CHAR(13) + CHAR(10) +
          'Object %s %s - %s is locked by %s' + CHAR(13) + CHAR(10);
        RAISERROR(@Message, 16, 1, @ObjectTypeText, @ObjectIdText, @ObjectName, @LockedBy);
        ROLLBACK TRANSACTION;
      END;

      IF @LockObjectAtSaving = 1 AND @IsModification = 1 AND @LockedBy = '' AND @Action <> 4
      BEGIN

        IF EXISTS(
          SELECT 1
          FROM [OM - Repository Setup]
          WHERE [Use Repository] = 1)
        BEGIN
          SET @Message =
            CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
            'OBJECT MANAGER ERROR:' + CHAR(13) + CHAR(10) +
            'The option ''Lock Object at Saving'' cannot be ' +
            'used in combination with repository' + CHAR(13) + CHAR(10);
          RAISERROR(@Message, 16, 1);
          ROLLBACK TRANSACTION;
        END;

        DELETE FROM [OM - Object Lock]
        WHERE [Object Type] = @ObjectType
        AND [Object No_] = @ObjectId;

        INSERT INTO [OM - Object Lock]
          (
            [Object Type], [Object No_], [Locked By],
            [Lock Date],
            [Lock Time],
            [Deleted], [Token No_], [Branch No_]
          )
        SELECT
          @ObjectType, @ObjectId, @Username,
          CAST(CONVERT(VARCHAR(20), GETDATE(), 112) + ' 00:00:00' AS DATETIME),
          CAST('17540101 ' + CONVERT(VARCHAR(20), GETDATE(), 108) AS DATETIME),
          0, 0, '';

        UPDATE [Object]
        SET [Locked] = 1,
            [Locked By] = @Username
        WHERE [Type] = @ObjectType
        AND [ID] = @ObjectId
        AND [Locked] = 0;

        SET @IsLocked = 1;
        SET @LockedAdded = 1;
        SET @LockedBy = @Username;

      END;

      IF @SetupSQLCheckObjectLockType = 2 AND @CheckIsLocked = 1 AND @IsLocked = 0
      BEGIN
        SELECT
          @ObjectTypeText =
            CASE @ObjectType
              WHEN 1 THEN 'Table'
              WHEN 2 THEN 'Form'
              WHEN 3 THEN 'Report'
              WHEN 4 THEN 'Dataport'
              WHEN 5 THEN 'Codeunit'
              WHEN 6 THEN 'XMLport'
              WHEN 7 THEN 'MenuSuite'
              WHEN 8 THEN 'Page'
              WHEN 9 THEN 'Query'
              ELSE ''
            END,
          @ObjectIdText = @ObjectId;

        SET @Message =
          CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
          'OBJECT MANAGER ERROR:' + CHAR(13) + CHAR(10) +
          'Object %s %s - %s is not locked' + CHAR(13) + CHAR(10);
        RAISERROR(@Message, 16, 1, @ObjectTypeText, @ObjectIdText, @ObjectName);
        ROLLBACK TRANSACTION;
      END;

      IF @IsModification = 1 AND @TraceModifications = 1
        INSERT INTO [OM - Modification]
          (
            [Object Type], [Object No_], [Object Name],
            [Object Date], [Object Time],
            [Status], [Inserted By],
            [Insert Date],
            [Insert Time],
            [Assigned to Project No_], [Assigned By],
            [Assign Date], [Assign Time], [Auto Assigned],
            [Transport No_], [Object Date Time],
            [Locked By], [Token No_], [Traced By SQL], [SQL Trigger], [SQL Status],
            [System User], [Host Name]
          )
        SELECT
          @ObjectType, @ObjectId, @ObjectName,
          @ObjectDate, CAST('17540101 ' + CONVERT(VARCHAR(20), @ObjectTime, 108) AS DATETIME),
          0, @ShortUsername,
          CAST(CONVERT(VARCHAR(20), GETDATE(), 112) + ' 00:00:00' AS DATETIME),
          CAST('17540101 ' + CONVERT(VARCHAR(20), GETDATE(), 108) AS DATETIME),
          '', '',
          CAST('17530101 00:00:00' AS DATETIME), CAST('17530101 00:00:00' AS DATETIME), 0,
          '', CAST(CONVERT(VARCHAR(20), @ObjectDate, 112) + ' ' +
          CONVERT(VARCHAR(20), @ObjectTime, 108) AS DATETIME),
          @LockedBy, @TokenNo, 1, @Action, 1,
          SUBSTRING(@ShortUsername, 1, 50), SUBSTRING(HOST_NAME(), 1, 50);

      IF @Action = 1 OR @Action = 2
      BEGIN
        INSERT INTO [OM - Object Lock]
          (
            [Object Type], [Object No_], [Locked By],
            [Lock Date],
            [Lock Time],
            [Deleted], [Token No_], [Branch No_]
          )
        SELECT
          ob.[Type], ob.ID, ob.[Locked By],
          CAST(CONVERT(VARCHAR(20), GETDATE(), 112) + ' 00:00:00' AS DATETIME),
          CAST('17540101 ' + CONVERT(VARCHAR(20), GETDATE(), 108) AS DATETIME),
          0, 0, ''
        FROM Inserted ob
          LEFT OUTER JOIN [OM - Object Lock] ol
            ON ob.[Type] = ol.[Object Type]
            AND ob.ID = ol.[Object No_]
        WHERE (ob.Locked = 1 OR @LockedAdded = 1)
        AND ol.[Object Type] IS NULL
        AND ob.[Locked By] <> 'CheckSettings'
        AND ob.[Type] > 0
        GROUP BY ob.[Type], ob.ID, ob.[Locked By];

        DELETE ol
        FROM [OM - Object Lock] ol
          INNER JOIN Inserted ob
            ON ol.[Object Type] = ob.[Type]
            AND ol.[Object No_] = ob.ID
        WHERE (ob.Locked = 0 AND NOT @LockedAdded = 1)
        AND ol.Deleted = 0;
      END;

      IF @Action = 3
        DELETE ol
        FROM [OM - Object Lock] ol
          INNER JOIN Deleted ob
            ON ol.[Object Type] = ob.[Type]
            AND ol.[Object No_] = ob.ID
            AND ol.Deleted = 0;

    END;

  END;

END;