-- Test Case 1 (With DROP TEMPORARY TABLE 'SysStructureTemp' (SysOperation_AINS [TRIGGER AFTER INSERT]) instruction) :

mysql> CREATE Schema AdminSchema;
Query OK, 1 row affected (0.00 sec)

mysql> USE AdminSchema;
Database changed
mysql> 
mysql> CREATE TABLE `SysDatabase`(
    -> `Name` CHAR(255) NOT NULL, 
    -> `Type` CHAR(255) NOT NULL, 
    -> `Value` CHAR(255) NOT NULL, 
    -> `Date` DATETIME NOT NULL, 
    -> `Instance` INT NOT NULL, 
    -> `Resource` INT NOT NULL, 
    -> `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
Query OK, 0 rows affected (0.37 sec)

mysql> \d $$
mysql> CREATE TRIGGER `SysDatabase_AINS` 
    ->      AFTER INSERT ON `SysDatabase` FOR EACH ROW
    ->      BEGIN
    ->      DECLARE Name, Type, Value CHAR(255);
    ->      DECLARE Date DATETIME;
    ->      DECLARE Instance, Resource INT;
    ->      
    ->      #Recovery
    ->      IF (SELECT COUNT(SysDatabase.ID) FROM SysDatabase) = 1 THEN 
    ->      SELECT SysDatabase.Name, SysDatabase.Type, SysDatabase.Value, 
    ->      SysDatabase.Date, SysDatabase.Instance, SysDatabase.Resource
    ->      INTO Name, Type, Value, Date, Instance, Resource
    ->      FROM SysDatabase;
    ->      
    ->      ELSE
    ->      
    ->      SELECT 
    ->      SysDatabase.Name, SysDatabase.Type, SysDatabase.Value, 
    ->      SysDatabase.Date, SysDatabase.Instance, SysDatabase.Resource 
    ->      INTO Name, Type, Value, Date, Instance, Resource
    ->      FROM SysDatabase 
    ->      WHERE NOT EXISTS 
    ->      (SELECT SysDatabaseTemp.ID 
    ->       FROM SysDatabaseTemp WHERE SysDatabaseTemp.ID = SysDatabase.ID); 
    ->      
    ->      END IF;
    ->      
    ->      INSERT 
    ->      INTO SysRecovery 
    ->      (TableName, Name, Type, Value, Date, NowDate, Instance, Resource) 
    ->      VALUE
    ->      ('SysDatabase', Name, Type, Value, Date, @NowDate, Instance, Resource); 
    ->      
    ->      #SysStructure
    ->      INSERT
    ->      INTO SysStructure
    ->      (Name, Type, Date, Instance, Resource) 
    ->      VALUES
    ->      (Name, 'Schema', @NowDate, Instance, Resource);
    ->      
    ->      #SysOperation 
    ->      SET @Action = 'Create'; 
    ->      
    ->      INSERT
    ->      INTO SysOperation
    ->      (Name, Type, Value, Date, Instance, Resource) 
    ->      VALUES
    ->      (Name, Type, 'Schema', @NowDate, Instance, Resource);
    ->      
    ->      END$$
Query OK, 0 rows affected (0.12 sec)

mysql> CREATE TRIGGER `SysDatabase_BINS` 
    ->      BEFORE INSERT ON `SysDatabase` FOR EACH ROW
    ->      BEGIN
    ->      #Log
    ->      SET @NowDate = NOW(); 
    ->      
    ->      IF (SELECT COUNT(ID) FROM SysDatabase) > 0 THEN
    ->      
    ->      CREATE TEMPORARY TABLE SysDatabaseTemp ENGINE = MEMORY
    ->      SELECT * FROM SysDatabase;
    ->      SET @SysDatabaseTemp = TRUE;
    ->      
    ->      END IF;
    ->      
    ->      SET @Table = 'Schema';
    ->      
    ->      END$$
Query OK, 0 rows affected (0.09 sec)

mysql> 
mysql> CREATE TRIGGER `SysDatabase_BUPD` 
    ->      BEFORE UPDATE ON `SysDatabase` FOR EACH ROW
    ->      BEGIN
    ->      #Log
    ->      IF @Structure IS FALSE THEN
    ->      SET @NowDate = NOW(); 
    ->      END IF;
    ->      #Recovery
    ->      CREATE TEMPORARY TABLE SysDatabaseTemp ENGINE = MEMORY
    ->      SELECT * FROM SysDatabase;
    ->      
    ->      SET @SysDatabaseTemp = TRUE;
    ->      
    ->      SET @Table = 'Schema';
    ->      
    ->      END$$
Query OK, 0 rows affected (0.11 sec)

mysql> 
mysql> CREATE TRIGGER `SysDatabase_AUPD` 
    ->      AFTER UPDATE ON `SysDatabase` FOR EACH ROW
    ->      BEGIN
    ->      DECLARE Name, Type, Value CHAR(255);
    ->      DECLARE Date DATETIME;
    ->      DECLARE Instance, Resource, ID INT;
    ->      
    ->      #Recovery
    ->      SELECT 
    ->      SysDatabase.Name, SysDatabase.Type, SysDatabase.Value, 
    ->      SysDatabase.Date, SysDatabase.Instance, SysDatabase.Resource 
    ->      INTO Name, Type, Value, Date, Instance, Resource
    ->      FROM SysDatabase 
    ->      WHERE NOT EXISTS 
    ->      (SELECT SysDatabaseTemp.ID
    ->       FROM SysDatabaseTemp WHERE SysDatabaseTemp.Date = SysDatabase.Date); 
    ->      
    ->      INSERT 
    ->      INTO SysRecovery 
    ->      (TableName, Name, Type, Value, Date, NowDate, Instance, Resource) 
    ->      VALUE
    ->      ('SysDatabase', Name, Type, Value, Date, @NowDate, Instance, Resource); 
    ->      #SysOperation
    ->      
    ->      SET @Action = 'Rename';
    ->      
    ->      IF @Structure IS FALSE THEN
    ->      
    ->      UPDATE SysStructure SET Date = Date
    ->      WHERE SysStructure.Date = (SELECT 
    ->      SysDatabaseTemp.Date
    ->      FROM SysDatabaseTemp 
    ->      WHERE NOT EXISTS 
    ->      (SELECT SysDatabase.ID
    ->       FROM SysDatabase WHERE SysDatabase.Date = SysDatabaseTemp.Date));
    ->      
    ->      END IF;
    ->      
    ->      INSERT
    ->      INTO SysOperation
    ->      (Name, Type, Value, Date, Instance, Resource) 
    ->      VALUES
    ->      (Name, Type, 'Schema', @NowDate, Instance, Resource);
    ->      
    ->      END$$
Query OK, 0 rows affected (0.10 sec)

mysql> 
mysql> \d ;
mysql> CREATE TABLE `SysStructure` (
    -> `Name` char(255) NOT NULL,
    -> `Type` char(255) NOT NULL,
    -> `Value` char(255) DEFAULT NULL,
    -> `Date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -> `Instance` int(11) NOT NULL,
    -> `Resource` int(11) NOT NULL,
    -> `ID` int(11) NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (`ID`))ENGINE=InnoDB;
