What happens to a Trigger if a table is dropped and rename and recreated with same name?
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 :
- Drop Table B.
- Rename Table A to Table B.
- Create Table A.
- 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
- Drop Table B. (Table B is renamed as $BIN$B) (Now what will happen to its trigger)
- Rename Table A to Table B.(Which trigger (X or Y) is Pointing to Table B NOW)
- 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?