-- 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,
    Comment 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.Comment: ', IFNULL(NEW.Comment, 'NULL'), ', 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.Comment: ', IFNULL(NEW.Comment, 'NULL'), ', 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.Comment: ', IFNULL(NEW.Comment, 'NULL'),
        ', 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.Comment: ', IFNULL(NEW.Comment, 'NULL'),
        ', 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_log(description) VALUES ('1');

INSERT INTO testing.duplicatekey (Name) VALUES ('Test Unique Key 1')
ON DUPLICATE KEY UPDATE Name = 'Test Unique Key 1'; -- Generates row with id:1. Before & After Insert triggers complete.

INSERT INTO testing.duplicatekey_log(description) VALUES ('2');

INSERT INTO testing.duplicatekey (Name) VALUES ('Test Unique Key 1')
ON DUPLICATE KEY UPDATE Name = 'Test Unique Key 1'; -- Updates row with id:1. Before Insert & Before Update triggers complete, missing After Update? ***

--

-- Test duplicate insert with Unique Key, with Comment
INSERT INTO testing.duplicatekey_log(description) VALUES ('3');

INSERT INTO testing.duplicatekey (Name, Comment) VALUES ('Test Unique Key 2', '3')
    ON DUPLICATE KEY UPDATE Name = 'Test Unique Key 2', Comment = '3'; -- Generates row with id:3. Before & After Insert triggers complete.

INSERT INTO testing.duplicatekey_log(description) VALUES ('4');

INSERT INTO testing.duplicatekey (Name, Comment) VALUES ('Test Unique Key 2', '4')
    ON DUPLICATE KEY UPDATE Name = 'Test Unique Key 2', Comment = '4'; -- Updates row with id:3. Before Insert, Before Update & After Update triggers complete

-- 

-- Test update with nothing to update (As per Unique Key 1)
INSERT INTO testing.duplicatekey_log(description) VALUES ('5');

INSERT INTO testing.duplicatekey (Name, Comment) VALUES ('Test Unique Key 3', '5'); -- Generates row with id:5. Before & After Insert triggers complete.

INSERT INTO testing.duplicatekey_log(description) VALUES ('6');

UPDATE testing.duplicatekey 
set Name = 'Test Unique Key 3'
WHERE Name = 'Test Unique Key 3'; -- Updates row with id:5. Before Update & After Update triggers complete. ***

-- 

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

--