Triggers on two tables to get Primary Keys (Auto Increment) into third linking table mysql

Question

I am new to SQL and looking for some help. I have three tables: author, study and casestudy (which is a linking table). What I want to achieve is when data is inserted into author and study tables (from a web form) their Auto increment IDs get inserted into casestudy table if it is possible. I guess I will need to create triggers. AuthorId and StudyId in casestudy table is a composite key. Table structure is as follow:

CREATE TABLE `test`.`author` ( 
`AuthorId` INT(11) NOT NULL AUTO_INCREMENT, 
`AuthorTitle` VARCHAR(45) NOT NULL, 
PRIMARY KEY (`AuthorId`), 
UNIQUE INDEX `AuthorId_UNIQUE` (`AuthorId` ASC));

CREATE TABLE `test`.`study` ( 
`StudyId` INT(11) NOT NULL AUTO_INCREMENT, 
`Title` VARCHAR(45) NOT NULL, 
PRIMARY KEY (`StudyId`), 
UNIQUE INDEX `StudyId_UNIQUE` (`StudyId` ASC));

CREATE TABLE `test`.`casestudy` ( 
`AuthorId` INT(11) NOT NULL, 
`StudyId` INT(11) NOT NULL, 
PRIMARY KEY (`AuthorId`, `StudyId`), 
INDEX `StudyId_idx` (`StudyId` ASC), 
CONSTRAINT `AuthorId` 
FOREIGN KEY (`AuthorId`) 
REFERENCES `test`.`author` (`AuthorId`) 
ON DELETE NO ACTION 
ON UPDATE NO ACTION, 
CONSTRAINT `StudyId` 
FOREIGN KEY (`StudyId`) 
REFERENCES `test`.`study` (`StudyId`) 
ON DELETE NO ACTION 
ON UPDATE NO ACTION);

Any advice will be appreciated. Thank you.

score 2 · Accepted Answer · edited May 23 '17 at 11:50

2

As far as I can see there is no data link between author and study tables. There is no such functionality as "one trigger on two tables update at a time". The best place to implement casestudy maintenance is in the procedure that populates the author and study tables during web form processing.

In PHP that can be done through collecting IDs after each Insert using mysql_insert_id(); (see Inserting data into multiple tables using php via a web form) and then using them all to update the linking table.

Using mysqli (and assuming $mysqli is the connection) that should be something like:

$AuthorTitle = $mysqli->real_escape_string($_POST[AuthorTitle value]);
$mysqli->query("INSERT INTO test.author(AuthorTitle) VALUES('$AuthorTitle')");
$AuthorId = $mysqli->insert_id;

$StudyTitle = $mysqli->real_escape_string($_POST[StudyTitle value]);
$mysqli->query("INSERT INTO test.study(Title) VALUES('$StudyTitle')");
$StudyId = $mysqli->insert_id;

$mysqli->query("INSERT INTO test.casestudy(AuthorId, StudyId) VALUES($AuthorId, $StudyId)");

Alternatively all three tables can be consistently populated through a stored procedure (or just an SQL script) that would take care of all the relevant web form fields in one go. Then after each Insert auto-generated ID can be collected using LAST_INSERT_ID(); (see LAST_INSERT_ID() MySQL).

$AuthorTitle = $mysqli->real_escape_string($_POST[AuthorTitle value]);
$StudyTitle = $mysqli->real_escape_string($_POST[StudyTitle value]);
$mysqli->multi_query("
    START TRANSACTION;
        INSERT INTO test.author(AuthorTitle) VALUES('$AuthorTitle');
        SET @AuthorId = LAST_INSERT_ID();
        INSERT INTO test.study(Title) VALUES('$StudyTitle');
        SET @StudyId = LAST_INSERT_ID();
        INSERT INTO test.casestudy(AuthorId, StudyId) VALUES(@AuthorId, @StudyId);
    COMMIT;
");

Finally this scenario can be implemented through an Updatable and Insertable View across all three tables that would take care of storing the data in consistent manner.

edited May 23 '17 at 11:50

Community

1
1

answered Feb 22 '16 at 11:38

Y.B.

3,526
14
24

Hi and thank you, maybe I have not explained properly myself. What I meant was to have trigger on author table and trigger on study table. Once the data is entered their auto increment IDs would be copied into linking table into relevant columns. – Vlad Feb 22 '16 at 11:53
@Vlad I think you were quite clear. Suppose there is a trigger on `author` and it ran on `after insert`. It has new `Inserted.AuthorId`, but what would be the `StudyId` to put into `casestudy` for the same record? The same for the trigger on `author` alone. We need a place where both IDs are available. That place might be the web form processing procedure (accessing the newly generated IDs through [LAST_INSERT_ID()](http://stackoverflow.com/questions/17112852/get-the-new-record-primary-key-id-from-mysql-insert-query)) or an Insertable View that would populate all three tables in one go. – Y.B. Feb 22 '16 at 12:01
Thank you for your tips. As I have mentioned I am new to this so it will take me some time to understand the problem. I do understand what you mean by same IDs for AuthorId and StudyId. Is the LAST_INCREMENT_ID() to be used in my webform or somewhere in sql? Sorry to be pain. Thank you – Vlad Feb 22 '16 at 12:29
@Vlad No worries. That really depends on how you populate the tables. If you run separate `Insert` statement for each table from PHP then after each `Insert` you also need to run `SELECT LAST_INSERT_ID();`, store the results into local variables and when all the IDs are collected use them to populate `casestudy`. Ideally though the whole update should happen in one transaction. To that end it's best to call from PHP a stored procedure that would handle all the required web form fields in one go. Then `SELECT LAST_INSERT_ID();` can be used in this procedure in a similar manner. – Y.B. Feb 22 '16 at 13:07
1

@Vlad PHP has got a direct call for MySQL `LAST_INCREMENT_ID()` through `mysql_insert_id();`. Have a look at the answer code sample here: [Inserting data into multiple tables using php via a web form](http://stackoverflow.com/questions/2262767/inserting-data-into-multiple-tables-using-php-via-a-web-form) – Y.B. Feb 22 '16 at 13:21
That looks exactly like what I need. I will try to use it and will let you know. Thank you – Vlad Feb 22 '16 at 14:00
As I said I will let you know if it is working. It does work perfectly. However if you don't mind. How could I protect this against injection? Referring to your second example where everything is done in one SQL script. I hope I am not bothering you too much. Thank you – Vlad Feb 23 '16 at 15:23
1

@Vlad Basic protection is there through `$mysqli->real_escape_string` which is designed to sanitize input parameters. Another approach (which I would prefer in my code, but have not mentioned in this answer for simplicity) would be to pass parameters as variables: [mysqli_stmt_bind_param](http://php.net/manual/en/mysqli-stmt.bind-param.php). But then again, I would probably have SQL code in stored procedure and call it in one line. Multi-line SQL code blocks in PHP stuffed all over with "?" would be difficult to read / debug. – Y.B. Feb 23 '16 at 16:37
Thank you, that is what I thought that using mysqli_stmt_bind_param would mess up statement (difficult to read / debug) as you say. I will stick to $mysqli->real_escape_string for now. – Vlad Feb 23 '16 at 16:45
Hi, @Y.B. as you definitely know what you are talking about would you be able to help with this? http://stackoverflow.com/questions/35604039/insert-into-database-from-duplicate-textfield-if-not-empty-php-sql – Vlad Feb 24 '16 at 18:49

Triggers on two tables to get Primary Keys (Auto Increment) into third linking table mysql

1 Answers1