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?