Query OK, 0 rows affected (0.36 sec)

mysql> 
mysql> \d $$
mysql> 
mysql> CREATE TRIGGER `SysStructure_BINS` 
    ->      BEFORE INSERT ON `SysStructure` FOR EACH ROW
    ->      BEGIN
    ->      
    ->      #Recovery
    ->      IF (SELECT COUNT(ID) FROM SysStructure ) > 0 THEN 
    ->      
    ->      CREATE TEMPORARY TABLE SysStructureTemp ENGINE = MEMORY
    ->      SELECT * FROM SysStructure;
    ->      
    ->      SET @SysStructureTemp = TRUE;
    ->      
    ->      END IF;
    ->      
    ->      END$$
Query OK, 0 rows affected (0.11 sec)

mysql> 
mysql> CREATE TRIGGER `SysStructure_AINS` 
    ->      AFTER INSERT ON `SysStructure` FOR EACH ROW
    ->      BEGIN 
    ->      DECLARE Name, Type, Value CHAR(255);
    ->      DECLARE Date DATETIME;
    ->      DECLARE Instance, Resource INT;
    ->      #Log
    ->      
    ->      # Recovery
    ->      IF (SELECT COUNT(SysStructure.ID) FROM SysStructure) = 1 THEN 
    ->      SELECT SysStructure.Name, SysStructure.Type, SysStructure.Value, 
    ->      SysStructure.Date, SysStructure.Instance, SysStructure.Resource
    ->      INTO Name, Type, Value, Date, Instance, Resource
    ->      FROM SysStructure;
    ->      
    ->      ELSE 
    ->      
    ->      SELECT SysStructure.Name, SysStructure.Type, SysStructure.Value, 
    ->      SysStructure.Date, SysStructure.Instance, SysStructure.Resource
    ->      INTO Name, Type, Value, Date, Instance, Resource
    ->      FROM SysStructure 
    ->      WHERE NOT EXISTS 
    ->      (SELECT SysStructureTemp.ID 
    ->       FROM SysStructureTemp WHERE SysStructureTemp.ID = SysStructure.ID); 
    ->      
    ->      END IF;
    ->      
    ->      INSERT 
    ->      INTO SysRecovery 
    ->      (TableName, Name, Type, Value, Date, NowDate, Instance, Resource) 
    ->      VALUES
    ->      ('SysTask', Name, Type, Value, Date, @NowDate, Instance, Resource); 
    ->      
    ->      
    ->      IF @SysStructureTemp = TRUE THEN 
    ->      
    ->      DROP TEMPORARY TABLE SysStructureTemp;
    ->      
    ->      SET @SysStructureTemp = FALSE; 
    ->      
    ->      END IF;
    ->      
    ->      END$$
