MySQL – Using IF Statement in Triggers

MySQLtrigger

I am writing a trigger to audit the MySQL database. IF statement doesn't work. Where am i doing wrong?

Also, can you guide me on the best trigger for mysql audit.

DELIMITER $$

CREATE 
    DEFINER = 'sqlsa'@'%'
TRIGGER AuthenticationLog_U
    AFTER UPDATE
    ON AuthenticationLog
    FOR EACH ROW
BEGIN
    DECLARE Type varchar(1);
    DECLARE TableId tinyint;
    DECLARE IsActive tinyint;
    SET @Type='U';
    SET @TableId = 2;
    SET @IsActive = (SELECT IsActive FROM AuditDB.ExportLogTableDefinition WHERE TableId=@TableId);
    IF (@IsActive=1) THEN
        INSERT INTO AuditDB.ExportLog (DBName,TableId,OperationType,HostName,UserName,PrimaryKey,OldData,NewData)
        VALUES (DATABASE(),@TableId,@Type,@@hostname,CURRENT_USER(),OLD.PKAuthenticationLogID,
        CONCAT_WS('',
          '[Key]=',IFNULL(OLD.Key,'NULL')
          ,'[Secret]=',IFNULL(OLD.Secret,'NULL')
          ,'[Token]=',IFNULL(OLD.Token,'NULL')
          ,'[ApplicationId]=',IFNULL(OLD.ApplicationId,'NULL')
          ,'[IpAddress]=',IFNULL(OLD.IpAddress,'NULL')
          ,'[Message]=',IFNULL(OLD.Message,'NULL')
          ,'[CreatedOn]=',IFNULL(OLD.CreatedOn,'NULL')
          ,'[IsSuccess]=',IFNULL(EXPORT_SET(OLD.IsSuccess,'1','0','',1),'NULL')
          ,'[LogType]=',IFNULL(OLD.LogType,'NULL')
          ),
            CONCAT_WS('',
          '[Key]=',IFNULL(NEW.Key,'NULL')
          ,'[Secret]=',IFNULL(NEW.Secret,'NULL')
          ,'[Token]=',IFNULL(NEW.Token,'NULL')
          ,'[ApplicationId]=',IFNULL(NEW.ApplicationId,'NULL')
          ,'[IpAddress]=',IFNULL(NEW.IpAddress,'NULL')
          ,'[Message]=',IFNULL(NEW.Message,'NULL')
          ,'[CreatedOn]=',IFNULL(NEW.CreatedOn,'NULL')
          ,'[IsSuccess]=',IFNULL(EXPORT_SET(NEW.IsSuccess,'1','0','',1),'NULL')
          ,'[LogType]=',IFNULL(NEW.LogType,'NULL'))
        );
      END IF;
END
$$

DELIMITER ;

There is no error message. But I try query, nice work.

SET @IsActive = 1;
    IF (@IsActive=1) THEN

Best Answer

After some experimentation, I figured out that the code works if I don't prefix my variable names with a '@':

DECLARE D_Type varchar(1);
DECLARE D_TableId tinyint;
DECLARE D_IsActive bigint;
SET D_Type='U';
SET D_TableId = 2;
SET D_IsActive = (SELECT IsActive FROM AuditDB.ExportLogTableDefinition WHERE TableId=D_TableId);
IF D_IsActive=1 THEN