Linux, FreeBSD, Juniper, Cisco / Network security articles and troubleshooting guides
https://forum.ivorde.com/

Mysql> stored procedure for inserting in multiple tables \w common key
https://forum.ivorde.com/mysql-stored-procedure-for-inserting-in-multiple-tables-w-common-key-t975.html
Page 1 of 1

Author:  debuser [ Tue Nov 22, 2011 5:15 pm ]
Post subject:  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 ;

Author:  debuser [ Tue Nov 22, 2011 5:17 pm ]
Post subject:  Re: Mysql> stored procedure > inserting in multiple files \w commond key

To load the above stored procedure in your mysql database, save it to a file, i.e.: insert_multiple_tables.sql and load it via input redirection:
Code:
# mysql -u user -ppassword db_name <insert_multiple_tables.sql

Page 1 of 1 All times are UTC - 5 hours [ DST ]
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/