Explore our sites

Stack Exchange
tour help
Take the tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

Is there a way to create a trigger in mysql without hardcoding the database name?

Doing a typical create trigger will stop working if I dump the database and then import it into a database of a different name.

share|improve this question
add comment

migrated from serverfault.com Dec 10 '13 at 20:54

This question came from our site for professional system and network administrators.

2 Answers

up vote 1 down vote

I guess I'll guess an answer to my question. A lot of mysql documentation just references a table name without specifying a database, so I am assuming the database is optional and if you don't include it, mysql will use the current one.

share|improve this answer
2  
This is the default behavior on most database systems I've encountered: "If the database name / schema namespace is not specified, assume the current one." –  voretaq7 Dec 10 '13 at 22:31
add comment
up vote 0 down vote

If you mysqldump a table with

mysqldump --triggers ...

the code for the trigger definition is included.

However, there is a hardcoding situation you want to be cognizant of

DELIMITER $$

CREATE TRIGGER trigger1 AFTER UPDATE ON mytable FOR EACH ROW 
BEGIN 

  insert into outsidedb.audir_trail values (...);

END $$

DELIMITER ;

If you mysqldump mytable and import it into another DB Server, the trigger works only if and only if the database outsidedb exists on the new DB Server and has the table called audit_trail.

Therefore, please keep aware of all hardcoding situations inside and outside of your triggers.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.