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

trigger

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 :

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
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?

Best Answer

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.

Related Solutions

SQL Server – Launch Python Script from Insert Trigger

Don't make your user transaction wait for the (hopefully!) successful completion of the Python script. Your entire transaction sits there and waits for this external process to run, try to send mail, etc. I doubt the e-mail really has to go out that instant - especially given you can't control any delays it has as it gets routed to the recipient's inbox anyway. Why not just run the process more frequently, if timing is so important?

Please give this tip a look-through.

If you really, really, really want to do this the wrong way, you can just enable xp_cmdshell and fire away.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

Now, assuming the user has access to xp_cmdshell and/or the SQL Server service account can see the folder where the python script is stored, you should be able to do this from within your trigger:

EXEC master..xp_cmdshell N'C:\Python27\python.exe C:\source\NotifyAgents.py';

As an aside, you should state in your question that you aware that this is a very bad thing^TM, but you are not concerned with that, for whatever reason. I still don't think you're going to get as real time as you expect, even if you do fire this from the trigger. Have you considered database mail instead of python?

PostgreSQL – INSTEAD OF Trigger Not Working When Inserting into View

You have a trigger INSTEAD OF DELETE in your code, but none INSTEAD OF INSERT. Create that, too.

You can make that a trigger INSTEAD OF INSERT OR DELETE, but I'd suggest separate trigger functions for INSERT and DELETE and separate triggers. Simplifies the function code (no more need for IF TG_OP = etc.).

Also consider this quote from the manual:

A row-level INSTEAD OF trigger should either return NULL to indicate that it did not modify any data from the view's underlying base tables, or it should return the view row that was passed in (the NEW row for INSERT and UPDATE operations, or the OLD row for DELETE operations). A nonnull return value is used to signal that the trigger performed the necessary data modifications in the view. This will cause the count of the number of rows affected by the command to be incremented. For INSERT and UPDATE operations only, the trigger may modify the NEW row before returning it. This will change the data returned by INSERT RETURNING or UPDATE RETURNING, and is useful when the view will not show exactly the same data that was provided.

Best Answer

Related Solutions

SQL Server – Launch Python Script from Insert Trigger

PostgreSQL – INSTEAD OF Trigger Not Working When Inserting into View

Related Question