What happens to a Trigger if a table is dropped and rename and recreated with same name

Question

What happens to a Trigger if a table is dropped and rename and recreated with same name?

For e.g:

We have a Trigger X which is define for Table A.
We have a Trigger Y which is define for Table B.

Trigger X is owned by SchemaA
Trigger Y is owned by SchemaB

Now everyday below steps a done during non-business hrs :

1. Drop Table B.
2. Rename Table A to Table B.
3. Create Table A.
4. Drop Trigger X & Y and Re-create same Trigger X & Y.

For some reasons above Step 4 was next executed means Trigger X & Y was not dropped and recreated. And everything seemed to be fine till the next day non-business activity started

Actual problem started when we were unable to drop Table B next day.

I wanted to know does the problem occurred because Trigger was not dropped and re-created. Here I understand, When a table is dropped corresponding Triggers are dropped.

What exactly would have happened?

As per my understanding below things have happened

1. Drop Table B. (Table B is renamed as $BIN$B) (Now what will happen to its trigger)
2. Rename Table A to Table B.(Which trigger (X or Y) is Pointing to Table B NOW)
3. Create Table A. (Does Trigger X is still pointing to Table A)

As Trigger is owned by two different Schema what would have stopped from dropping Table B?

Answer 1

The SQL:2008, draft version, has this to say in Part 11, Schemata :

(in the description of the INFORMATION_SCHEMA table that describes triggers)

...

CONSTRAINT TRIGGERS_FOREIGN_KEY_SCHEMATA FOREIGN KEY ( TRIGGER_CATALOG, TRIGGER_SCHEMA ) REFERENCES SCHEMATA,

CONSTRAINT TRIGGERS_REFERENCES_TABLES CHECK ( EVENT_OBJECT_CATALOG <> ANY ( SELECT CATALOG_NAME FROM SCHEMATA ) OR ( EVENT_OBJECT_CATALOG, EVENT_OBJECT_SCHEMA, EVENT_OBJECT_TABLE ) IN ( SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM TABLES ) )

...

The first constraint merely states that a trigger cannot exist if the schema it is in does not itself exist,

The second one states that the EVENT_OBJECT_TABLE (the table from which the trigger is fired) must exist in the catalog and schema, or else if that other condition evaluates to true.

So dropping the table and leaving the trigger can only possibly be a valid option if "that other condition" evaluates to true [or unknown, but let's not get into that]. Let's take a look.

EVENT_OBJECT_CATALOG <> ANY ( SELECT CATALOG_NAME FROM SCHEMATA )

is a <quantified comparison predicate> as per chpt 8.9, pg 421 [Part 2, foundations] in my draft version. The relevant part of the spec here is :

c) If the implied <comparison predicate> is True for at least one row RT in T, then “R <comp op> <some> T” is True.
d) If T is empty or if the implied <comparison predicate> is False for every row RT in T, then “R <comp op> <some> T” is False.

So, the condition becomes true if there exists at least one row in SELECT CATALOG_NAME FROM SCHEMATA whose catalog name differs from the catalog that the triggering table is in.

So what all of this seems to say is :

If you have only one single CATALOG_NAME in your database, then dropping a table while leaving a trigger that is fired off of it, is invalid. Either the DROP should be rejected or it should something like CASCADE DELETE its corresponding triggers.

If you have some CATALOG_NAME in your database that is different from the CATALOG_NAME that the triggering table is in, dropping a table while leaving the trigger seems perfectly OK (oddly enough, I must say).

Final disclaimer : Since this is a rule that derives from Part 11 of the SQL standard, of course it only applies to products that claim to support Part 11 of the standard.