Creating and using a trigger


A trigger is created by a CREATE statement. The syntax follows:

 CREATE TRIGGER name ON table [WITH ENCRYPTION] [FOR/AFTER/INSTEAD OF] [INSERT, UPDATE, DELETE] [NOT FOR REPLICATION] AS BEGIN ... END 

We will look at the two options for the type of trigger, AFTER and INSTEAD OF, in the Types of Triggers section. When naming a trigger, it is best to follow the naming standard of prefixing the name with tr_, followed by actions the trigger is for, like ins for insert, upd for update, del for delete, and lastly, the name of the table that the trigger is attached to. If we create more than one trigger for a table, for the same set of actions, which is possible with AFTER triggers, then we will suffix each trigger with other information to identify each one. If we had a trigger on the orders table, which will fire on an INSERT only, then it will be called tr_i_orders or tr_iorders. Many people prefer the first method, as it makes the associated trigger action clearer.

As a trigger fires on an action (unlike procedures, which are invoked), it is obvious that it is not possible to pass in any parameters. All of the information for a trigger has to come from table information. If we need to pass in some sort of parameter information, then prior to executing the triggering action, we need to create a temporary table that can then be referenced by the trigger.

There are two occasions when a trigger will not fire on a modification action:

  • Triggers fire on logged actions, such as an INSERT, when the action is placed in the transaction log. TRUNCATE TABLE statements are non-logged actions, which is one reason for TRUNCATE TABLE being faster than a DELETE with no WHERE statement, as a TRUNCATE TABLE will not fire any underlying triggers.

  • The second occasion is when we use WRITETEXT to write information to a TEXT data type column. This is also a non-logged action, and therefore cannot fire a trigger.

Code within triggers needs to be short. Any trigger that fires on a table modification will reside in the same transaction as the modification action that fired it. Therefore, the longer a trigger takes to run, the longer the lock on any rows or tables is held.

It is possible to reference any tables within the database in which the trigger resides, tables outside the containing database, or any temporary table created in the calling batch. Remember that every such action will degrade performance, and hence it is crucial to create optimal T-SQL code.

We will look at replication and triggers, but it will be useful to make the point about using the NOT FOR REPLICATION option in the trigger. If we code our trigger with this option, we are indicating that if the database is replicated, this trigger will not fire when the update coming into the table is from a replication action.

Important

When we set up replication within SQL Server, we can automatically transfer triggers from the publisher to the subscriber database. If the publisher is marked NOT FOR REPLICATION, and placed in the subscriber. If the trigger does not have this option, then the triggers will also be replicated without the option, which may not be what we want.

Although not a mandatory option, it is best to keep this in mind in case our company decides to replicate a production database to another location.

Note

We can encrypt a trigger for security, as with stored procedures, hence the WITH ENCRYPTION option in the syntax.

The Conceptual Tables within Triggers

When a trigger fires, there are two conceptual tables available for use to check data changes. For example, let's say we are updating a row in TableA in some database. SQL Server knows we have an update trigger on this table, so it takes an image of the row before the modification takes place, then allows the modification to occur, and finally takes an image of the row. This pre-modification image is placed into a table called deleted, as we delete the values in the old row. The post-modification row is placed into a table called inserted. Remember that these tables only exist within a trigger. A deletion will have a row in the deleted table and an insertion will have a row in the inserted table, per row modified.

We can demonstrate this in the following trigger:

 CREATE TRIGGER tr_upd_OrdersCount ON orders AFTER UPDATE AS BEGIN   DECLARE @Rc VARCHAR(20)   SELECT @Rc = CAST(COUNT(*) as VARCHAR(10)) FROM deleted   SET @Rc = 'Rows Updated ' + @Rc   RAISERROR(@Rc,1,1) END 

If we update the data with the following, we will see that Rows Updated as well as the COUNT for the UPDATE command comes to 5.

     UPDATE orders     SET RequiredDate = RequiredDate     WHERE CustomerId = 'VINET' 

The output of the UPDATE is:

 Msg 50000, Level 1, State 50000 Rows Updated 5 (5 row(s) affected) 

When working with multiple modifications, it is necessary to have a method to match the rows in the deleted table with the rows in the inserted table. Normal practice will be to use the primary key, providing that this gives unique rows, without which we may need to alter our underlying table.

These conceptual tables are held in memory, and hence are fast. If a trigger fires after a large number of row modifications, it is not possible to index these tables to speed up the trigger.

Triggers Fired on an Update

When a trigger fires on an UPDATE or INSERT action, it is possible to find out if a specific column value has been modified. If the IF UPDATE clause is used, SQL Server will compare the values in that column from the deleted table with those in the inserted table, to determine if a modification has taken place. If every column in the IF statement has been updated or inserted, then this will return a TRUE value, and the trigger will then drop in the relevant code block for the decision. This is faster than multiple IFELSE IF, where we will need one IF per column. Computed and TEXT data type columns cannot be placed in the UPDATE test.

There is another similar function called COLUMNS_UPDATED that returns a bitmask of the column number of every column that has also been modified. A bitmask is simple binary positioning for the columns in a table.

We can see the bitmask value against each column in the table:

Column in table

Bitmask value

First

1

Second

2

Third

4

Fourth

8

Fifth

16

Sixth

32

Seventh

64

Eighth

128

If we want to check if the second, fifth, and seventh columns have been updated, then add 2, 16, and 64 (result is 82), and the SQL statement will be IF COLUMNS_UPDATED(82). It will not matter if the other columns have also been updated, as this statement will only consider the three columns mentioned.

As we can see, we have mentioned the first eight columns only. What if we want to check if the ninth column has been updated? The value will not be 256 because we are going from one byte to two bytes, and COLUMNS_UPDATED() works on the bit settings of one byte only. Well, we can still do this, but we have to SUBSTRING that second byte from COLUMNS_UPDATED(), and then use the POWER function.

Code to test columns 4, 7, 10, and 12 is as follows:

     IF SUBSTRING(COLUMNS_UPDATED(),1,1) = POWER(2,4-1) + POWER(2,7-1)     AND SUBSTRING(COLUMNS_UPDATED(),2,1) = POWER(2,10-8-1) + POWER(2,12-8-1) 

This can be clarified as 24-1 + 27-1 (as we want to test column 4, and 20 is 1). In the second test, it is 210-8-1 + 212-8-1 (as the first eight were covered in the first SUBSTRING).

If a column is inserted in the middle of an existing table, then our bitmask tests will no longer be valid. The same reason for not coding SELECT * applies here – our related processes will no longer work. if the column order changes.




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