Errors when deleting cords and droping table at the same time
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!
---------------------------------
Do You Yahoo!?
锟斤拷锟斤拷锟斤拷太小锟斤拷锟脚伙拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟捷ｏ拷