How to do an insert in an update trigger?

Question

I have an UPDATE trigger that occurs when a record is inserted into a table but would like to create an INSERT at the same time the trigger is fired.

The original INSERT puts a record into the BatchQueue table and when this occurs the trigger is fired to update a second table [Call].

I want to be able to add a record in the BatchQueue table that is basically similar to the initial record being inserted but differs on the time columns and one other column. I could technically do it on the original insert but would need to do a WHEN or IF, for only when SFlags = 11

The original INSERT statement:

BEGIN
    INSERT INTO BatchQueue (TimeToExecute, Operation, 
    Parameter,RuleID, FilterID, SFlags)
        SELECT @TimeToExecute, @Operation, @Parameter, @RuleID, @FilterID, 
        @SFlags FROM Call WHERE [ID]=@Parameter AND Active=0
               AND (OnlineCount>0 OR OnlineScreenCount>0 
       OR (Flags&POWER(2,26)=POWER(2,26)))  
 END

This is the insert I would like to do:

BEGIN
   INSERT INTO [dbo].[BatchQueue]
   VALUES(DateAdd(mi,2,getutcdate()),1,Call.ID,2,null,9,0,0,0)
   WHEN Call.SFlags = 11
END

The trigger statement:

BEGIN
IF @@ROWCOUNT > 0
BEGIN
   SET NOCOUNT ON
   UPDATE Call SET StoreReqCount = StoreReqCount + CallCount.actionCount
   FROM Call, (SELECT Parameter, [actionCount] = COUNT(*) FROM inserted 
   GROUP BY Parameter ) AS CallCount
   WHERE Call.ID = CallCount.Parameter
END

Can someone advise how best to do this? I hope I have clarified. To reiterate, either add the second INSERT to the first with the condition of SFlags=11 or do the insert with the trigger. Which is better?

EDIT:

Tried the following:

   SELECT @StoreActionID = [ID] FROM BatchQueue 
   WHERE Parameter=@Parameter AND RuleID=@RuleID AND 
    SFlags=@SFlags
   IF @StoreActionID IS NOT NULL
   BEGIN
     UPDATE BatchQueue SET FilterID = @FilterID WHERE [ID] = 
     @StoreActionID
   END
ELSE
BEGIN
   INSERT INTO BatchQueue (TimeToExecute, Operation, Parameter, 
      RuleID, FilterID, SFlags)
        SELECT @TimeToExecute, @Operation, @Parameter, @RuleID, 
     @FilterID, @SFlags FROM Call WHERE [ID]=@Parameter AND Active=0
               AND (OnlineCount>0 OR OnlineScreenCount>0 OR 
     (Flags&POWER(2,26)=POWER(2,26))) --bit26 indicates META-DATA-ONLY
END
-- ADDED ADDITIONAL INSERT TO INSERT ACTION BASED ON STOREFLAGS 
BEGIN
    IF @SFlags = 11
    BEGIN 
        INSERT INTO BatchQueue (TimeToExecute, Operation, Parameter, 
        RuleID, FilterID, SFlags)
        SELECT @TimeToExecute, @Operation, @Parameter, @RuleID, 
        @FilterID, 9 FROM Call WHERE [ID]=@Parameter AND Active=0
               AND (OnlineCount>0 OR OnlineScreenCount>0 OR 
        (Flags&POWER(2,26)=POWER(2,26))) --bit26 indicates META-DATA-ONLY
    END        
END

Answer 1

One of the good options you can take is write a stored procedure, and within the stored procedure include the original INSERT statement and then the special INSERT into same table as first INSERT. But, only execute the special INSERT if the first INSERT was successful.

Let the UPDATE happen through your existing trigger. You don't want to disturb the trigger if its being used in production successfully.

Also,it would be good if you can wrap these two INSERTstatements within a transaction.

This will satisfy your requirement.

Put following T-SQL code into your stored precedure

SET NOCOUNT ON;
BEGIN TRY

 Declare @insertedID int;

  --original INSERT
  INSERT INTO BatchQueue (TimeToExecute, Operation,
  Parameter, RuleID, FilterID, SFlags)
    SELECT
      @TimeToExecute,
      @Operation,
      @Parameter,
      @RuleID,
      @FilterID,
      @SFlags
    FROM Call
    WHERE [ID] = @Parameter
    AND Active = 0
    AND (OnlineCount > 0
    OR OnlineScreenCount > 0
    OR (Flags & POWER(2, 26) = POWER(2, 26));

  Set @insertedID = scope_identity() ;

  ---- INSERT if a row is successfully inserted
  IF @@ROWCOUNT > 0
  BEGIN

    INSERT INTO [dbo].[BatchQueue]
      SELECT DATEADD(mi, 2, GETUTCDATE()), 1, @parameter, 2, NULL, 9, 0, 0, 0)  
      WHERE @SFlags = 11;

  END

  --commit tran
  IF @@trancount > 0
  BEGIN
    COMMIT TRAN
  END

END TRY

BEGIN CATCH
  --get error details, which you can log to some table
  DECLARE @ErrorMessage nvarchar(4000);
  DECLARE @ErrorSeverity int;
  DECLARE @ErrorState int;

  SELECT
    @ErrorMessage = ERROR_MESSAGE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE();

  --roll back tran
  IF @@trancount > 0
  BEGIN
    ROLLBACK TRAN
  END

END CATCH