Query OK, 0 rows affected (0.11 sec)

mysql> 
mysql> CREATE TRIGGER `SysStructure_BUPD` 
    ->      BEFORE UPDATE ON `SysStructure` FOR EACH ROW
    ->      BEGIN
    ->      #Log
    ->      #Recovery
    ->      CREATE TEMPORARY TABLE SysStructureTemp ENGINE = MEMORY
    ->      SELECT * FROM SysStructure;
    ->      
    ->      SET @SysStructureTemp = TRUE;
    ->      
    ->      END$$
Query OK, 0 rows affected (0.12 sec)

mysql> 
mysql> CREATE TRIGGER `SysStructure_AUPD` 
    ->      AFTER UPDATE ON `SysStructure` FOR EACH ROW
    ->      BEGIN
    ->      DECLARE Name, Type, Value CHAR(255);
    ->      DECLARE Date DATETIME;
    ->      DECLARE Instance, Resource, ID INT;
    ->      
    ->      IF @Action IS NULL THEN
    ->      SET @NowDate = NOW(); 
    ->      END IF;
    ->      #Recovery
    ->      SELECT 
    ->      SysStructure.Name, SysStructure.Type, SysStructure.Value, 
    ->      SysStructure.Date, SysStructure.Instance, SysStructure.Resource 
    ->      INTO Name, Type, Value, Date, Instance, Resource
    ->      FROM SysStructure 
    ->      WHERE NOT EXISTS 
    ->      (SELECT SysStructureTemp.ID
    ->       FROM SysStructureTemp WHERE SysStructureTemp.Date = SysStructure.Date); 
    ->      
    ->      INSERT 
    ->      INTO SysRecovery 
    ->      (TableName, Name, Type, Value, Date, NowDate, Instance, Resource) 
    ->      VALUE
    ->      ('SysStructure', Name, Type, Value, Date, @NowDate, Instance, Resource); 
    ->      #SysOperation
    ->      
    ->      CASE 1
    ->      WHEN Type = 'Schema' THEN
    ->      
    ->      CASE 1 
    ->      WHEN @Action IS NULL THEN
    ->      
    ->      SET @Structure = TRUE; 
    ->      
    ->      UPDATE SysDatabase 
    ->      SET Date = Date 
    ->      WHERE SysDatabase.Date = (SELECT 
    ->      SysStructureTemp.Date 
    ->      FROM SysStructureTemp 
    ->      WHERE NOT EXISTS 
    ->      (SELECT SysStructure.ID
    ->       FROM SysStructure WHERE SysStructure.Date = SysStructureTemp.Date)); 
    ->      
    ->      ELSE
    ->      
    ->      BEGIN END;
    ->      
    ->      END CASE;
    ->      
    ->      END CASE; 
    ->      
    ->      IF @SysStructureTemp = TRUE THEN 
    ->      
    ->      DROP TEMPORARY TABLE SysStructureTemp;
    ->      
    ->      SET @SysStructureTemp = FALSE; 
    ->      
    ->      END IF;
    ->      
    ->      END$$
Query OK, 0 rows affected (0.18 sec)

