CODEWITHSUNDEEP
mysql
Alistair Laing
Alistair Laing

Reputation: 973

How Do I create a trigger on a table that insert into another database

I have two mySQL db's on called home and the other called zenphoto_live.

I want to create a trigger on zephoto_live.zp_images that inserts a record into home.tbl_new_image_inserts when ever a new/update occurs on the zenphoto_live.zp_images table. I tried

CREATE DEFINER = CURRENT_USER TRIGGER new_images AFTER INSERT ON zenphoto_live.zp_images FOR EACH ROW insert into home.tbl_new_image_inserts (id,albumid,datetime) values (zenphoto_live.zp_images.id,zenphoto_live.zp_images.albumid,now());

but I get "No Database selected" error.

Any help much appreciated.

Upvotes: 0

Views: 1639

Answers (1)

Devart
Devart

Reputation: 121902

Specify a full name for the trigger, e.g. -

CREATE DEFINER = CURRENT_USER TRIGGER zephoto_live.new_images AFTER INSERT ON
...

Or set default database for the session - 

USE zephoto_live;
CREATE DEFINER = CURRENT_USER TRIGGER new_images AFTER INSERT ON
...

Upvotes: 1

Related Questions