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