sql

trigger.sql

-- http://stackoverflow.com/questions/10628640/get-full-mysql-query-string-on-insert-or-update
-- SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id=CONNECTION_ID();

USE test;

CREATE TABLE test_entry (id INTEGER(10) NOT NULL DEFAULT 0);
CREATE TABLE `log_queries` (
  `query_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Query ID',
  `query_time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Query ran on time',
  `query_table` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Query made on table',
  `query_action` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Query action',
  `query_string` TEXT NOT NULL COMMENT 'Full SQL Query string',
  PRIMARY KEY (`query_id`)
);

DROP TRIGGER IF EXISTS ti_test_entry;

DELIMITER $$
CREATE TRIGGER ti_test_entry BEFORE INSERT ON `test_entry`
FOR EACH ROW
BEGIN
	DECLARE original_full_query TEXT;
	SET original_full_query = (SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id=CONNECTION_ID());
	-- INSERT INTO test_entry_debug(query_time, query_string) VALUES (now(), original_full_query);
	EXECUTE SELECT f_log('test_entry', 'INSERT');
END;
$$

DROP FUNCTION IF EXISTS f_log;

DELIMITER $$
CREATE FUNCTION f_log(_table_name VARCHAR(255), _action_name VARCHAR(255))
RETURNS BOOLEAN
BEGIN
	DECLARE original_full_query TEXT;
	SET original_full_query = (SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id=CONNECTION_ID());
	INSERT INTO log_queries(
		query_time, query_table, query_action, query_string
	) VALUES (
		NOW(), _table_name, _action_name, original_full_query
	);
	RETURN TRUE;
END $$

INSERT INTO `test_entry`(`id`) VALUES ( '43');
SELECT * FROM log_queries;