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

[Vladimir Velan]

Hi guys, I am new to SQL and looking for some help. I have a three tables: author, study and casestudy(which is linking table). What I want to achieve is when data are inserted into author and study tables (from webform) their Auto increment ID will be inserted into "casestudy" table if it is possible. I guess I will need to create triggers. Table structure is as follow:


CREATE TABLE `test`.`new_table` (
`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