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;

Ask SQL Trigger

ALTER TRIGGER [dbo].[OM_Ask_SQL_1301]
   ON  [dbo].[OM - Ask SQL]
   AFTER INSERT
AS
BEGIN
  SET NOCOUNT ON

  DECLARE @Question INTEGER;
  DECLARE @Answer NVARCHAR(100);

  SELECT @Question = Question
  FROM inserted;

  SET @Answer = '';

  IF @Question = 1
  BEGIN
    SET @Answer = '1301';
  END;

  IF @Question = 2
  BEGIN

    SET @Answer = '1';

    DELETE FROM [OM - Where Used Status]
    WHERE Question = 2;

    INSERT INTO [OM - Where Used Status] ([Object Type], [Object No_], Question, Answer)
    SELECT ob.[Type], ob.ID, 2, 0
    FROM [Object] ob
      LEFT OUTER JOIN [OM - Known Object Export Error] ee
        ON ob.[Type] = ee.[Object Type]
        AND ob.ID = ee.[Object No_]
      LEFT OUTER JOIN [OM - Where Used Object] wu
        ON ob.[Type] = wu.[Object Type]
        AND ob.ID = wu.[Object No_]
      LEFT OUTER JOIN
      (
        SELECT ho2.[Object Type], ho2.[Object No_], MAX(ho2.[Entry No_]) [Entry No_]
        FROM [OM - C_AL History Object] ho2
        GROUP BY ho2.[Object Type], ho2.[Object No_]
      ) sho
        ON ob.[Type] = sho.[Object Type]
        AND ob.ID = sho.[Object No_]
      LEFT OUTER JOIN [OM - C_AL History Object] ho
        ON sho.[Entry No_] = ho.[Entry No_]
        AND ob.[Type] = ho.[Object Type]
        AND ob.ID = ho.[Object No_]
    WHERE ob.[Type] <> 0
    AND ee.[Object Type] IS NULL
    AND
    (
      wu.[Object Type] IS NULL OR
      ho.[Entry No_] IS NULL OR
      REPLACE(REPLACE(REPLACE(ho.[Version List], ',', ''), '#', ''), 'LOCKED', '') <>
      REPLACE(REPLACE(REPLACE(ob.[Version List], ',', ''), '#', ''), 'LOCKED', '') OR
      ho.Modified <> ob.Modified OR
      ho.[Object Name] <> ob.Name OR
      ho.[Object Date] <> ob.Date OR
      ABS(CAST(ho.[Object Time] AS FLOAT) - CAST(ob.[Time] AS FLOAT)) * 60 * 60 * 24 > 1 OR
      wu.[Object Date] <> ob.Date OR
      wu.[Object Time] <> ob.Time OR
      wu.[Object Modified] <> ob.Modified
    );

    INSERT INTO [OM - Where Used Status] ([Object Type], [Object No_], Question, Answer)
    SELECT wu.[Object Type], wu.[Object No_], 2, 0
    FROM [OM - Where Used Object] wu
      LEFT OUTER JOIN [Object] ob
        ON wu.[Object Type] = ob.[Type]
        AND wu.[Object No_] = ob.ID
      LEFT OUTER JOIN [OM - Extension Object] eo
        ON wu.[Object Type] = eo.[Object Type]
        AND wu.[Object No_] = eo.[Object No_]
    WHERE wu.[Object No_] < 2000000000
    AND ob.[Type] IS NULL
    AND eo.[Object Type] IS NULL;

    INSERT INTO [OM - Where Used Status] ([Object Type], [Object No_], Question, Answer)
    SELECT wu.[Object Type], wu.[Object No_], 2, 0
    FROM [OM - Where Used Object] wu
      LEFT OUTER JOIN [OM - Where Used Status] ws
        ON wu.[Object Type] = ws.[Object Type]
        AND wu.[Object No_] = ws.[Object No_]
        AND ws.Question = 2
    WHERE wu.[Object No_] < 2000000000
    AND
    (
      wu.[C_AL Changed] = 1
      OR wu.[Status] < 2
     OR wu.[Version No_] < 701
   )
    AND ws.[Object Type] IS NULL;

    INSERT INTO [OM - Where Used Status] ([Object Type], [Object No_], Question, Answer)
    SELECT eo.[Object Type], eo.[Object No_], 2, 0
    FROM [OM - Extension Object] eo
      LEFT OUTER JOIN [OM - Known Object Export Error] ee
        ON eo.[Object Type] = ee.[Object Type]
        AND eo.[Object No_] = ee.[Object No_]
      LEFT OUTER JOIN [OM - Where Used Status] ws
        ON eo.[Object Type] = ws.[Object Type]
        AND eo.[Object No_] = ws.[Object No_]
        AND ws.Question = 2
    WHERE ee.[Object Type] IS NULL
    AND eo.[Status] IN (1, 2)
    AND ws.[Object Type] IS NULL;

    SELECT TOP 1 @Answer = '0'
    FROM [OM - Where Used Status]
    WHERE Question = 2;

  END;

  IF @Question = 3
  BEGIN
    DECLARE @String NVARCHAR(250);
    DECLARE @CaseSensitive INTEGER;

    DELETE FROM [OM - Where Used Status]
    WHERE Question = 3;

    SELECT
      @String = [Parameter 1],
      @CaseSensitive = CONVERT(INTEGER, [Parameter 2])
    FROM inserted;

    IF @CaseSensitive = 1
    BEGIN
      INSERT INTO [OM - Where Used Status] ([Object Type], [Object No_], Question, Answer)
      SELECT 0, ho.[Entry No_], 3, CHARINDEX(@String COLLATE SQL_Latin1_General_CP1_CS_AS, CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), ho.[Source Code])))
      FROM [OM - C_AL History Object] ho
        INNER JOIN [OM - Where Used Status] wus
        ON ho.[Entry No_] = wus.[Object No_]
        AND wus.Question = 4;
    END ELSE BEGIN
      INSERT INTO [OM - Where Used Status] ([Object Type], [Object No_], Question, Answer)
      SELECT 0, ho.[Entry No_], 3, CHARINDEX(@String COLLATE SQL_Latin1_General_CP1_CI_AS, CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), ho.[Source Code])))
      FROM [OM - C_AL History Object] ho
        INNER JOIN [OM - Where Used Status] wus
        ON ho.[Entry No_] = wus.[Object No_]
        AND wus.Question = 4;
    END;

    SET @Answer = '1';

  END;

  UPDATE [OM - Ask SQL] SET Answer = @Answer
  FROM inserted i, [OM - Ask SQL] ASql
  WHERE i.Question = ASql.Question;

END

Block Database Conversion Trigger

ALTER TRIGGER [dbo].[OM_Block_DB_Conversion_900]
   ON  [dbo].[$ndo$dbproperty]
   AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @OtherDatabaseVersionNo INTEGER = 0;
  DECLARE @Message NVARCHAR(100);

  SELECT @OtherDatabaseVersionNo = 1
  FROM Inserted i
    INNER JOIN Deleted d
      ON i.programid = d.programid
  WHERE i.databaseversionno <> d.databaseversionno;
  IF @OtherDatabaseVersionNo = 1
  BEGIN
    SET @Message =
      CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
      'OBJECT MANAGER ERROR:' + CHAR(13) + CHAR(10) +
      'Database cannot be converted.' + CHAR(13) + CHAR(10) +
      'Please first remove the Block Database Conversion trigger.' + CHAR(13) + CHAR(10);

    RAISERROR(@Message, 16, 1);
    ROLLBACK TRANSACTION;
  END;

END

Block Database Conversion Trigger

OMA Object Manager Advanced

SQL Triggers

Trace Modification Trigger

Ask SQL Trigger

Block Database Conversion Trigger

Block Database Conversion Trigger