Trigger xp_LogEvent based on inserted cell value 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
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 =, !=, &lt;, &lt;= , >, >= 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;