trigger newbie: odd "fail" behavior?

[7 reeds]

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?