Trigger xp_LogEvent based on inserted cell value

Question

I need to create a trigger on a table (after insert) that simply checks for a string in a cell. If the string exists, I need to log an event in the windows event logger. I am not really sure if I am able to use the inserted tables this way, but I thought I would try. This is what I have so far:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER bcpCatchTrigger
   ON  dbo.TABLE_NAME
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    DECLARE @@Message varchar(255), @@go int;
    SET @@go = 0

    CASE
        WHEN inserted.STAT_MESSAGE LIKE '%bcp.exe%' THEN @@go = 1
    END

    IF @@go == 1 {
        @@Message = inserted.STAT_MESSAGE
        EXEC xp_logevent 60000, @@Message, ERROR;
    }

END
GO

I get incorrect syntax at the CASE statement but I don't quite see why. Perhaps the issue is actually how I'm accessing the inserted table. Where am I going wrong here?

Thank you.

EDITED CODE:

SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER bcpCatchTrigger
   ON  dbo.TABLE_NAME
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    DECLARE @@Message varchar(255), @@go int;
    SET @@go = CASE
        WHEN (SELECT STAT_MESSAGE FROM INSERTED) LIKE '%bcp.exe%' THEN 1
        ELSE 0
    END

    IF (@@go = 1) BEGIN
        SET @@Message = (SELECT STAT_MESSAGE FROM INSERTED)
        EXEC xp_logevent 60000, @@Message, ERROR;
    END

END
GO

Answer 1

Let's start with the main issue of this trigger:

SET @@Message = (SELECT STAT_MESSAGE FROM dbo.TABLE_NAME)

This will produce the following error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

as soon as your INSERT statement will have more than one row.

Others are more suggestions than corrections.

• It's a good practice to check if the insert did insert any row and if not just exit the trigger (if @@rowcount = 0 return;)
• You also should not execute the code that reads from inserted twice.
• Finally, I changed it to read only the first occasion of STAT_MESSAGE LIKE '%bcp.exe%' (select top 1) but you should think about the case when multiple rows LIKE '%bcp.exe%' are inserted: if you want to log all of them you should use a loop.

Here is your trigger modified:

alter TRIGGER bcpCatchTrigger
   ON  dbo.TABLE_NAME
   AFTER INSERT
AS 
BEGIN
if @@rowcount = 0 return; 

SET NOCOUNT ON;

DECLARE @@Message varchar(255);

SELECT @@Message = (select top 1 STAT_MESSAGE
                    FROM INSERTED
                    where STAT_MESSAGE LIKE '%bcp.exe%')  
if @@Message is NULL return;

EXEC xp_logevent 60000, @@Message, ERROR;
END;

Answer 2

SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER bcpCatchTrigger
   ON  dbo.TABLE_NAME
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    DECLARE @@Message varchar(255), @@go int;
    SET @@go = CASE
        WHEN (SELECT STAT_MESSAGE FROM INSERTED) LIKE '%bcp.exe%' THEN 1
        ELSE 0
    END

    IF (@@go = 1) BEGIN
        SET @@Message = (SELECT STAT_MESSAGE FROM INSERTED)
        EXEC xp_logevent 60000, @@Message, ERROR;
    END

END
GO

The above worked perfectly. Now the error puts an entry in to my event log and I can trigger scheduled task events from it.