I agree with that generally, but there are also times when a trigger is a very useful thing to use to undo some of the damage done by someone's earlier "job creation scheme".

Way beack when I had the problem of converting a heap of rubbish into a viable system. One major component was a vast quantity of C++ which was full of embedded SQL, often that SQL being generated dynamically using C++ string manipulation. One particular problem was a table that grew like topsy, although it should have been very small. This table was declare like this (names changed to protect the guilty):

CREATE TABLE sometable (

ID int identity PRIMARY key

, thingID int REFERENCES thing(ID)

, otherthingID varchar(8) REFERENCES otherthing(ID)

, and several columns more, irrelevant to this comment

) ;

It turned out that the C++ part was doing a lot of inserts. Although thingID and otherthingID weren't declared NOT NULL, they were never observed to be NULL, and the logic of what the application was supposed to present to end users was that (thingID,otherthingID) was a natural key despite there being no unique constraint. The ID column couldn't easilty be eliminated, because the last value inserted was carried about and used in the C++ code (read by "select top 1 ID from something order by ID desc" tacked on to the end of the embedded SQL string whenver the C++ thought it was creating a new row), unsurprisingly given the quality of the rest of the embedded SQL), and rewriting the app was going to take a long time, longer than we could live with this problem.

So the short term problem was how to prevent the table from growing too big and causing performance problems without eliminating the ID column. The natural thing to do was try adding not null restrictions to the two columns of the natural key and a unique constraint and see what happened; what happened was chaos; the unique constraint caused the application to fail in a number of ways (it felt like about a million differnt ways, but of course wasn't); the NOT NULL constraints did no harm, so we tried using them plus an "instead of insert" trigger which deleted all rows which had the same thingID and otherthingID as the row to be inserted before inserting it. That worked - the app didn't complain, and about a dozen known functional errors disappeared from the outstanding bug list (the inserts should have been updates, but we couldn't do that because the highest ID was used in generating some more embedded SQL in that dreadful C++).

Of course we did rewrite that C++ eventually; but first we changed the rest of the app (mostly writen in JS, and with no string manipulation generating SQL) so that it didn't use the C++ component except where essential (the previous developers' job creation project had decided to route all calls to this database through the C++ component, which was incredibly stupid but sure would have guaranteed them lots of work if I hadn't been pulled in to trouble-shoot the db stuff) so there was a lot less functionality needed in C++.