How to Store MySQL Trigger exception / failure info into Table or in Variables
<p>I am stuck at somewhere and I need some help from you.</p>

<h1>Scenario</h1>

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

<p>Here's <code>users</code> table schema:</p>

<pre><code>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;
</code></pre>

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

<ul>
<li>on <code>INSERT</code> in <code>test_db1.users</code>, insert same row in <code>test_db2.users</code></li>
<li>on <code>UPDATE</code> in <code>test_db1.users</code>, update same row in <code>test_db2.users</code></li>
<li>on <code>DELETE</code> in <code>test_db1.users</code>, delete same row from <code>test_db2.users</code></li>
</ul>

<p>And here's the trigger code snippet.</p>

<pre><code>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;
</code></pre>

<p>Now, the Question!</p>

<p>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?</p>

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

<p>Here's <code>errors</code> table schema something looks like:</p>

<pre><code>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;
</code></pre>

<p>FYI: Below are the possible failures:</p>

<ul>
<li>If <code>test_db2</code> is not there or unable to connect?</li>
<li>If <code>INSERT</code> trigger fails i.e., whatever the reason?</li>
<li>If <code>UPDATE</code> trigger fails i.e., whatever the reason?</li>
<li>If <code>DELETE</code> trigger fails i.e., whatever the reason?</li>
</ul>

<p>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?</p>

<p>I am running MySQL version: 5.5+</p>

<p>Thanks!</p>