0

I need to record insert statements, who did them, and when.
I am adding triggers to tables and putting the freshly inserted data into a sql_log table.
Everything is working well except one thing.
I want dates, times, and timestamps to be surrounded by quotes. Just like they would be in an insert statement. SET `somevariable` = CONCAT('\'' , NEW.date_variable , '\''); I expect this would produce:
'0000-00-00' but it does not. There are no quotes in the output.

When the trigger runs it creates the INSERT statement and writes it to the sql_log table. The ONLY problem is the dates are not quoted.

What is the problem?

To keep all this in context, here is one of the triggers in it's entirety

DROP TRIGGER IF EXISTS trig_jos_weblinks;
DELIMITER $$
CREATE TRIGGER trig_jos_weblinks AFTER INSERT ON jos_weblinks
FOR EACH ROW
BEGIN

-- the following variable will hold the new sql string that generated this insert
DECLARE sqlVariable TEXT(65535);

-- each of the following variables represent a column in the table and will hold the data from that column
DECLARE `id`  int(10);
DECLARE `catid`  int(10);
DECLARE `sid`  int(10);
DECLARE `title`  varchar(250);
DECLARE `alias`  varchar(255);
DECLARE `url`  varchar(250);
DECLARE `description`  text(65535);
DECLARE `date`  datetime;
DECLARE `hits`  int(10);
DECLARE `state`  tinyint(3);
DECLARE `checked_out`  int(10);
DECLARE `checked_out_time`  datetime;
DECLARE `ordering`  int(10);
DECLARE `archived`  tinyint(3);
DECLARE `approved`  tinyint(3);
DECLARE `access`  int(10);
DECLARE `params`  text(65535);
DECLARE `language`  char(7);
DECLARE `created`  datetime;
DECLARE `created_by`  int(10);
DECLARE `created_by_alias`  varchar(255);
DECLARE `modified`  datetime;
DECLARE `modified_by`  int(10);
DECLARE `metakey`  text(65535);
DECLARE `metadesc`  text(65535);
DECLARE `metadata`  text(65535);
DECLARE `featured`  tinyint(3);
DECLARE `xreference`  varchar(50);
DECLARE `publish_up`  datetime;
DECLARE `publish_down`  datetime;

-- here I set the freshly inserted value from the table row into variables
IF NEW.id  IS NOT NULL
THEN SET `id` = NEW.id;
ELSE SET `id` = 'NULL' ;
END IF;

IF NEW.catid  IS NOT NULL
THEN SET `catid` = NEW.catid;
ELSE SET `catid` = 'NULL' ;
END IF;

IF NEW.sid  IS NOT NULL
THEN SET `sid` = NEW.sid;
ELSE SET `sid` = 'NULL' ;
END IF;

IF NEW.title  IS NOT NULL
THEN SET `title` =  quote( NEW.title ) ;
ELSE SET `title` = 'NULL' ;
END IF;

IF NEW.alias  IS NOT NULL
THEN SET `alias` =  quote( NEW.alias ) ;
ELSE SET `alias` = 'NULL' ;
END IF;

IF NEW.url  IS NOT NULL
THEN SET `url` =  quote( NEW.url ) ;
ELSE SET `url` = 'NULL' ;
END IF;

IF NEW.description  IS NOT NULL
THEN SET `description` =  quote( NEW.description ) ;
ELSE SET `description` = 'NULL' ;
END IF;

IF NEW.date  IS NOT NULL
THEN SET `date` = CONCAT('\'' , NEW.date , '\'');
ELSE SET `date` = 'NULL' ;
END IF;

IF NEW.hits  IS NOT NULL
THEN SET `hits` = NEW.hits;
ELSE SET `hits` = 'NULL' ;
END IF;

IF NEW.state  IS NOT NULL
THEN SET `state` = NEW.state;
ELSE SET `state` = 'NULL' ;
END IF;

IF NEW.checked_out  IS NOT NULL
THEN SET `checked_out` = NEW.checked_out;
ELSE SET `checked_out` = 'NULL' ;
END IF;

IF NEW.checked_out_time  IS NOT NULL
THEN SET `checked_out_time` = CONCAT('\'' , NEW.checked_out_time , '\'');
ELSE SET `checked_out_time` = 'NULL' ;
END IF;

IF NEW.ordering  IS NOT NULL
THEN SET `ordering` = NEW.ordering;
ELSE SET `ordering` = 'NULL' ;
END IF;

