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 ;