If T2 is derived data based on the contents of T1, then you may want to replace T2 with a materialized view. If T2 is rarely accessed, and quick to derive, then you may just want to use a view.
On the application side, if you stop maintaining table T2, then the values it gets will become increasing incorrect. This can be partially corrected by recalculating the derived values and updating T2 with the new values.
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.
Best Answer
The errors generated by RAISERROR operate the same as errors generated by the Database Engine code. The values specified by
RAISERRORare reported by theERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE, and @@ERRORsystem functions. WhenRAISERRORis run with a severity of 11 or higher in aTRYblock, it transfers control to the associatedCATCHblock. The error is returned to the caller ifRAISERRORis run:Outside the scope of any TRY block.
With a severity of 10 or lower in a TRY block.
With a severity of 20 or higher that terminates the database connection.
If you are in trigger context, all errors terminate the batch and roll back the transaction on the spot.
When it comes to error handling in SQL Server, no rule is valid without an exception. Errors you raise yourself with
RAISERRORdo not abort the batch, not even in trigger contextThere is one more way that a trigger can terminate the batch. This happens if
@@trancountis 0 when the trigger exits. A trigger always executes in the context of a transaction, since even if there is no multi-statement transaction in progress eachINSERT, UPDATE and DELETEstatement is its own transaction in SQL Server, and the trigger is part of that transaction. Doc