Skip navigation links

MySQL Forums :: Triggers :: Trigger: "NEW.col1" value is OK, but I can't use it in triggers subquery!(?)


Advanced Search

Trigger: "NEW.col1" value is OK, but I can't use it in triggers subquery!(?)
Posted by: Maran Randal ()
Date: April 09, 2012 10:08PM

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;

Options: ReplyQuote


Subject Views Written By Posted
Trigger: "NEW.col1" value is OK, but I can't use it in triggers subquery!(?) 1045 Maran Randal 04/09/2012 10:08PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.