mysql如何在触发器中输出提示信息_如何将MySQL触发器异常/失败信息存储到表或变量中...

I am stuck at somewhere and I need some help from you.

Scenario

I have two databases i.e., test_db1 and test_db2 and have users table on both of them. Both databases initially are empty (0 rows).

Here's users table schema:

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (

`id` int(11) AUTO_INCREMENT,

`name` varchar(30) NOT NULL,

`age` int(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

After that, I have written some triggers for INSERT, UPDATE and DELETE events on test_db1.users table, Below is what each trigger does:

on INSERT in test_db1.users, insert same row in test_db2.users

on UPDATE in test_db1.users, update same row in test_db2.users

on DELETE in test_db1.users, delete same row from test_db2.users

And here's the trigger code snippet.

DELIMITER //

-- TRIGGER FOR INSERT

DROP TRIGGER IF EXISTS `test_db1_users_bi`;

CREATE TRIGGER `test_db1_users_bi` BEFORE INSERT ON `users` FOR EACH ROW

BEGIN

INSERT INTO `test_db2`.`users` (id, name, age) VALUES (NEW.id, NEW.name, NEW.age);

END; //

-- TRIGGER FOR UPDATE

DROP TRIGGER IF EXISTS `test_db1_users_bu`;

CREATE TRIGGER `test_db1_users_bu` BEFORE UPDATE ON `users` FOR EACH ROW

BEGIN

UPDATE `test_db2`.`users`

SET name = NEW.name,

age = NEW.age

WHERE id = NEW.id;

END; //

-- TRIGGER FOR DELETE

DROP TRIGGER IF EXISTS `test_db1_users_bd`;

CREATE TRIGGER `test_db1_users_bd` BEFORE DELETE ON `users` FOR EACH ROW

BEGIN

DELETE FROM `test_db2`.`users`

WHERE id = OLD.id;

END; //

-- DELIMITER;

Now, the Question!

Currently, this doesn't have any errors/exceptions handlers defined in triggers.. So, I want to handle that too but I don't know how to do that. I just don't know how would I get the exception and it's properties like exceptions - error code, error message?

I just want to store the caught exception/error into test_db1.errors table from each of the trigger (if fails):

Here's errors table schema something looks like:

DROP TABLE IF EXISTS `errors`;

CREATE TABLE `errors` (

`id` int(11) AUTO_INCREMENT,

`error_code` varchar(30) DEFAULT NULL,

`error_message` TEXT DEFAULT NULL,

`emailed` TINYINT DEFAULT 0,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

FYI: Below are the possible failures:

If test_db2 is not there or unable to connect?

If INSERT trigger fails i.e., whatever the reason?

If UPDATE trigger fails i.e., whatever the reason?

If DELETE trigger fails i.e., whatever the reason?

If anyone can Just let me know how can I get the exception and it's properties like error code, error message and store it to variables from inside triggers so that then I will perform insert to store them into table?

I am running MySQL version: 5.5+

Thanks!

解决方案

I found a hack via DECLARE CONTINUE HANDLER and GET DIAGNOSTICS CONDITION, so just thought, I must share it here.

Here's my complete final script which keeps backup (sync) for both databases users table what it means is that any update on test_db1 (which we may call it as a productionDB) will occur on test_db2 (which we may call it to as stagingDB):

/*

Create two databases:

1. `test_db1`

2. `test_db2`

and execute below create *Table script* on both databases.

after that execute *Triggers Script* on `test_db1` only..

*/

/*

TABLE STRUCTURE FOR `users`

*/

DROP TABLE IF EXISTS `users`;

CREATE TABLE IF NOT EXISTS `users` (

`id` int(11) AUTO_INCREMENT NOT NULL,

`name` varchar(30) NOT NULL,

`age` int(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

/*

TABLE STRUCTURE FOR `errors`

*/

DROP TABLE IF EXISTS `errors`;

CREATE TABLE IF NOT EXISTS `errors` (

`id` int(11) AUTO_INCREMENT NOT NULL,

`code` varchar(30) NOT NULL,

`message` TEXT NOT NULL,

`query_type` varchar(50) NOT NULL,

`record_id` int(11) NOT NULL,

`on_db` varchar(50) NOT NULL,

`on_table` varchar(50) NOT NULL,

`emailed` TINYINT DEFAULT 0,

`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

/*

TRIGGERS SCRIPTS FOR INSERT, UPDATE AND DELETE OPERATIONS

*/

DELIMITER //

-- TRIGGER FOR INSERT

DROP TRIGGER IF EXISTS `test_db1_users_ai`;

CREATE TRIGGER `test_db1_users_ai` AFTER INSERT ON `users` FOR EACH ROW

BEGIN

-- Declare variables to hold diagnostics area information

DECLARE errorCode CHAR(5) DEFAULT '00000';

DECLARE errorMessage TEXT DEFAULT '';

-- Declare exception handler for failed insert

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

BEGIN

GET DIAGNOSTICS CONDITION 1

errorCode = RETURNED_SQLSTATE, errorMessage = MESSAGE_TEXT;

END;

-- Perform the insert

INSERT INTO `test_db2`.`users` (id, name, age) VALUES (NEW.id, NEW.name, NEW.age);

-- Check whether the insert was successful

IF errorCode != '00000' THEN

INSERT INTO `errors` (code, message, query_type, record_id, on_db, on_table) VALUES (errorCode, errorMessage, 'insert', NEW.id, 'test_db2', 'users');

END IF;

END; //

-- TRIGGER FOR UPDATE

DROP TRIGGER IF EXISTS `test_db1_users_au`;

CREATE TRIGGER `test_db1_users_au` AFTER UPDATE ON `users` FOR EACH ROW

BEGIN

-- Declare variables to hold diagnostics area information

DECLARE errorCode CHAR(5) DEFAULT '00000';

DECLARE errorMessage TEXT DEFAULT '';

-- Declare exception handler for failed insert

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

BEGIN

GET DIAGNOSTICS CONDITION 1

errorCode = RETURNED_SQLSTATE, errorMessage = MESSAGE_TEXT;

END;

-- Perform the update

UPDATE `test_db2`.`users`

SET name = NEW.name,

age = NEW.age

WHERE id = NEW.id;

-- Check whether the update was successful

IF errorCode != '00000' THEN

INSERT INTO `errors` (code, message, query_type, record_id, on_db, on_table) VALUES (errorCode, errorMessage, 'update', NEW.id, 'test_db2', 'users');

END IF;

END; //

-- TRIGGER FOR DELETE

DROP TRIGGER IF EXISTS `test_db1_users_ad`;

CREATE TRIGGER `test_db1_users_ad` AFTER DELETE ON `users` FOR EACH ROW

BEGIN

-- Declare variables to hold diagnostics area information

DECLARE errorCode CHAR(5) DEFAULT '00000';

DECLARE errorMessage TEXT DEFAULT '';

-- Declare exception handler for failed insert

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

BEGIN

GET DIAGNOSTICS CONDITION 1

errorCode = RETURNED_SQLSTATE, errorMessage = MESSAGE_TEXT;

END;

-- Perform the delete

DELETE FROM `test_db2`.`users`

WHERE id = OLD.id;

-- Check whether the insert was successful

IF errorCode != '00000' THEN

INSERT INTO `errors` (code, message, query_type, record_id, on_db, on_table) VALUES (errorCode, errorMessage, 'delete', OLD.id, 'test_db2', 'users');

END IF;

END; //

-- DELIMITER;

Hope this will help others when they come over here.

Cheers,