I don’t have a date for this but we’ll start making progress on this. When we fix this in SqlManagementObjects then it will show up in SSMS, mssql-scripter, and SQL ops studio.
5 votesSQlpro commented
This is the most stupid idea ever. RDBMS vendors have all came to triggers against the CREATE ASSERTION statement for performances reasons...
Let me tell you why with an example.
You have two tables, one with customers wich are enterprise that have emitted an order and the other with prospectives that are also enterprise but had never ordered
On common attribute is the legal state tax id.
The businees rule il that you must never have the same legal state tax id in both two table.
That is a quite simple ASSERTION, wich can be write in pure ISO normative SQL language, as :
CREATE ASSERTION unique_state_tax_id
CHECK NOT EXISTS(SELECT *
FROM customers AS c
JOIN prospectives AS p
ON c.state_tax_id = p.state_tax_id)
Assuming both table have only 1000 rows and no indexes are on this join, the treatment must read 1 million rows evrey time a INSERT, DELETE or UPDATE occur (even when it is embedded in a MERGE statement).
With a couple of triggers, you can choose the events that are really needed. Validating this assertion in a case of DELETE is particlarely stupid.
In a trigger you can also reduce the amount of rows to check by using the pseudo tables INSERTED or DELETED.
For one row INSERTED, the treatment is now reduced to 1000 rows to verify this assertion (that's only 1000 times better... !).
An finally the UPDATE() function into the scope of the trigger can decide if the check must be done or not. For instance, if an UPDATE occur and modify the name of a company, there is no need to check the assertion.
For all theeses reasons, ASSERTION has not be implemented in the most common databases to preserve good performances
Whith such a request, you try to kill the performances...
16 votesSQlpro shared this idea ·