current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute 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 0 down vote favorite

I got a big problems here. Let's say I got the table A (with columns like id, name etc) in the main database. So when I making INSERT into A I need to create new database lets say B_id. I got .backup file with database sturcture which suppose to use B_id databases.

I guess I need to write trigger which activates script.

But how I suppose to wrtie the script which creates database(with name depends on id number) and applies .backup file to it? Is it even okay to do like that?

share|improve this question
    
This doesn't make any sense. Do you actually mean "database" when you say "database" for "B_id" ? –  Craig Ringer Mar 26 at 10:30
    
@CraigRinger yeah I mean new database with tables and stored procedures, triggers etc inside –  Danil Gholtsman Mar 26 at 10:33
2  
So you want to run CREATE DATABASE and/or pg_restore or load an SQL script, using a trigger, when you do an insert into table on a different DB? Why on earth do you want to do that? –  Craig Ringer Mar 26 at 10:46
1  
Then I'm glad I don't have that task. Sounds like a terrible idea, strongly suggest re-thinking the whole thing. Start with "what am I actually trying to achieve by doing this" and go from there. –  Craig Ringer Mar 26 at 11:07
2  
It's not your fault your employer has weird ideas about how to do things. Doing what you want is possible - you can use dblink from a trigger, for example. It's just weird, awkward, and clumsy. –  Craig Ringer Mar 26 at 11:46
show 7 more comments

put on hold as unclear what you're asking by Craig Ringer, Paul White, RolandoMySQLDBA, Mark Storey-Smith, Kin Apr 1 at 16:33

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

up vote 3 down vote accepted

Creating a database inside a transaction is not possible, so that precludes doing it directly in a trigger, and more generally in any function called by the SQL engine.

The usual method to issue script-like operations from a trigger is to have such script running as a daemon and listening to events through the SQL LISTEN command, the events being signalled from the trigger with a NOTIFY command, possibly including a payload with parameters. If the transaction is rolled back, its notifications are not sent.

From a design point of view, this is a misuse of triggers. A trigger that "does something" is a side effect by nature and side effects are more a problem than a solution in programming. Creating and populating a new database is a massive side-effect.

Unfortunately some think that triggers being cool, they should use and abuse them no matter what problem they're trying to solve.

share|improve this answer
add comment

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