# i-senso import/export
# i-senso/2006
# http://www.i-senso.com/
#
# The format of this file is very simple. Every statement _must_ start at column 1. If it spans for more than one line,
# others than the first _must_ start with tabs or spaces. That's all.
#
DROP DATABASE onkeyupdate_trigger;
CREATE DATABASE onkeyupdate_trigger;
USE onkeyupdate_trigger;
-- CHARSET --
SET NAMES utf8;

-- DROP RELATIONS (will be rebuilt later) --
DROP PROCEDURE IF EXISTS __clear_constraints;
CREATE PROCEDURE `__clear_constraints`()
	BEGIN
	DECLARE _res INT;
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _res = 0;



	END
CALL __clear_constraints();
DROP PROCEDURE IF EXISTS __clear_constraints;

-- TABLES --

---- Table "tab" --
DROP TABLE IF EXISTS `tab`;
CREATE TABLE `tab` (
	`Id` int(11) NOT NULL,
	`Text` varchar(256) default NULL
	) ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_general_ci;

INSERT INTO `tab` (`Id`,`Text`) VALUES
	(1,'This one was update by the ON DUPLICATE KEY UPDATE but the trigger didn\'t run');

-- VIEWS --


DROP PROCEDURE IF EXISTS __table_exists;
DROP PROCEDURE IF EXISTS __table_exists_;
DROP PROCEDURE IF EXISTS __create_table;

-- "CONSTRAINTS" --

DROP PROCEDURE IF EXISTS __clear_constraints;
CREATE PROCEDURE `__clear_constraints`()
	BEGIN
	DECLARE _res INT;
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _res = 0;

	ALTER TABLE `tab` DROP PRIMARY KEY;

	END
CALL __clear_constraints();
DROP PROCEDURE IF EXISTS __clear_constraints;

---- Indexes for tab --
ALTER TABLE `tab` ADD PRIMARY KEY (`Id`);
---- Constraints for tab --

-- "TRIGGERS" --

DROP TRIGGER `tab_on_aupd`
CREATE TRIGGER tab_on_aupd AFTER UPDATE ON tab
	FOR EACH ROW SET @output="TRIGGER was run"

-- "STORED PROCS" --

---- PROCEDURE "test" --
DROP PROCEDURE IF EXISTS `test`
CREATE PROCEDURE `test`()
	BEGIN

	TRUNCATE TABLE tab;
	-- Insert a row.
	SELECT "Insert a row...";
	INSERT INTO tab (Id, Text) VALUES (1, "Inserted row");
	-- Updates run the triggers.
	SET @output = "** Trigger not run **";
	SELECT "Updates run the triggers...";
	UPDATE tab SET Text = "updated row";
	SELECT * FROM tab;
	SELECT *, @output FROM tab;
	-- But On Duplicate Key don't.
	SET @output = "** Trigger not run **";
	INSERT INTO tab (Id, Text) VALUES (1, "This one won't be inserted")
		ON DUPLICATE KEY UPDATE Text = "This one was update by the ON DUPLICATE KEY UPDATE but the trigger didn't run";
	SELECT *, @output FROM tab;

	END