Sql-server – getting the error message from another trigger in sql server

sql servertrigger

i have a table 'Event' and it contains a trigger trEvent which updates table 'Position', and table 'Position' has a trigger trPosition which validates the Position records.
whenever trEvent is fired one record in Position table is updated and the trPosition trigger is fired to check the validation of position, if it is a valid position there is no problem but if the position is invalid how the trEvent should know there is something wrong with the Position which is updated.

in simple words how to access error message from trPosition trigger in trEvent trigger?

thanks in advance!

Best Answer

Based on the problem description, I tried to create a similar scenario including the solution. Hope this will help you.

Here I created tables and triggers.

Trigger trgEvents inserting the EventId into tbl_Position table if the EventId does not exists.

In the trigger trgPosition created on tbl_Position, I'm just validating whether column PositionId in tbl_Position is having a numeric value.

DROP TABLE IF EXISTS tbl_Events
GO
CREATE TABLE tbl_Events
(
EventId int PRIMARY KEY,
EventDescription varchar(512)
);
DROP TABLE IF EXISTS tbl_Position
GO
CREATE TABLE tbl_Position
(
EventId int REFERENCES tbl_Events(EventId),
PositionId varchar(8)
);
GO

DROP TRIGGER IF EXISTS trEvents
GO
CREATE TRIGGER trEvents
ON tbl_Events
FOR INSERT
AS
BEGIN
BEGIN TRY
IF EXISTS (select 'Exists ?' from tbl_Position P join inserted i on P.EventId=i.EventId)
    BEGIN
    UPDATE P SET PositionId=i.EventId+100 from tbl_Position P join inserted i on P.EventId=i.EventId;
    END
ELSE
    BEGIN
    INSERT INTO tbl_Position
    SELECt EventId,'NA' FROM inserted;
    END
END TRY
BEGIN CATCH
ROLLBACK;

DECLARE @Err NVARCHAR(4000) = ERROR_MESSAGE()
        RAISERROR(@Err, 16, 10);
END CATCH
END
GO

DROP TRIGGER IF EXISTS trPosition
GO
CREATE TRIGGER trPosition
ON tbl_Position
FOR UPDATE,INSERT
AS
BEGIN
IF (SELECT isnumeric(PositionId) from inserted)=1
    BEGIN
    Print 'PositionId has Numeric value'
    END
ELSE
    BEGIN
    RAISERROR ('PositionId has Non-Numeric value', 16 ,10)
    ROLLBACK;
    END
END

After I insert bellow execute the INSERT statement below.

INSERT INTO tbl_Events(EventId,EventDescription)
VALUES(1,'Dance compatition in New Delhi')

It fails and complete transaction gets rolled back.

(0 rows affected)
Msg 50000, Level 16, State 10, Procedure trEvents, Line 21 [Batch Start Line 62]
PositionId has Non-Numeric value
Msg 3609, Level 16, State 1, Line 63
The transaction ended in the trigger. The batch has been aborted.

Thanks!

Related Question