-- test using a trigger with insert into .. on duplicate key syntax

-- see *** for queries

CREATE DATABASE IF NOT EXISTS testing;

-- 

-- Create Tables and insert/update logging triggers
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;

CREATE OR REPLACE TABLE testing.duplicatekey_log (
    id int(11) NOT NULL AUTO_INCREMENT,
    description varchar(500) DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

DELIMITER $$
CREATE OR REPLACE TRIGGER testing.duplicatekey_before_insert
BEFORE INSERT ON testing.duplicatekey FOR EACH ROW
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
$$
DELIMITER ;

DELIMITER $$
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
$$
DELIMITER ;

DELIMITER $$
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$$
DELIMITER ;

DELIMITER $$
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$$
DELIMITER ;

-- 

-- Test duplicate insert with Unique Key
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.
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? ***

SELECT * FROM testing.duplicatekey;
SELECT * FROM testing.duplicatekey_log;

-- 

-- Test that after update trigger works
UPDATE testing.duplicatekey
SET Name = 'Test Unique Key'
WHERE Name = 'Test Unique Key';

SELECT * FROM testing.duplicatekey;
SELECT * FROM testing.duplicatekey_log;

--