mysql> 
mysql> \d ;
mysql> CREATE TABLE SysRecovery(
    -> `TableName` CHAR(255), 
    -> `Name` CHAR(255) NOT NULL, 
    -> `Type` CHAR(255) NOT NULL, 
    -> `Value` CHAR(255) DEFAULT NULL, 
    -> `Task` CHAR(255) DEFAULT NULL, 
    -> `Date` DATETIME NOT NULL, 
    -> `NowDate` DATETIME NOT NULL,
    -> `Instance` INT NOT NULL, 
    -> `Resource` INT NOT NULL, 
    -> `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
Query OK, 0 rows affected (0.35 sec)

mysql> 
mysql> CREATE TABLE SysOperation(
    -> `Name` CHAR(255) NOT NULL, 
    -> `Type` CHAR(255) NOT NULL, 
    -> `Value` CHAR(255) NOT NULL, 
    -> `Date` DATETIME NOT NULL, 
    -> `Instance` INT NOT NULL, 
    -> `Resource` INT NOT NULL, 
    -> `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
Query OK, 0 rows affected (0.37 sec)

mysql> 
mysql> \d $$
mysql> CREATE TRIGGER `SysOperation_BINS` 
    ->      BEFORE INSERT ON `SysOperation` FOR EACH ROW
    ->      BEGIN
    ->      #Log
    -> 
    ->      #Recovery
    ->      IF (SELECT COUNT(ID) FROM SysOperation) > 0 THEN 
    ->      CREATE TEMPORARY TABLE SysOperationTemp ENGINE = MEMORY
    ->      SELECT * FROM SysOperation;
    ->      SET @SysOperationTemp = TRUE;
    ->  
    ->      END IF;
    ->  
    ->      END$$
Query OK, 0 rows affected (0.11 sec)

mysql> 
mysql> CREATE TRIGGER `SysOperation_AINS` 
    ->      AFTER INSERT ON `SysOperation` FOR EACH ROW
    -> 
    -> BEGIN
    ->      DECLARE Name, Type, Value, Task CHAR(255);
    ->      DECLARE Date DATETIME;
    ->      DECLARE Instance, Resource INT;  
    ->      
    ->      IF (SELECT COUNT(SysOperation.ID) FROM SysOperation) = 1 THEN 
    ->      SELECT SysOperation.Name, SysOperation.Type, SysOperation.Value, 
    ->      SysOperation.Date, SysOperation.Instance, SysOperation.Resource
    ->      INTO Name, Type, Value, Date, Instance, Resource
    ->      FROM SysOperation;
    ->      
    ->      ELSE 
    ->      
    ->      IF (SELECT COUNT(SysOperationTemp.ID) FROM SysOperationTemp) > 0 THEN 
    ->      SELECT SysOperation.Name, SysOperation.Type, SysOperation.Value, 
    ->      SysOperation.Date, SysOperation.Instance, SysOperation.Resource
    ->      INTO Name, Type, Value, Date, Instance, Resource
    ->      FROM SysOperation 
    ->      WHERE NOT EXISTS 
    ->      (SELECT SysOperationTemp.ID 
    ->       FROM SysOperationTemp WHERE SysOperationTemp.ID = SysOperation.ID); 
    ->      
    ->      END IF;
    ->      END IF;
    ->      
    ->      INSERT 
    ->      INTO SysRecovery 
    ->      (TableName, Name, Type, Value, Date, NowDate, Instance, Resource) 
    ->      VALUES
    ->      ('SysOperation', Name, Type, Value, Date, @NowDate, Instance, Resource); 
    ->      
    ->       
    ->      IF @SysDatabaseTemp IS TRUE THEN 
    ->      DROP TEMPORARY TABLE SysDatabaseTemp; 
    ->      SET @SysDatabaseTemp = FALSE;
    ->      END IF; 
    ->      
    ->      IF @SysOperationTemp IS TRUE THEN 
    ->      DROP TEMPORARY TABLE SysOperationTemp;
    ->      SET @SysOperationTemp = FALSE;
    ->      END IF;
    ->      
    ->      IF @SysStructureTemp IS TRUE THEN 
    ->      DROP TEMPORARY TABLE SysStructureTemp; 
    ->      SET @SysStructureTemp = FALSE;
    ->      END IF; 
    -> 
    ->      SET @Action = null, @Table = null;
    ->      
    ->      END$$
Query OK, 0 rows affected (0.18 sec)

mysql> 
mysql> \d;
mysql> INSERT INTO SysDatabase(Name,Type,Value,Date,Instance,Resource) VALUE ('Schema Name','Schema Type','Schema Value',NOW(),1,1);
Query OK, 1 row affected (0.12 sec)

mysql> DO SLEEP(1);
Query OK, 0 rows affected (1.00 sec)

mysql> UPDATE SysStructure SET Value='DEFAULT CHARSET = UCS2', Date=NOW() WHERE Date=@NowDate;
ERROR 1137 (HY000): Can't reopen table: 'SysStructureTemp'
mysql> 


-- How to fix : 

-- SETUP this Environment;

mysql> SET @SysStructureTemp = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS SysStructureTemp;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP Schema AdminSchema;
Query OK, 4 rows affected (1.05 sec)

mysql> SET @Action = null, @Table = null;
Query OK, 0 rows affected (0.00 sec)


-- THEN Perform Test Case 2 :

-- Test Case 2 (Without DROP TEMPORARY TABLE 'SysStructureTemp' (SysOperation_AINS [TRIGGER AFTER INSERT]) instruction) :

mysql> CREATE Schema AdminSchema;
Query OK, 1 row affected (0.01 sec)

mysql> USE AdminSchema;
Database changed
mysql> 
mysql> CREATE TABLE `SysDatabase`(
    -> `Name` CHAR(255) NOT NULL, 
    -> `Type` CHAR(255) NOT NULL, 
    -> `Value` CHAR(255) NOT NULL, 
    -> `Date` DATETIME NOT NULL, 
    -> `Instance` INT NOT NULL, 
    -> `Resource` INT NOT NULL, 
    -> `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
Query OK, 0 rows affected (0.36 sec)

mysql> 
mysql> \d $$
mysql> CREATE TRIGGER `SysDatabase_AINS` 
    ->      AFTER INSERT ON `SysDatabase` FOR EACH ROW
    ->      BEGIN
    ->      DECLARE Name, Type, Value CHAR(255);
    ->      DECLARE Date DATETIME;
    ->      DECLARE Instance, Resource INT;
    ->      
    ->      #Recovery
    ->      IF (SELECT COUNT(SysDatabase.ID) FROM SysDatabase) = 1 THEN 
    ->      SELECT SysDatabase.Name, SysDatabase.Type, SysDatabase.Value, 
    ->      SysDatabase.Date, SysDatabase.Instance, SysDatabase.Resource
    ->      INTO Name, Type, Value, Date, Instance, Resource
    ->      FROM SysDatabase;
    ->      
    ->      ELSE
    ->      
    ->      SELECT 
    ->      SysDatabase.Name, SysDatabase.Type, SysDatabase.Value, 
    ->      SysDatabase.Date, SysDatabase.Instance, SysDatabase.Resource 
    ->      INTO Name, Type, Value, Date, Instance, Resource
    ->      FROM SysDatabase 
    ->      WHERE NOT EXISTS 
    ->      (SELECT SysDatabaseTemp.ID 
    ->       FROM SysDatabaseTemp WHERE SysDatabaseTemp.ID = SysDatabase.ID); 
    ->      
    ->      END IF;
    ->      
    ->      INSERT 
    ->      INTO SysRecovery 
    ->      (TableName, Name, Type, Value, Date, NowDate, Instance, Resource) 
    ->      VALUE
    ->      ('SysDatabase', Name, Type, Value, Date, @NowDate, Instance, Resource); 
    ->      
    ->      #SysStructure
    ->      INSERT
    ->      INTO SysStructure
    ->      (Name, Type, Date, Instance, Resource) 
    ->      VALUES
    ->      (Name, 'Schema', @NowDate, Instance, Resource);
    ->      
    ->      #SysOperation 
    ->      SET @Action = 'Create'; 
    ->      
    ->      INSERT
    ->      INTO SysOperation
    ->      (Name, Type, Value, Date, Instance, Resource) 
    ->      VALUES
    ->      (Name, Type, 'Schema', @NowDate, Instance, Resource);
    ->      
    ->      END$$
Query OK, 0 rows affected (0.10 sec)

mysql> 
mysql> 
mysql> CREATE TRIGGER `SysDatabase_BINS` 
    ->      BEFORE INSERT ON `SysDatabase` FOR EACH ROW
    ->      BEGIN
    ->      #Log
    ->      SET @NowDate = NOW(); 
    ->      
    ->      IF (SELECT COUNT(ID) FROM SysDatabase) > 0 THEN
    ->      
    ->      CREATE TEMPORARY TABLE SysDatabaseTemp ENGINE = MEMORY
    ->      SELECT * FROM SysDatabase;
    ->      SET @SysDatabaseTemp = TRUE;
    ->      
    ->      END IF;
    ->      
    ->      SET @Table = 'Schema';
    ->      
    ->      END$$
Query OK, 0 rows affected (0.12 sec)

mysql> 
mysql> CREATE TRIGGER `SysDatabase_BUPD` 
    ->      BEFORE UPDATE ON `SysDatabase` FOR EACH ROW
    ->      BEGIN
    ->      #Log
    ->      IF @Structure IS FALSE THEN
    ->      SET @NowDate = NOW(); 
    ->      END IF;
    ->      #Recovery
    ->      CREATE TEMPORARY TABLE SysDatabaseTemp ENGINE = MEMORY
    ->      SELECT * FROM SysDatabase;
    ->      
    ->      SET @SysDatabaseTemp = TRUE;
    ->      
    ->      SET @Table = 'Schema';
    ->      
    ->      END$$
Query OK, 0 rows affected (0.10 sec)

mysql> 
mysql> CREATE TRIGGER `SysDatabase_AUPD` 
    ->      AFTER UPDATE ON `SysDatabase` FOR EACH ROW
    ->      BEGIN
    ->      DECLARE Name, Type, Value CHAR(255);
    ->      DECLARE Date DATETIME;
    ->      DECLARE Instance, Resource, ID INT;
    ->      
    ->      #Recovery
    ->      SELECT 
    ->      SysDatabase.Name, SysDatabase.Type, SysDatabase.Value, 
    ->      SysDatabase.Date, SysDatabase.Instance, SysDatabase.Resource 
    ->      INTO Name, Type, Value, Date, Instance, Resource
    ->      FROM SysDatabase 
    ->      WHERE NOT EXISTS 
    ->      (SELECT SysDatabaseTemp.ID
    ->       FROM SysDatabaseTemp WHERE SysDatabaseTemp.Date = SysDatabase.Date); 
    ->      
    ->      INSERT 
    ->      INTO SysRecovery 
    ->      (TableName, Name, Type, Value, Date, NowDate, Instance, Resource) 
    ->      VALUE
    ->      ('SysDatabase', Name, Type, Value, Date, @NowDate, Instance, Resource); 
    ->      #SysOperation
    ->      
    ->      SET @Action = 'Rename';
    ->      
    ->      IF @Structure IS FALSE THEN
    ->      
    ->      UPDATE SysStructure SET Date = Date
    ->      WHERE SysStructure.Date = (SELECT 
    ->      SysDatabaseTemp.Date
    ->      FROM SysDatabaseTemp 
    ->      WHERE NOT EXISTS 
    ->      (SELECT SysDatabase.ID
    ->       FROM SysDatabase WHERE SysDatabase.Date = SysDatabaseTemp.Date));
    ->      
    ->      END IF;
    ->      
    ->      INSERT
    ->      INTO SysOperation
    ->      (Name, Type, Value, Date, Instance, Resource) 
    ->      VALUES
    ->      (Name, Type, 'Schema', @NowDate, Instance, Resource);
    ->      
    ->      END$$
Query OK, 0 rows affected (0.09 sec)

mysql> 
mysql> \d ;
mysql> CREATE TABLE `SysStructure` (
    -> `Name` char(255) NOT NULL,
    -> `Type` char(255) NOT NULL,
    -> `Value` char(255) DEFAULT NULL,
    -> `Date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -> `Instance` int(11) NOT NULL,
    -> `Resource` int(11) NOT NULL,
    -> `ID` int(11) NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (`ID`))ENGINE=InnoDB;
Query OK, 0 rows affected (0.58 sec)

mysql> 
mysql> \d $$
mysql> 
mysql> CREATE TRIGGER `SysStructure_BINS` 
    ->      BEFORE INSERT ON `SysStructure` FOR EACH ROW
    ->      BEGIN
    ->      
    ->      #Recovery
    ->      IF (SELECT COUNT(ID) FROM SysStructure ) > 0 THEN 
    ->      
    ->      CREATE TEMPORARY TABLE SysStructureTemp ENGINE = MEMORY
    ->      SELECT * FROM SysStructure;
    ->      
    ->      SET @SysStructureTemp = TRUE;
    ->      
    ->      END IF;
    ->      
    ->      END$$
Query OK, 0 rows affected (0.10 sec)

mysql> 
mysql> CREATE TRIGGER `SysStructure_AINS` 
    ->      AFTER INSERT ON `SysStructure` FOR EACH ROW
    ->      BEGIN 
    ->      DECLARE Name, Type, Value CHAR(255);
    ->      DECLARE Date DATETIME;
    ->      DECLARE Instance, Resource INT;
    ->      #Log
    ->      
    ->      # Recovery
    ->      IF (SELECT COUNT(SysStructure.ID) FROM SysStructure) = 1 THEN 
    ->      SELECT SysStructure.Name, SysStructure.Type, SysStructure.Value, 
    ->      SysStructure.Date, SysStructure.Instance, SysStructure.Resource
    ->      INTO Name, Type, Value, Date, Instance, Resource
    ->      FROM SysStructure;
    ->      
    ->      ELSE 
    ->      
    ->      SELECT SysStructure.Name, SysStructure.Type, SysStructure.Value, 
    ->      SysStructure.Date, SysStructure.Instance, SysStructure.Resource
    ->      INTO Name, Type, Value, Date, Instance, Resource
    ->      FROM SysStructure 
    ->      WHERE NOT EXISTS 
    ->      (SELECT SysStructureTemp.ID 
    ->       FROM SysStructureTemp WHERE SysStructureTemp.ID = SysStructure.ID); 
    ->      
    ->      END IF;
    ->      
    ->      INSERT 
    ->      INTO SysRecovery 
    ->      (TableName, Name, Type, Value, Date, NowDate, Instance, Resource) 
    ->      VALUES
    ->      ('SysTask', Name, Type, Value, Date, @NowDate, Instance, Resource); 
    ->      
    ->      
    ->      IF @SysStructureTemp = TRUE THEN 
    ->      
    ->      DROP TEMPORARY TABLE SysStructureTemp;
    ->      
    ->      SET @SysStructureTemp = FALSE; 
    ->      
    ->      END IF;
    ->      
    ->      END$$
Query OK, 0 rows affected (0.10 sec)

mysql> 
mysql> CREATE TRIGGER `SysStructure_BUPD` 
    ->      BEFORE UPDATE ON `SysStructure` FOR EACH ROW
    ->      BEGIN
    ->      #Log
    ->      #Recovery
    ->      CREATE TEMPORARY TABLE SysStructureTemp ENGINE = MEMORY
    ->      SELECT * FROM SysStructure;
    ->      
    ->      SET @SysStructureTemp = TRUE;
    ->      
    ->      END$$
Query OK, 0 rows affected (0.10 sec)

mysql> 
mysql> CREATE TRIGGER `SysStructure_AUPD` 
    ->      AFTER UPDATE ON `SysStructure` FOR EACH ROW
    ->      BEGIN
    ->      DECLARE Name, Type, Value CHAR(255);
    ->      DECLARE Date DATETIME;
    ->      DECLARE Instance, Resource, ID INT;
    ->      
    ->      IF @Action IS NULL THEN
    ->      SET @NowDate = NOW(); 
    ->      END IF;
    ->      #Recovery
    ->      SELECT 
    ->      SysStructure.Name, SysStructure.Type, SysStructure.Value, 
    ->      SysStructure.Date, SysStructure.Instance, SysStructure.Resource 
    ->      INTO Name, Type, Value, Date, Instance, Resource
    ->      FROM SysStructure 
    ->      WHERE NOT EXISTS 
    ->      (SELECT SysStructureTemp.ID
    ->       FROM SysStructureTemp WHERE SysStructureTemp.Date = SysStructure.Date); 
    ->      
    ->      INSERT 
    ->      INTO SysRecovery 
    ->      (TableName, Name, Type, Value, Date, NowDate, Instance, Resource) 
    ->      VALUE
    ->      ('SysStructure', Name, Type, Value, Date, @NowDate, Instance, Resource); 
    ->      #SysOperation
    ->      
    ->      CASE 1
    ->      WHEN Type = 'Schema' THEN
    ->      
    ->      CASE 1 
    ->      WHEN @Action IS NULL THEN
    ->      
    ->      SET @Structure = TRUE; 
    ->      
    ->      UPDATE SysDatabase 
    ->      SET Date = Date 
    ->      WHERE SysDatabase.Date = (SELECT 
    ->      SysStructureTemp.Date 
    ->      FROM SysStructureTemp 
    ->      WHERE NOT EXISTS 
    ->      (SELECT SysStructure.ID
    ->       FROM SysStructure WHERE SysStructure.Date = SysStructureTemp.Date)); 
    ->      
    ->      ELSE
    ->      
    ->      BEGIN END;
    ->      
    ->      END CASE;
    ->      
    ->      END CASE; 
    ->      
    ->      IF @SysStructureTemp = TRUE THEN 
    ->      
    ->      DROP TEMPORARY TABLE SysStructureTemp;
    ->      
    ->      SET @SysStructureTemp = FALSE; 
    ->      
    ->      END IF;
    ->      
    ->      END$$
Query OK, 0 rows affected (0.10 sec)

mysql> 
mysql> \d ;
mysql> CREATE TABLE SysRecovery(
    -> `TableName` CHAR(255), 
    -> `Name` CHAR(255) NOT NULL, 
    -> `Type` CHAR(255) NOT NULL, 
    -> `Value` CHAR(255) DEFAULT NULL, 
    -> `Task` CHAR(255) DEFAULT NULL, 
    -> `Date` DATETIME NOT NULL, 
    -> `NowDate` DATETIME NOT NULL,
    -> `Instance` INT NOT NULL, 
    -> `Resource` INT NOT NULL, 
    -> `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
Query OK, 0 rows affected (0.36 sec)

mysql> 
mysql> CREATE TABLE SysOperation(
    -> `Name` CHAR(255) NOT NULL, 
    -> `Type` CHAR(255) NOT NULL, 
    -> `Value` CHAR(255) NOT NULL, 
    -> `Date` DATETIME NOT NULL, 
    -> `Instance` INT NOT NULL, 
    -> `Resource` INT NOT NULL, 
    -> `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
Query OK, 0 rows affected (0.32 sec)

mysql> 
mysql> \d $$
mysql> 
mysql> CREATE TRIGGER `SysOperation_BINS` 
    ->      BEFORE INSERT ON `SysOperation` FOR EACH ROW
    ->      BEGIN
    ->      #Log
    -> 
    ->      #Recovery
    ->      IF (SELECT COUNT(ID) FROM SysOperation) > 0 THEN 
    ->      CREATE TEMPORARY TABLE SysOperationTemp ENGINE = MEMORY
    ->      SELECT * FROM SysOperation;
    ->      SET @SysOperationTemp = TRUE;
    ->  
    ->      END IF;
    ->  
    ->      END$$
Query OK, 0 rows affected (0.09 sec)

mysql> 
mysql> CREATE TRIGGER `SysOperation_AINS` 
    ->      AFTER INSERT ON `SysOperation` FOR EACH ROW
    -> 
    -> BEGIN
    ->      DECLARE Name, Type, Value, Task CHAR(255);
    ->      DECLARE Date DATETIME;
    ->      DECLARE Instance, Resource INT;  
    ->      
    ->      IF (SELECT COUNT(SysOperation.ID) FROM SysOperation) = 1 THEN 
    ->      SELECT SysOperation.Name, SysOperation.Type, SysOperation.Value, 
    ->      SysOperation.Date, SysOperation.Instance, SysOperation.Resource
    ->      INTO Name, Type, Value, Date, Instance, Resource
    ->      FROM SysOperation;
    ->      
    ->      ELSE 
    ->      
    ->      IF (SELECT COUNT(SysOperationTemp.ID) FROM SysOperationTemp) > 0 THEN 
    ->      SELECT SysOperation.Name, SysOperation.Type, SysOperation.Value, 
    ->      SysOperation.Date, SysOperation.Instance, SysOperation.Resource
    ->      INTO Name, Type, Value, Date, Instance, Resource
    ->      FROM SysOperation 
    ->      WHERE NOT EXISTS 
    ->      (SELECT SysOperationTemp.ID 
    ->       FROM SysOperationTemp WHERE SysOperationTemp.ID = SysOperation.ID); 
    ->      
    ->      END IF;
    ->      END IF;
    ->      
    ->      INSERT 
    ->      INTO SysRecovery 
    ->      (TableName, Name, Type, Value, Date, NowDate, Instance, Resource) 
    ->      VALUES
    ->      ('SysOperation', Name, Type, Value, Date, @NowDate, Instance, Resource); 
    ->      
    ->       
    ->      IF @SysDatabaseTemp IS TRUE THEN 
    ->      DROP TEMPORARY TABLE SysDatabaseTemp; 
    ->      SET @SysDatabaseTemp = FALSE;
    ->      END IF; 
    ->      
    ->      IF @SysOperationTemp IS TRUE THEN 
    ->      DROP TEMPORARY TABLE SysOperationTemp;
    ->      SET @SysOperationTemp = FALSE;
    ->      END IF;
    -> 
    ->      SET @Action = null, @Table = null;
    ->      
    ->      END$$
Query OK, 0 rows affected (0.10 sec)

mysql> 
mysql> \d;
mysql> INSERT INTO SysDatabase(Name,Type,Value,Date,Instance,Resource) VALUE ('Schema Name','Schema Type','Schema Value',NOW(),1,1);
Query OK, 1 row affected (0.12 sec)

mysql> DO SLEEP(1);
Query OK, 0 rows affected (1.00 sec)

mysql> UPDATE SysStructure SET Value='DEFAULT CHARSET = UCS2', Date=NOW() WHERE Date=@NowDate;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>