Creating a AFTER INSERT trigger

[Vitamin]

I have a multiple table database.  I need to have 1 table that keeps track of 4 of the child tables.  The structure of the 4 child tables is as follows.

 

table1-4

id = auto_increment

masterid = (Needs to be the ID of the master table)

more fields...

 

The master table is

id = auto_increment

tablesid = The id of the table depending on which table gets a insert.

 

The trigger is this

DELIMITER $$
CREATE TRIGGER ins_masterid AFTER INSERT ON `table1`
FOR EACH ROW BEGIN
INSERT INTO mastertable (id, table1id) VALUES (NULL, NEW.id);
SET NEW.table1id = LAST_INSERT_ID();
END$$
DELIMITER ;

 

The trigger is created and runs with INSERTS, but the table1id is not inserted into the mastertable.  On every insert the value is 0.  Apparently NEW.id in the INSERT INTO line is not the right thing to use.  This is my first go at triggers, so any help would be greatly appreciated.

[Muddy_Funster]

you could try:

DELIMITER $$
CREATE TRIGGER ins_masterid AFTER INSERT ON `table1`
FOR EACH ROW BEGIN
INSERT INTO mastertable (table1id) SELECT MAX(LAST_INSERT_ID(id)) FROM table1;
END$$
DELIMITER ;

 

not sure if it is only the most recent id value that you are after or not, but the code above is assuming that it is.  Let me know how you get on with it.

[mikosiko]

the field table1id doesn't exist in your table mastertable (according to what you posted).. maybe just a typo?

 

this work for me perfectly (tested): 

 

DELIMITER $$

CREATE TRIGGER ins_masterid AFTER INSERT ON `table1`

FOR EACH ROW BEGIN

  INSERT INTO mastertable (tablesid) VALUES (NEW.id);    // I did replace table1id for tablesid (according to what you posted) and eliminate the auto-increment field (not necessary)

END$$

DELIMITER ;

[Vitamin]

Thanks for the help guys.  I guess my problem was using the AFTER INSERT instead of BEFORE INSERT.

 

Can't use keyword NEW in a AFTER INSERT trigger.  It works now, but I went another way about it in my code rather then using a trigger.

[mikosiko]

what MYSQl version do you have?

 

The trigger that I posted for you (AFTER INSERT and using NEW) does work perfectly

[Vitamin]

Server version is 5.5.15.  I run it on windows (not sure if that matters).  According to the documentation I found what I was doing wrong though.

 

In a BEFORE trigger, the NEW value for an AUTO_INCREMENT column is 0, not the automatically generated sequence number that will be generated when the new record actually is inserted.

 

Which was exactly what I was doing.  I might mess around with it more some other time, but my new solution works perfectly.

 

Originally I was using the BEFORE, but when I copy pasted my code I was trying the AFTER trigger.

 

Thanks for the help!

[mikosiko]

Maybe I should try to clarify a little...

....

 

Can't use keyword NEW in a AFTER INSERT trigger. 

 

unless you have an auto-increment field in your table (as in your case).

 

In general terms NEW will no have any effect (value) in an AFTER INSERT trigger, unless  (exception) the same row receive an UPDATE that occurs BEFORE the trigger execution, in such case NEW will show the value of the field of the last UPDATED row ... according to the manual:

"You can refer to columns in the subject table (the table associated with the trigger) by using the aliases OLD and NEW. OLD.col_name refers to a column of an existing row before it is updated or deleted. NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated."

 

that is why the trigger code that I did post works ... confusing?... is not really... MYSQL auto-increment inner functionality is the responsible of this "behavior" ... basically the row is inserted and immediately updated for the auto-increment functionality, hence the AFTER trigger works.

 

And yes, that doesn't work in a BEFORE trigger

 

 

[Vitamin]

Hey thanks for the info.  I'll dentally keep that in mind if I ever make a attempt at triggers again.