Bad Practice


Firstly, don't substitute a constraint with a trigger, as we covered much of this in the Triggers Versus Constraints section. Within a database solution, there are different levels of integrity, and at each level, there is an appropriate method of enforcing that integrity.

  • Entity Integrity – No row has a NULL in the primary key, all tables have primary keys, and no two rows have the same primary key value. The solution to enforcing this is to build a primary key as a UNIQUE constraint, and no column that forms the primary key allows NULLs.

  • Domain Integrity – When all values in a specific column are valid, whether this is a range of values or specific values, the solution is to place a constraint on a specific column, known as a CHECK constraint

  • Referential Integrity – Where all foreign keys are valid, the solution is to place foreign key constraints on the relevant tables.

These solutions for each integrity type will cover most scenarios. We will consider using a trigger only when they are not sufficient. It is unusual, if not rare, to run into a different database solution, and find that triggers have replaced these natural methods of enforcing integrity. Although overheads of using triggers are low normally, we create a performance overhead if we use a trigger when one of these options would have been sufficient. Therefore, any code violating these recommendations should be removed and replaced.

There are other problems with a ROLLBACK in a trigger too if we use cursors and nested transactions; we looked at some of the problems in Chapter 3.

There are no restrictions on the T-SQL code placed within a trigger. Therefore, it is possible to place a SELECT statement within code to return a rowset, which is an unusual action, since we should return data within a stored procedure when we need to.




SQL Server 2000 Stored Procedures Handbook
SQL Server 2000 Stored Procedures Handbook (Experts Voice)
ISBN: 1590592875
EAN: 2147483647
Year: 2005
Pages: 100

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net