IF NEW.archived  IS NOT NULL
THEN SET `archived` = NEW.archived;
ELSE SET `archived` = 'NULL' ;
END IF;

IF NEW.approved  IS NOT NULL
THEN SET `approved` = NEW.approved;
ELSE SET `approved` = 'NULL' ;
END IF;

IF NEW.access  IS NOT NULL
THEN SET `access` = NEW.access;
ELSE SET `access` = 'NULL' ;
END IF;

IF NEW.params  IS NOT NULL
THEN SET `params` =  quote( NEW.params ) ;
ELSE SET `params` = 'NULL' ;
END IF;

IF NEW.language  IS NOT NULL
THEN SET `language` =  quote( NEW.language ) ;
ELSE SET `language` = 'NULL' ;
END IF;

IF NEW.created  IS NOT NULL
THEN SET `created` = CONCAT('\'' , NEW.created , '\'');
ELSE SET `created` = 'NULL' ;
END IF;

IF NEW.created_by  IS NOT NULL
THEN SET `created_by` = NEW.created_by;
ELSE SET `created_by` = 'NULL' ;
END IF;

IF NEW.created_by_alias  IS NOT NULL
THEN SET `created_by_alias` =  quote( NEW.created_by_alias ) ;
ELSE SET `created_by_alias` = 'NULL' ;
END IF;

IF NEW.modified  IS NOT NULL
THEN SET `modified` = CONCAT('\'' , NEW.modified , '\'');
ELSE SET `modified` = 'NULL' ;
END IF;

IF NEW.modified_by  IS NOT NULL
THEN SET `modified_by` = NEW.modified_by;
ELSE SET `modified_by` = 'NULL' ;
END IF;

IF NEW.metakey  IS NOT NULL
THEN SET `metakey` =  quote( NEW.metakey ) ;
ELSE SET `metakey` = 'NULL' ;
END IF;

IF NEW.metadesc  IS NOT NULL
THEN SET `metadesc` =  quote( NEW.metadesc ) ;
ELSE SET `metadesc` = 'NULL' ;
END IF;

IF NEW.metadata  IS NOT NULL
THEN SET `metadata` =  quote( NEW.metadata ) ;
ELSE SET `metadata` = 'NULL' ;
END IF;

IF NEW.featured  IS NOT NULL
THEN SET `featured` = NEW.featured;
ELSE SET `featured` = 'NULL' ;
END IF;

IF NEW.xreference  IS NOT NULL
THEN SET `xreference` =  quote( NEW.xreference ) ;
ELSE SET `xreference` = 'NULL' ;
END IF;

IF NEW.publish_up  IS NOT NULL
THEN SET `publish_up` = CONCAT('\'' , NEW.publish_up , '\'');
ELSE SET `publish_up` = 'NULL' ;
END IF;

IF NEW.publish_down  IS NOT NULL
THEN SET `publish_down` = CONCAT('\'' , NEW.publish_down , '\'');
ELSE SET `publish_down` = 'NULL' ;
END IF;

-- put all the variables into a usable insert statement
SET sqlVariable = CONCAT( 'INSERT INTO jos_weblinks VALUES ('
, `id`, ',', `catid`, ',', `sid`, ',', `title`, ',', `alias`, ',', `url`, ',', `description`, ',', `date`, ',', `hits`, ',', `state`, ',', `checked_out`, ',', `checked_out_time`, ',', `ordering`, ',', `archived`, ',', `approved`, ',', `access`, ',', `params`, ',', `language`, ',', `created`, ',', `created_by`, ',', `created_by_alias`, ',', `modified`, ',', `modified_by`, ',', `metakey`, ',', `metadesc`, ',', `metadata`, ',', `featured`, ',', `xreference`, ',', `publish_up`, ',', `publish_down`, ')'
) ;

-- finally insert this stuff into the sql_log table
INSERT INTO sql_log 
SET id = 0, sql_statement =  sqlVariable, 
by_user = USER() ;
END $$
DELIMITER ;
1
Contributor
1
Reply
2
Views
6 Years
Discussion Span
Last Post by dkaptain
0

I hope it's not bad form to answer my own question. I still don't know why setting a variable using CONCAT to add single quotes doesn't work. I did however manage to get the results I wanted. I added the quotes when I create sqlVariable near the end of the script.

I will mark this solved since my script now works.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.