MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS testing;
 and insert/update logging triggers
CREATE OR REPLACE TAQuery OK, 1 row affected (0.001 sec)

BLE testing.duplicatekey (
MariaDB [(none)]>
MariaDB [(none)]> --
MariaDB [(none)]>
nt(11) NOT NULLMariaDB [(none)]> -- Create Tables and insert/update logging triggers
MariaDB [(none)]> CREATE OR REPLACE TABLE testing.duplicatekey (
    ->     id int(11) NOT NULL AUTO_INCREMENT,
    ->     Name varchar(30) DEFAULT NULL,
    ->     Created timestamp NOT NULL DEFAULT current_timestamp(),
    ->     Modified timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    ->     PRIMARY KEY (id),
    ->     UNIQUE KEY `UK_Name` (`Name`)
    -> ) ENGINE=InnoDB;
FAULT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

DELQuery OK, 0 rows affected (0.007 sec)

MariaDB [(none)]>
MariaDB [(none)]> CREATE OR REPLACE TABLE testing.duplicatekey_log (
    ->     id int(11) NOT NULL AUTO_INCREMENT,
    ->     description varchar(500) DEFAULT NULL,
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]>
MariaDB [(none)]> DELIMITER $$
MariaDB [(none)]> CREATE OR REPLACE TRIGGER testing.duplicatekey_before_insert
    -> BEFORE INSERT ON testing.duplicatekey FOR EACH ROW
W.Name: ', NEW.Na    -> BEGIN
    ->     insert into testing.duplicatekey_log (description) values(concat('Before Insert into duplicatekey : id: ', NEW.id, ', NEW.Name: ', NEW.Name, ', NEW.Modified: ', NEW.Modified));
    -> END
    -> $$
E TRIGGER testing.duplicatekey_before_update
BEFORE UPDATE ON testing.duplicatekey FOR EACH ROW
BEGIN
    insert into teQuery OK, 0 rows affected (0.005 sec)

sting.duplicatekMariaDB [(none)]> DELIMITER ;
MariaDB [(none)]>
MariaDB [(none)]> DELIMITER $$
MariaDB [(none)]> CREATE OR REPLACE TRIGGER testing.duplicatekey_after_insert
    -> AFTER INSERT ON testing.duplicatekey FOR EACH ROW
    -> BEGIN
    ->     insert into testing.duplicatekey_log (description) values(concat('After Insert into duplicatekey : id: ', NEW.id, ', NEW.Name: ', NEW.Name, ', NEW.Modified: ', NEW.Modified));
    -> END
    -> $$
    ', NEW.Modified: ', NEW.Modified, ', OLD.Modified : ', OLD.Modified, ', duplicatekey.Modified : ', (select Modified from testing.duplicatekey where id = NEW.id)));
END$$
DELIMITER ;

DEQuery OK, 0 rows affected (0.004 sec)

LIMIMariaDB [(none)]> DELIMITER ;
MariaDB [(none)]>
MariaDB [(none)]> DELIMITER $$
MariaDB [(none)]> CREATE OR REPLACE TRIGGER testing.duplicatekey_before_update
    -> BEFORE UPDATE ON testing.duplicatekey FOR EACH ROW
    -> BEGIN
    ->     insert into testing.duplicatekey_log (description) values(concat('Before Update on duplicatekey : id: ', NEW.id,
    ->     ', OLD.Name: ', OLD.Name, ', NEW.Name: ', NEW.Name, ', duplicatekey.Name : ', (select Name from testing.duplicatekey where id = NEW.id),
    ->     ', NEW.Modified: ', NEW.Modified, ', OLD.Modified : ', OLD.Modified, ', duplicatekey.Modified : ', (select Modified from testing.duplicatekey where id = NEW.id)));
    -> END$$
', duplicatekey.Name : ', (select Name from testing.duplicatekey where id = NEW.id),
    ', NEW.Modified: ', NEW.ModifQuery OK, 0 rows affected (0.004 sec)

MariaDB [(none)]> DELIMITER ;
MariaDB [(none)]>
MariaDB [(none)]> DELIMITER $$
MariaDB [(none)]> CREATE OR REPLACE TRIGGER testing.duplicatekey_after_update
    -> AFTER UPDATE ON testing.duplicatekey FOR EACH ROW
    -> BEGIN
    ->     insert into testing.duplicatekey_log (description) values(concat('After Update on duplicatekey : id: ', NEW.id,
    ->     ', OLD.Name: ', OLD.Name, ', NEW.Name: ', NEW.Name, ', duplicatekey.Name : ', (select Name from testing.duplicatekey where id = NEW.id),
    ->     ', NEW.Modified: ', NEW.Modified, ', OLD.Modified : ', OLD.Modified, ', duplicatekey.Modified : ', (select Modified from testing.duplicatekey where id = NEW.id)));
    -> END$$
