Sql-server – Trigger xp_LogEvent based on inserted cell value

sql serversql server 2014trigger

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

Best Answer

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;

Related Solutions

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

What you presented is essentially the correct way for interrupting triggers in MySQL 5.1

I wrote about this before

Dec 23, 2011 : check constraint does not work?
Apr 25, 2011 : Trigger in MySQL to prevent insertion

You cannot do Dynamic SQL in triggers.

You may have to resort to coding the my_signal like this:

CREATE PROCEDURE `my_signal`(error_type INT)
BEGIN
    IF error_type = 1 THEN
        UPDATE `Error: can not insert new OWNER !` set x=1;
    END IF;
    IF error_type = 2 THEN
        UPDATE `Error: Row can not reference itself!` set x=1;
    END IF;
    .
    .
END//

then change the trigger to look like this:

DELIMITER $$
CREATE
  TRIGGER `employee_before_insert` BEFORE INSERT
    ON `Employee`
    FOR EACH ROW BEGIN
      CASE
       WHEN NEW.designation = 'OWNER'  THEN
          CALL my_signal(1);
       WHEN NEW.SSN = NEW.MSSN THEN
          CALL my_signal(2);
      END CASE; 
  END$$   
DELIMITER ;

Sorry for the clumsy programming but Signal Processing is MySQL 5.1 is horrible.

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );

Best Answer

Related Solutions

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

Related Question