Trigger insert into multiple tables

Question

I have got 2 tables named: train_information and axle.

Train_information

has the folowing:

train_id (PK)
train_name
train_length
number_of_axles

axle

has the folowing:

axle_id (PK)
train_id(FK)
axle

When I insert something on train_information and let's say for example put the number 16 in number_of_axles. I want the table axle to get that same information automatically.

How do i do this with a trigger?

EDIT:

it works now with this code:

CREATE TRIGGER TriggerName AFTER INSERT ON train_information
FOR EACH
ROW 
BEGIN 
INSERT INTO axle( train_id, axle ) 
VALUES (
NEW.train_id, NEW.number_of_axles
);

END

But right now, it enters for example the number 16 in the table axle but i want it to go like:

axle_id = 1
train_id = 1
axle = 1

axle_id = 2
train_id = 1
axle = 2

axle_id = 3
train_id = 1
axle = 3

Is this posible?

Stanislovas Kalašnikovas · Accepted Answer · 2015-03-10 09:34:06Z

1

You can create AFTER TRIGGER on Train_information table, something like this:

SQL More about After Triggers in SQL

CREATE TRIGGER TriggerName
   ON Train_information
   FOR INSERT
   AS
   BEGIN
      SET NOCOUNT ON
      INSERT INTO axle
        (train_id, axle)
    SELECT
        train_id, 'axle_name'
        FROM inserted
    END

MySQL More about After Triggers in MySQL

DELIMITER $$
CREATE TRIGGER TriggerName
   AFTER INSERT ON Train_information FOR EACH ROW
   BEGIN
      INSERT INTO axle (train_id, axle)
      VALUES (NEW.train_id, NEW.axle_name)            
    END;
$$
DELIMITER ;

edited Mar 10, 2015 at 9:34

answered Mar 10, 2015 at 9:09

Stanislovas Kalašnikovas

9,0735 gold badges36 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mitch Over a year ago

I get the following error: MySQL meldt: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON train_information FOR INSERT AS BEGIN SET NOCOUNT ON ' at line 2 I'm using phpmyadmin

Stanislovas Kalašnikovas Over a year ago

So are you using MySQL?

Mitch Over a year ago

Yes, i click on: add trigger. There i have to fill in the name etc. and a field that says: Definition. There i fill the code in.

Stanislovas Kalašnikovas Over a year ago

Updated my answer how It should be in MySQL, check It. Also added documentation to learn about triggers.

Mitch Over a year ago

It still gives a syntax error, but i think i'm doing something wrong. I'l take a look at the documentation! thank you!

|

Collectives™ on Stack Overflow

Trigger insert into multiple tables

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related