Calling a Synchronous Event within a Transaction and Handling Failure

  1. Determine the current site.
  2. Name a configuration, by convention:
    DECLARE @Site SiteType
    SELECT @Site = site FROM parms
    
  3. Determine the current SessionID:
    DECLARE @SessionID RowPointerType
    SET @SessionID = dbo.SessionIdSp()
  4. Add the procedure code:
    BEGIN TRANSACTION 
    UPDATE coitem
    SET due_date = dbo.CalcDueDate(@Parm1, @Parm2)
    WHERE coitem.co_num = @CoNum
    AND coitem.co_line = @CoLine
    AND coitem.co_release = @CoRelease
    
    SET @MyEventParmId = NEWID()
    EXEC InsertEventInputParameterSp @MyEventParmId, 'CoNum', @CoNum
    EXEC InsertEventInputParameterSp @MyEventParmId, 'CoLine', @CoLine
    EXEC InsertEventInputParameterSp @MyEventParmId, 'CoRelease', @CoRelease
    
    DECLARE
    @AnyHandlersFailed [tinyint],
    @Result [nvarchar](4000),
    @Infobar [nvarchar](4000)
    
    EXEC @Severity = FireEventSp
    @EventName = 'SetCoitemDueDate',
    @ConfigName = 'SyteLine',
    @Initiator = 'MyInitiator',
    @SessionID = @SessionID,
    @EventParmId = @MyEventParmID,
    @EventTrxId = NULL,
    @Transactional = 0,
    @GeneratingEventActionStateRowPointer = NULL,
    @AnyHandlersFailed = @Unused OUTPUT,
    @Result = @Result OUTPUT,
    @Infobar = @Infobar OUTPUT
    
    IF @Severity > 0
    BEGIN
    EXEC RaiseError @Infobar, @Severity
    ROLLBACK TRANSACTION END
    ...
    COMMIT TRANSACTION