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