CODEWITHSUNDEEP
sqlsql-servertriggersdynamic-sqlraiseerror
Jay Edwards
Jay Edwards

Reputation: 25

Raise error in trigger to return insert row column

I am trying to set up a trigger on a table so that when the RaiseError condition is met it returns the column value of the inserted row in a string.

I am struggling to get the column value into the error message, my thought was to use dynamic SQL however I can't get it run:

Incorrect syntax near the keyword 'Select'

Any thoughts on how to get this to run?

AFTER INSERT, UPDATE
AS
    IF (ROWCOUNT_BIG()  = 0)
        RETURN;
    ELSE IF EXISTS (SELECT * FROM inserted AS a
                    WHERE Label  = '0')
    BEGIN
        DECLARE @Error VARCHAR(100)
        DECLARE @UpdateError VARCHAR(100)
        DECLARE @Lay_Class VARCHAR(50)

        SET @Lay_Class = (SELECT [Lay_Class] FROM inserted);
        SET @UpdateError = 'Set @error = ''Error: ' + @Lay_Class + ' New Lay Class, Please add to Case When on Label''';

        EXEC sp_executesql @UpdateError;

        RAISERROR(@Error, 16, 1);
        ROLLBACK TRANSACTION;

        RETURN;
    END
    ELSE ... etc

Upvotes: 0

Views: 2044

Answers (1)

Dale K
Dale K

Reputation: 27201

  1. You're making the classic trigger 101 error, and treating the inserted table as though it only has a single row. It will have as many rows as have been inserted/updated and you have to handle that accordingly i.e. as a set based operation.

  2. You don't mix SET and SELECT you use the appropriate one.

  3. THROW is now recommended over RAISEERROR

The following might do what you require:

IF (ROWCOUNT_BIG()  = 0)
    RETURN;
ELSE IF EXISTS (
    SELECT * FROM inserted AS a
    WHERE Label  = '0'
)
BEGIN
    DECLARE @Error varchar(100)
    declare @Lay_Class  varchar(50)

    select top 1 @Lay_Class = [Lay_Class] FROM inserted where Label  = '0';
    set @error = 'Error: ' + @Lay_Class + ' New Lay Class, Please add to Case When on Label';

    THROW 51000, @Error, 1;

    ROLLBACK TRANSACTION;
END

Upvotes: 3

Related Questions