In this chapter, we have examined the different types of UDFs and
We then looked at calling UDFs and scalar valued functions. Our discussions also included the Table data type and the advantages and pitfalls in using it. We also looked at the SQL Server's in-built functions that return Table data types.
We then discussed schema bindings and finally went on to examine the similarities between stored procedures and UDFs. UDFs share a number of similarities with stored procedures, but as we saw there are a number of restrictions too. None is more
As we approach the conclusion of this book, we come to an important topic in the specialized stored procedure arena – triggers. Triggers are best used for enforcing business rules as well as performing validation or data modifications, when other
We will see that triggers are
Creating audit records and reflecting changes to crucial business tables
Validating of changes against a set of business rules coded in T-SQL
In this chapter, we will cover:
What a trigger is and why it exists
How to create a trigger and its different types
Good and bad programming practices
A trigger is a specialized stored procedure which fires automatically when an alteration occurs to an underlying table due to an action like deleting, inserting, or updating of a row, or a batch of rows, within the single table that the stored procedure is linked with, or
Triggers can be assigned to tables or views. However, although there are two types of triggers, INSTEAD OF and AFTER , as we will see later in the Types of Triggers section, only one type of trigger can be assigned to views. Briefly, an INSTEAD OF trigger is the one that is usually associated with a view, and runs on an UPDATE action placed on that view. An AFTER trigger fires after a modification action has occurred.
It is possible to have more than one trigger firing for the same action, if they are
triggers. So it is quite possible and
From a performance viewpoint, the fewer the triggers, the better, as we will invoke less processes. Therefore, do not think that having one trigger per action to make things modular will not incur performance degradation. A trigger's main overhead is referencing either two specialist tables that exist in triggers – deleted and inserted or other tables for business rules. Modularizing triggers to make the whole process easier to understand will incur multiple references to these tables, and hence a greater overhead.
It is not possible to create a trigger to fire when a data modification occurs in two or more tables. A trigger can be associated only with a single table.
Care has to be taken in areas such as
, when loading data into tables, as we may wish to switch off the firing of the triggers. DTS (Data Transformation Services) is a tool within SQL Server to allow bulk loading of data, data manipulation, and many other useful processes
Disabling the trigger is the preferred option;
Care must be taken to ensure that we perform the
only when there are no other
One way to ensure this is to change the database Access property through the Restrict Access clause, to a Single User in the Properties page of the desired database, in the Enterprise Manager.
Later, in the Replication section, we will also look at triggers in places where replication of a database is involved.
Triggers are not designed for returning any information. No parameters can be passed to them, and returning a rowset of information will cause