What is a Trigger?


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 potentially multiple tables if the trigger is linked to a view. To clarify the statement of ‘batch of rows’– if we have an UPDATE statement that modifies several rows, for example, then the trigger will not fire for each row updated, but only once when the batch completes. If we needed to fire the trigger for each row being updated, then we would need to construct some sort of loop processing within our T-SQL to do this.

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 AFTER triggers. So it is quite possible and perfectly valid for two or more separate triggers to fire on the same table modification action. This will allow separate processing to take place, rather than having different underlying functionalities in the same procedure. It is also possible to define the order in which triggers will fire. Both these areas are covered in detail, in the Types of Triggers section.

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.

Note

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 DTS or BCP, 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 surrounding data. BCP is the predecessor of DTS, and is a simple prompt-based utility for bulk loading from a source to SQL Server. Dropping the trigger from the database, or using the DISABLE TRIGGER option of the ALTER TABLE statement, may be needed in such cases. We can then complete the bulk loading and reapply the trigger. If the trigger remains in place, and we bulk load a number of rows, then a trigger could be fired once for each row being placed into SQL Server, and hence it should remain only if we need this. For example, loading 100,000 rows will slow down a normally fast process. If a trigger exists as part of auditing, then we may remove it.

Note

Disabling the trigger is the preferred option; otherwise, we will need to recreate the trigger once it has been dropped.

Important

Care must be taken to ensure that we perform the DROP or DISABLE only when there are no other tasks being performed at that time. If we drop the trigger when a valid task is running, then we may corrupt the data, especially if the trigger is performing a business function. Triggers should be dropped only when they will not be invoked.

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.

click to expand

Later, in the Replication section, we will also look at triggers in places where replication of a database is involved.

Note

Triggers are not designed for returning any information. No parameters can be passed to them, and returning a rowset of information will cause logistical problems in the invoking procedure. If you are concerned that a rowset may be returned, then use the SET NOCOUNT statement at the start of the trigger to ensure that no rows are returned.




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