MySQL Trigger on after insert

Question

I am new to MySQL. I have two tables total_loaner and available_loaner. I am trying to create a trigger for every new row added in total_loaner, I would also like to add that new row to available_loaner.

Here how my tables look like:

CREATE TABLE `total_loaner` (   `Kind` varchar(10) NOT NULL,   `Type` varchar(10) NOT NULL,   `Sno` varchar(10) NOT NULL,   PRIMARY KEY (`Sno`) )  CREATE TABLE `available_loaner` (   `Kind` varchar(10) NOT NULL,   `Type` varchar(10) NOT NULL,   `Sno` varchar(10) NOT NULL,   `Status` char(10) NOT NULL DEFAULT '',   PRIMARY KEY (`Sno`) )  

My trigger does not seem to work.

CREATE TRIGGER new_loaner_added  AFTER INSERT ON 'total_loaner' for each row begin INSERT INTO available_loaner (Kind, Type, Sno, Status) Values (new.Kind, new.Type, new.Sno, 'Available'); END; 

Answer 1

In your case you can rewrite your trigger like this

CREATE TRIGGER new_loaner_added  AFTER INSERT ON total_loaner FOR EACH ROW    INSERT INTO available_loaner (Kind, Type, Sno, Status)   VALUES (NEW.Kind, NEW.Type, NEW.Sno, 'Available'); 

Note:

• single quotes removed from table name total_loaner, because quotes effectively makes it a string literal instead of a proper identifier. You can use back ticks if you want but it's unnecessary since it's not a reserved word and it don't contain any special characters.
• since it's a one-statement trigger now you don't need to use DELIMITER command and BEGIN...END block

Here is SQLFiddle demo