MySQL Forums
Forum List  »  Triggers

Use DDL-Statement(s) in a Trigger
Posted by: Lars Bischoff
Date: April 30, 2009 02:19AM

Hello dear community,

I am currently developing an application where the user is able to create customers as well as documents, and assign these documents to the customers. The customers are hereby stored in a table 'customers', where each row represents a customer. Each document, however, is represented as a separate table which is mapped to the customer in a third table called 'documents2customers' that contains
a) the customer's id
b) the table's name

There may be customers without documents, but there may never be a document without a customer, which is why I use referential integrity to synchronize the tables 'customer' and 'documents2customers'.
Since the document-tables are created dynamically upon a user-interaction in the UI, I create them manually and also update the table 'documents2customers' also manually. If a customer is deleted, all of its assigned documents are ought to be deleted too, which is done by a foreign-key constraint. However, only the mapping-entries in the 'documents2customers' is deletable by that. The document-table itself still remains.
I wanted to solve this problem by a trigger that listens on the table 'documents2customers' for deleting-events and then removes the corresponding table, but it seems that it is not possible to use DDL statements inside a trigger?!

Is there any workaround, or do I have to solve this manually by code?

Best regards,

Options: ReplyQuote

Written By
Use DDL-Statement(s) in a Trigger
April 30, 2009 02:19AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.