Trigger: "NEW.col1" value is OK, but I can't use it in triggers subquery!(?)
Hello, I spent a lot of time(2days) by testing, but without any success :(
I didn't expect that this can be a problem...
I will be relly glad for any help...
### MY QUESTION:
I can't understand why this TRIGGER allways returns NULL in log2 column?? (log1,log2,log3) ('84', NULL, 'TEXT')
### DB TRIGGER ON fwdzone TABLE
DROP TRIGGER IF EXISTS fwdzone_insert|
CREATE TRIGGER fwdzone_insert AFTER INSERT ON fwdzone
FOR EACH ROW
BEGIN
INSERT INTO mysql_trigger_log (log1,log2,log3)
VALUES (NEW.data_id,(SELECT hname FROM fwddns WHERE fwddns.id=NEW.data_id AND horder=1),'TEXT');
END|
### I tested the subquerry independently(it works). (80("NEW.data_id") should be automatically passed by trigger, but isn'n)
SELECT hname FROM fwddns WHERE fwddns.id=80 AND horder=1
### THERE ARE 3 TABLES IN DATABASE
CREATE TABLE IF NOT EXISTS `mysql_trigger_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`log1` text CHARACTER SET utf8 COLLATE utf8_czech_ci,
`log2` text CHARACTER SET utf8 COLLATE utf8_czech_ci,
`log3` text CHARACTER SET utf8 COLLATE utf8_czech_ci,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ;
CREATE TABLE IF NOT EXISTS `fwdzone` (
`data_id` bigint(20) NOT NULL AUTO_INCREMENT,
`domain` varchar(254) NOT NULL DEFAULT '',
`createmod` date DEFAULT NULL,
`lastmod` datetime DEFAULT NULL,
`regmod` date DEFAULT NULL,
`expiremod` datetime DEFAULT NULL,
`engineer` varchar(254) DEFAULT '',
`error_message` varchar(254) DEFAULT '',
`responsiblemail` varchar(64) DEFAULT '',
`serialdate` varchar(8) NOT NULL DEFAULT '',
`serialnum` int(11) DEFAULT '0',
`ttl` int(11) DEFAULT '0',
`refresh` int(11) DEFAULT '0',
`retry` int(11) DEFAULT '0',
`expire` int(11) DEFAULT '0',
`minimum` int(11) DEFAULT '0',
`userid` varchar(40) DEFAULT '',
`lastexp` datetime DEFAULT NULL,
`slaveonly` varchar(1) NOT NULL DEFAULT 'N',
`zonefilepath1` varchar(254) DEFAULT '',
`zonefilepath2` varchar(254) DEFAULT '',
`customer` smallint(5) unsigned NOT NULL DEFAULT '0',
`aliasdataid` bigint(20) DEFAULT '0',
`admingrp` varchar(40) DEFAULT '',
PRIMARY KEY (`data_id`),
KEY `fwdzone_customer` (`customer`),
KEY `fwdzone_domain` (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=84 ;
CREATE TABLE IF NOT EXISTS `fwddns` (
`id` bigint(20) NOT NULL DEFAULT '0',
`hname` varchar(100) NOT NULL DEFAULT '',
`ttl` int(11) DEFAULT '0',
`horder` tinyint(3) unsigned NOT NULL DEFAULT '0',
KEY `fwddns_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;