SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';

DROP TRIGGER IF EXISTS befins_asset;
DROP TRIGGER IF EXISTS befupd_asset;
DROP TRIGGER IF EXISTS aftins_asset_version;

DROP TABLE IF EXISTS asset_version;
DROP TABLE IF EXISTS asset;

CREATE TABLE `asset` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `VersionID` int(10) unsigned NOT NULL,
  `IsPublished` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  INDEX (VersionID)
) ENGINE=InnoDB;

CREATE TABLE `asset_version` (
  `id` int(10) unsigned NOT NULL,
  `VersionID` int(10) unsigned NOT NULL auto_increment,
  `IsPublished` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`VersionID`),
  FOREIGN KEY (`id`) REFERENCES `asset` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

DELIMITER //
CREATE TRIGGER `befins_asset` BEFORE INSERT ON `asset` FOR EACH ROW BEGIN
  SET NEW.IsPublished = 0;
END//

CREATE TRIGGER `aftins_asset_version` AFTER INSERT ON `asset_version` FOR EACH ROW BEGIN
  UPDATE `asset`
  SET `id` = NEW.`id`, `VersionID` = NEW.`VersionID`
  WHERE id = NEW.id AND (NEW.IsPublished OR VersionID = 0);
END//

-- Adding this trigger in will stop the problem from occurring
-- CREATE TRIGGER `befupd_asset` BEFORE UPDATE ON `asset` FOR EACH ROW BEGIN	
--  SET NEW.IsPublished = 0;
-- END//

DELIMITER ;

BEGIN;
INSERT INTO `asset` (`IsPublished`) VALUES  (1);

-- This will emit an error "VersionID cannot be null"
INSERT INTO `asset_version` (`IsPublished`,id) VALUES  (1, LAST_INSERT_ID());