Trigger xp_LogEvent based on inserted cell value
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;