Hello!
Every one.
I met some difficult questions when I was developing an application.
We have an old table object whose definition is:
CREATE TABLE object (
name name, -- object’s name
pid oid, -- parent’s object ID
PRIMARY KEY(oid),
FOREIGN KEY(pid)
REFERENCES object(oid)
ON DELETE SET NULL
);
The table “object” is used as a base table and there are many tables(over 1500) that is derived from it for we treat almost every thing as object. Though the idea isn’t bad and it can really simplify our conceptual mode, it also results in low performance. When we want to find an object from table “object”, it will perform searching in 1500 tables, the long time to wait is insufferable. And the request that gives a pid to find childen oid is called frequently.
So we decide to reimplement the system and now we treat parent-child relationship is a common relationship. The relationship is named “containment”. For the “pid” field is wild used by other programmers, we can’t delete it. Then we have a task to keep the two table existent. To reduce the possibility to make mistakes, we define triggers on every table.
Now the system can work well when we do “insert”, “delete” or “update” operation. But when I drop a table that is inherits from “object” or the table “object” itself, the triggers on the table will not work. To avoid such cases, I wrote a function “_test_delete_and_drop()” that will delete all columns in table “object” and then drop the table. But I met puzzling information when I do that.
I have two questions:
1. Why don’t the triggers work when drop a table?
2. I can do “delete” and “drop” without any problem. Why do the error come out when I combine the two operation together?
Note: The source code is in the accessory(test.sql).
Could any one help?
Any effort will be appreciated.
Thanks very much!