MySQL Forums
Forum List  »  Triggers

trigger newbie: odd "fail" behavior?
Posted by: 7 reeds
Date: July 05, 2012 12:13PM

Hi,

I was trying out a trigger test this morning. I have two identical tables:

CREATE TABLE IF NOT EXISTS `testA` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `thing` varchar(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `thing` (`thing`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `testB` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `thing` varchar(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `thing` (`thing`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Trigger I was trying to test:
CREATE TRIGGER `test_trigger` AFTER INSERT ON `testA` FOR EACH ROW BEGIN
    IF NEW.thing = 'fail' THEN
        CALL ADMIN_force_trigger_failure;
    END IF;

    INSERT INTO testB (thing) VALUES(NEW.thing);
END

The idea is that records could constantly be flowing into testA. The "after" trigger will copy all records into testB unless a record has "fail" in the "thing" column. If "fail" exists then the Non-Existent stored procedure is called wich aborts the trigger. This is not elegant but there are no "exit" or "return" statements that I can find.

Anyway, I set up a sequence of test inserts:
INSERT INTO `testA`(`thing`) VALUES ('a');
INSERT INTO `testA`(`thing`) VALUES ('b');
INSERT INTO `testA`(`thing`) VALUES ('fail');
INSERT INTO `testA`(`thing`) VALUES ('d');
INSERT INTO `testA`(`thing`) VALUES ('e');

This is launced in one operation, not 5 individual inserts. What I expect is that textB will end up holding all five values plus I'll be shown a DB message about the missing stored proc.

My first lesson was that that is NOT the case. When executed as one grouped operation I get the values: 'a', 'b', 'fail', 'fail', 'fail', 'fail'. Yeah, that is six total inserted records for five insert operations.

I have NOT been able to reproduce this behavior since.

Most recently I have only seen the 'a' and 'b' values to appear, no 'fail'.

If I issue the inserts one at a time though I get what i initially expected.

So I suppose my questions are:
1) is there something wrong with using the missing stored proc as an abort -- or, is there a better way in MySQL 5.1 to gently exit a trigger?
2) should the behavior change?

Options: ReplyQuote


Subject
Views
Written By
Posted
trigger newbie: odd "fail" behavior?
1665
July 05, 2012 12:13PM


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.