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
There are other problems with a
in a trigger too if we use
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.
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
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
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
However, in today's web-enabled world, two significant things have
The quality of the data that we are collecting is getting both more
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
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.