Query OK, 0 rows affected (0.004 sec)

MariaDB [(none)]> DELIMITER ;
MariaDB [(none)]> -- Test duplicate insert with Unique Key
MariaDB [(none)]> INSERT INTO testing.duplicatekey (Name) VALUES ('Test Unique Key')
    -> ON DUPLICATE KEY UPDATE Name = 'Test Unique Key'; -- Generates row with id:1. Before & After Insert triggers complete.
Query OK, 1 row affected (0.006 sec)

MariaDB [(none)]> INSERT INTO testing.duplicatekey (Name) VALUES ('Test Unique Key')
    -> ON DUPLICATE KEY UPDATE Name = 'Test Unique Key'; -- Updates row with id:1. Before Insert & Before Update triggers complete, missing After Update? ***
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SELECT * FROM testing.duplicatekey;
T * FROM testing.duplicatekey_log;
+----+-----------------+---------------------+---------------------+
| id | Name            | Created             | Modified            |
+----+-----------------+---------------------+---------------------+
|  1 | Test Unique Key | 2023-12-20 08:59:51 | 2023-12-20 08:59:51 |
+----+-----------------+---------------------+---------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> SELECT * FROM testing.duplicatekey_log;
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | description
                                                                                         |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | Before Insert into duplicatekey : id: 0, NEW.Name: Test Unique Key, NEW.Modified: 2023-12-20 08:59:51
                                                                                         |
|  2 | After Insert into duplicatekey : id: 1, NEW.Name: Test Unique Key, NEW.Modified: 2023-12-20 08:59:51
                                                                                         |
|  3 | Before Insert into duplicatekey : id: 0, NEW.Name: Test Unique Key, NEW.Modified: 2023-12-20 08:59:51
                                                                                         |
|  4 | Before Update on duplicatekey : id: 1, OLD.Name: Test Unique Key, NEW.Name: Test Unique Key, duplicatekey.Name : Test Unique Key, NEW.Modified: 2023-12-20 08:59:51, OLD.Modified : 2023-12-20 08:59:51, duplicatekey.Modified : 2023-12-20 08:59:51 |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.000 sec)

MariaDB [(none)]> UPDATE testing.duplicatekey
    -> SET Name = 'Test Unique Key'
    -> WHERE Name = 'Test Unique Key';
ROM testing.duplicatekey_log;
Query OK, 0 rows affected (0.002 sec)
Rows matched: 1  Changed: 0  Warnings: 0

MariaDB [(none)]>
MariaDB [(none)]> SELECT * FROM testing.duplicatekey;
+----+-----------------+---------------------+---------------------+
| id | Name            | Created             | Modified            |
+----+-----------------+---------------------+---------------------+
|  1 | Test Unique Key | 2023-12-20 08:59:51 | 2023-12-20 08:59:51 |
+----+-----------------+---------------------+---------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT * FROM testing.duplicatekey_log;
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | description
                                                                                         |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | Before Insert into duplicatekey : id: 0, NEW.Name: Test Unique Key, NEW.Modified: 2023-12-20 08:59:51
                                                                                         |
|  2 | After Insert into duplicatekey : id: 1, NEW.Name: Test Unique Key, NEW.Modified: 2023-12-20 08:59:51
                                                                                         |
|  3 | Before Insert into duplicatekey : id: 0, NEW.Name: Test Unique Key, NEW.Modified: 2023-12-20 08:59:51
                                                                                         |
|  4 | Before Update on duplicatekey : id: 1, OLD.Name: Test Unique Key, NEW.Name: Test Unique Key, duplicatekey.Name : Test Unique Key, NEW.Modified: 2023-12-20 08:59:51, OLD.Modified : 2023-12-20 08:59:51, duplicatekey.Modified : 2023-12-20 08:59:51 |
|  5 | Before Update on duplicatekey : id: 1, OLD.Name: Test Unique Key, NEW.Name: Test Unique Key, duplicatekey.Name : Test Unique Key, NEW.Modified: 2023-12-20 09:01:02, OLD.Modified : 2023-12-20 08:59:51, duplicatekey.Modified : 2023-12-20 08:59:51 |
|  6 | After Update on duplicatekey : id: 1, OLD.Name: Test Unique Key, NEW.Name: Test Unique Key, duplicatekey.Name : Test Unique Key, NEW.Modified: 2023-12-20 09:01:02, OLD.Modified : 2023-12-20 08:59:51, duplicatekey.Modified : 2023-12-20 08:59:51  |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.000 sec)