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 NULL s.
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.

Summary

Triggers are excellent tools for a developer to have when building a database solution. Care has to be taken that the right trigger and the right code is placed within a trigger though. But the skill lies in knowing when to have cascading triggers, whether to work with transactions within a trigger, what to do when replication is involved, and what isolation levels to have.

Once a database solution is in place with working triggers, any alteration to that database environment, such as replication, will require a great deal of thought and planning. Testing should be intensive with an emphasis placed on areas such as performance. We may need to alter what happens within triggers, and maybe even split an AFTER trigger on one table action into two, with only one working on the replication.

Chapter 8: Security

By now you will have realized that using stored procedures is the best way for making data available from an SQL database. In the earlier chapters, we have seen that there are several reasons for preferring this approach. One such major reason is security. In this chapter will examine this aspect of SQL Server stored procedures.

We will first analyze security threats and the core security requirement of SQL Server – allowing data access only to explicitly authorized users. Then, we will look at the proper implementation of stored procedures for adequately countering these threats. Finally, we will examine some general methods for securing our SQL Server implementation; we will be giving special emphasis on SQL Server stored procedures and the system stored procedures that Microsoft ships with SQL Server.

Increased Need for Security

Whenever we deal with a threat, we not only analyze the degree of the vulnerability, but also the gain-to-loss ratio that a hacker has for taking advantage of that vulnerability. If the gains are high and the losses are perceived to be low, the hacker is more likely to hack the system. Note that, the gains can be subjective or abstract. Until a few years ago, the vast majority of SQL Server applications were client-server applications, which were totally contained within the firewall of an organization. In such a scenario, the potential hacker was an employee of the company itself. The gains in successfully hacking the application and not getting caught were significantly counter-balanced by the perceived loss of losing their job and the fear of being prosecuted.

However, in today's web-enabled world, two significant things have happened :

The quality of the data that we are collecting is getting both more valuable and more liquid (for example, credit card numbers ).
Secondly, with the increasing penetration of the Internet, the potential hacker is sitting miles away and has no direct relationship with the organization. You can't fire them, since they usually aren't an employee or contractor. Getting their name , address, and phone number to turn them over to a law enforcement authority is much more difficult.

As a result, the gains for the hackers have gone up considerably while the perceived losses have gone down dramatically. In such a scenario, hacking is bound to be more attractive to a crooked conscience.

Buy on amazon.com >>

Bain T., Davidson L., Dewson R.

<< Previous page

Table of contents

Next page >>