SEPERATE INSERTS
================

--source include/have_innodb.inc
#Enable binary logging
--source include/have_log_bin.inc

# Here we use mixed based formats and seperate inserts. We get the consecutive numbers
# in auto_increment column.

SET BINLOG_FORMAT='MIXED';
CREATE DATABASE IF NOT EXISTS ptr_ob1;
USE ptr_ob1;
CREATE TABLE ptr_ob1.t1(id INT, a INT, b CHAR(5))ENGINE=INNODB;
CREATE TABLE ptr_ob1.t3(srno INT AUTO_INCREMENT, PRIMARY KEY(srno), name VARCHAR(30) NOT NULL, old_a INT)ENGINE=INNODB;
--echo **** Creating Triggers ****

delimiter ||;
CREATE TRIGGER ptr_ob1.trg1 BEFORE INSERT ON ptr_ob1.t1 FOR EACH ROW
BEGIN
 INSERT INTO ptr_ob1.t3 VALUES(NULL, 't1', new.a);
END;||
delimiter ;||

INSERT INTO ptr_ob1.t1 VALUES(1,20, 'bb1');
INSERT INTO ptr_ob1.t1 VALUES(2,50, 'bb2');
INSERT INTO ptr_ob1.t1 VALUES(3,80,'bb3');
SELECT * FROM ptr_ob1.t1;
SELECT * FROM ptr_ob1.t3;
INSERT INTO ptr_ob1.t3 VALUES(NULL,'t1', 20);
SELECT * FROM ptr_ob1.t3;

RESULTS
=======

SET BINLOG_FORMAT='MIXED';
CREATE DATABASE IF NOT EXISTS ptr_ob1;
USE ptr_ob1;
CREATE TABLE ptr_ob1.t1(id INT, a INT, b CHAR(5))ENGINE=INNODB;
CREATE TABLE ptr_ob1.t3(srno INT AUTO_INCREMENT, PRIMARY KEY(srno), name VARCHAR(30) NOT NULL, old_a INT)ENGINE=INNODB;
**** Creating Triggers ****
CREATE TRIGGER ptr_ob1.trg1 BEFORE INSERT ON ptr_ob1.t1 FOR EACH ROW
BEGIN
INSERT INTO ptr_ob1.t3 VALUES(NULL, 't1', new.a);
END;||
INSERT INTO ptr_ob1.t1 VALUES(1,20, 'bb1');
INSERT INTO ptr_ob1.t1 VALUES(2,50, 'bb2');
INSERT INTO ptr_ob1.t1 VALUES(3,80,'bb3');
SELECT * FROM ptr_ob1.t1;
id      a       b
1       20      bb1
2       50      bb2
3       80      bb3
SELECT * FROM ptr_ob1.t3;
srno    name    old_a
1       t1      20
2       t1      50
3       t1      80
INSERT INTO ptr_ob1.t3 VALUES(NULL,'t1', 20);
SELECT * FROM ptr_ob1.t3;
srno    name    old_a
1       t1      20
2       t1      50
3       t1      80
4       t1      20