Mysql> stored procedure for inserting in multiple tables \w common key
Below is an example of a mysql stored procedure that:
1) drops the procedure if it already exists
2) creates procedure and defines the fields that it accepts
3) declares an internal variable
4) inserts a row in multiple tables having a common key all tables using last_insert_id() from the first table as their first column's value
5) sets the internal variable to the last_insert_id()
6) selects the internal variable, returning it to the calling handler
Code:
DELIMITER //
DROP PROCEDURE IF EXISTS insert_multiple_tables
//
CREATE PROCEDURE insert_multiple_tables(product_hash char(32), user_id smallint, product_ip char(10), product_dir ENUM('p1','p2','p3','p4'), product_name varchar(100), product_type varchar(30), product_ext varchar(10), product_size bigint)
BEGIN
DECLARE RETURN_VAL INT UNSIGNED DEFAULT 0;
START TRANSACTION;
INSERT INTO f (product_id, product_hash, user_id)
VALUES('', product_hash, user_id);
INSERT INTO product_info (product_id, product_upload_date, product_ip )
VALUES(LAST_INSERT_ID(), NOW(), product_ip);
INSERT INTO product_location (product_id, product_dir, product_node)
VALUES(LAST_INSERT_ID(), product_dir, '0');
INSERT INTO product_properties(product_id, product_name, product_type, product_ext, product_size)
VALUES (LAST_INSERT_ID(), product_name, product_type, product_ext, product_size);
INSERT INTO product_status(product_id, product_private)
VALUES (LAST_INSERT_ID(), '0');
INSERT INTO product_download(product_id, product_d_date, product_d_ip)
VALUES (LAST_INSERT_ID(), CURDATE(), '0');
SET RETURN_VAL = LAST_INSERT_ID();
SELECT RETURN_VAL;
COMMIT;
END//
DELIMITER ;