Figure 1:Accessing the triggers icon
If you are building a web application, many of the common database scenarios can be managed more easily through the use of triggers. A trigger is a stored procedure, but it is a special kind of stored procedure. You don’t call a trigger explicitly, like you do a regular stored procedure. A trigger is executed as a result of some other action within your database. This can be a database insert, update, or delete statement. These are the action queries of SQL, and each one can cause a trigger to execute. There is no interface in a web tool such as Dreamweaver MX to work with triggers, but if you know how to create and manage triggers, your web application can be
Triggers have a variety of uses, but they are often misused as well. A good SQL Server programmer will know when to use a trigger and when something else might be more appropriate. Also, it is wise to limit the use of the trigger within the trigger itself, by testing values and only performing actions if they are required. The best trigger is a fast trigger and one that doesn’t do anything if it’s not needed. For example, if you have a trigger that updates a total sales figure for a specific product in a given day, you can skip the calculation if no products of that type were sold. Knowing how to minimize the number of database transactions will make your triggers (and your databases) more efficient.
Some of the uses of triggers are:
Greater flexibility for referential integrity. In general, you will want to use declarative referential integrity (built-in) for managing the integrity of your data, but there are times when a trigger can give you specific advantage, such as sending a custom error message to the end user.
Responding to specific events. There are many times when you may want to have an event fire upon a successful action in your database, such as sending an email after a database insert, or marking a past due column in a related table “paid” after a payment is inserted.
Enforcing business rules. You may have things that are specific to your application that can be addressed with a trigger, such as checking to make sure that a minimum order of $20 was made before using a UPS shipping cost on a specific order.
Cascade update and delete. If you have a products table in your database and you have to delete a specific supplier, you may want to use a trigger to delete all products in the database that were provided by that supplier based on the condition that no inventory remains for that product. Conditional logic is also something that a trigger can do that can’t be done with standard declarative referential integrity.
Working across databases. Triggers can access other databases on your server, so if you have a related table in another database, you can enforce integrity between tables in different databases. An example of this might be a second products database that is separate from your e-store database products table.
As you can see, triggers can do a lot of things, most of which can be done by other methods as well. Sometimes it is a difficult decision to turn some aspect of your application into a trigger, but if you have some trigger experience under your belt you’ll be able to make an informed decision.
The first thing you’ll notice about a trigger is that it looks almost like a stored procedure. Triggers are written with Transact SQL just like stored procedures, but there are some important differences. For one thing, a trigger isn’t called – it responds to an action. Since you don’t call a trigger, you also can’t pass values to a trigger. Triggers are generally interested only in the affected rows, so there are special tables set aside for use in triggers – the inserted and deleted tables. These two virtual tables provide the information needed by the trigger to act on the data.
The inserted table resides in memory when an insert occurs on a table. The table rows that were affected by the insert are stored in this table. Similarly, the deleted table resides in memory when a delete occurs on a table. The table rows that were affected by the delete are stored in this table. When an update to the database occurs, rows are stored in both the inserted and the deleted tables, because an update is essentially the action of deleting an old row and inserting a new row. It should also be noted that the inserted and deleted tables are only available to triggers and not to stored procedures, and only available while the trigger is executing.
Since you can’t pass values to a trigger, you will also rely heavily on built-in system functions when building your triggers. The two that are frequently used in triggers are @@ROWCOUNT and @@IDENTITY. @@ROWCOUNT will contain the integer number of how many rows were affected by the action. A test of @@ROWCOUNT should be in most triggers, since if the value returns zero, no rows were affected by the insert, update, or delete:
IF @@ROWCOUNT = 0
RETURN
Also, you can use the @@ROWCOUNT for to check if the number of affected rows is what you were expecting. If an order that should have affected one row shows a value of 100, you know there is a problem. Another use of this value is to allow your code to do different things based on how many rows are affected. For example, if only one row is affected, you may have no reason to go into a loop, whereas if more than one row is affected, you may need to code a loop. Providing alternate code depending on how many rows are returned can speed up the execution of the trigger.
The @@IDENTITY variable will contain the value of the identity column of the last inserted row. This can get a little messy if your trigger is the result of several inserts, or inserts across multiple tables, so it’s wise to have a well-thought out plan as to where your @@IDENTITY values are retrieved. For a simple one-table, one-row insert, you can safely call it from your trigger, but for more complex operations it might be wise to work with your identity values within the stored procedures themselves.
You can create a trigger with a CREATE TRIGGER statement. A trigger is applied directly to a table, so you need to specify the table and the action that the trigger is associated with:
CREATE TRIGGER tri_SendOrder
ON Payments
FOR INSERT
AS
IF @@ROWCOUNT = 0
RETURN
DECLARE @orderid int
SELECT @orderid = orderid FROM inserted
EXEC dbo.spOrderPlaced @orderid
This trigger is used when the Transactions table is inserted to. When using triggers, it is important to understand exactly when the trigger will fire. For example, when inserting an order into your database, a row is typically inserted into the Orders table first, but there may be subsequent inserts performed on an OrderDetails table. If you were to apply the trigger to the Orders table, the Order Details table hasn’t been updated yet. Here we are sending an email upon the insert to the Payments table, which is the last table affected by the transaction.
In this example, a delete trigger is applied to the Products table to test for low stock levels on updates to the table:
CREATE TRIGGER tri_CheckInventory
ON Products
FOR UPDATE
AS
IF @@ROWCOUNT = 0
RETURN
DECLARE @LowStock int
DECLARE @ProductID int
SELECT @ProductID = productid FROM deleted
SELECT @LowStock = UnitsInStock FROM Products
WHERE ProductID = @ProductID
IF @LowStock < 5
BEGIN
EXEC spAlertSupplier @ProductID
END
The trigger is set to go off whenever an update is made to the products table. First, the @@ROWCOUNT system function is checked to see whether any rows have been affected by the update. If the number isn’t zero, two variables are declared: @LowStock and @ProductID. Then, the memory-resident deleted table is queried for the ProductID, and then the UnitsInStock column is retrieved. If that number is less than 5, the stored procedure spAlertSupplier is fired.
This is a new type of trigger for SQL Server 2000, and is especially useful because it can also be applied to views. This type of trigger, unlike the standard for triggers shown above, is called instead of calling the action that is specified in the trigger. For example, an instead of trigger applied to the products table on the insert action will execute before any insert actually occurs. What is unique is that the inserted and deleted tables are available in full to the instead of trigger, even though no action has been performed on the table. Inside of that instead of trigger you can either follow through with the action, or do it conditionally based on some other criteria.
For example, suppose you want to insert a user into a table, but want to make sure that his social security number isn’t duplicated in the table. One option is to make the social security number a unique value in the table, but maybe everybody doesn’t need a social security number in your table. Since you can’t have null values if you want the values to be unique in the table, you can use an instead of trigger to test the table first for a duplicate value for social security numbers:
CREATE TRIGGER tri_CheckSSN
ON Employees
INSTEAD OF INSERT
AS
DECLARE @test int
SELECT @test = count(SSN) FROM inserted where
SSN in (SELECT SSN FROM Employees)
IF @test = 0
BEGIN
INSERT INTO Employees (SSN, firstname, lastname)
(SELECT SSN, Firstname, lastname from inserted)
END
In this example, the insert will only take place if the social security number (SSN) isn’t already in the table.
Another use for an instead of trigger is to bypass some of the built-in constraints that a table may have. For example, if your table has an identity column that is the primary key, you can’t insert a duplicate primary key into the table. What if you have some data that you want to insert, but some of the data may exist already. The rows that already exist would have to be updated, but the rows that don’t exist have to be inserted. You could write a trigger that would take the place of a standard insert, and update those rows that already exist. In this way, you can perform simple inserts on the table with your data that may or may not contain some duplicate data.
We mentioned that instead of triggers can be applied to views as well. This is a new feature in SQL Server 2000, and has several uses. For example, suppose your view has a concatenated Name column that consists of a firstname + ' ' + lastname. Your trigger could pull a name field apart into its component parts and insert the value into the two columns.
Frequently you will have a situation where you want all changes to the database to be logged in a separate table. This can be done easily with a trigger. For example, you may have an orders table that is accessed by several people – salepeople, customer service, production, and accounting. Each person is making changes to the data in the tables, but you want to have a trail of changes logged separately from the data. By adding a trigger to the orders table, you can log each row as it is changed in a separate table – even in a separate database – so that the columns that are updated are logged along with the user id number of the person making the change and the date/time of the change. A trigger such as the following can insert a row into your audit table that is an exact duplicate of the row that was just updated in the orders table:
CREATE TRIGGER tri_Orders2
ON Orders FOR Insert, Update, Delete
AS
If @@ROWCOUNT = 0
RETURN
INSERT Orders2 SELECT * FROM inserted
INSERT Orders2 SELECT * FROM deleted
After triggers are nothing new – they are the standard triggers that get called after an action occurs on the table. SQL Server 2000 differentiates between after triggers and instead of triggers by using the after keyword, as in this example:
CREATE TRIGGER tri_deleteOrders
ON Orders
AFTER Delete
AS
IF @@ROWCOUNT = 1
BEGIN
--do cascade delete on one order
END
ELSE
BEGIN
-- do cascade delete on multiple orders
END
This could also have been written as:
CREATE TRIGGER tri_deleteOrders
ON Orders
For Delete
AS
-- etc.
The for keyword is synonymous with after, but it’s deprecated and the after keyword should be used with SQL Server 2000 and future versions.
After a trigger is created, you have to use the ALTER keyword if you need to make changes to the trigger:
ALTER TRIGGER tri_CheckSSN
ON Employees
INSTEAD OF INSERT,UPDATE
AS
DECLARE @test int
SELECT @test = count(SSN) FROM inserted where
SSN in (SELECT SSN FROM Employees)
IF @test = 0
BEGIN
INSERT INTO Employees (SSN, firstname, lastname)
(SELECT SSN, Firstname, lastname from inserted)
END
Here, we’ve changed the original tri_CheckSSN trigger to work with updates as well as inserts.
To remove a trigger, use the DROP keyword:
DROP TRIGGER tri_CheckSSN
You can create, alter, and drop triggers directly from the Query Analyzer, or by using the visual interface of the Enterprise Manager. Adding a trigger to a SQL Server table is a little different than adding other database objects such as tables and views, however. The triggers exist on the individual tables, so you can access the triggers that apply to an individual table from the Enterprise Manager by right-clicking the table in the Enterprise Manager and clicking Design Table. The triggers icon is available from Design view, shown here:
Figure 1:Accessing the triggers icon
From the trigger interface, you can create a new trigger and test the syntax:
Figure 2:Creating a new trigger from the design view of the table interface.
Since you can have more than one after trigger on a table (you can only have one instead of trigger), the triggers are available from the dropdown list in the interface, along with the option to add a new trigger:
Figure 3:Accessing existing triggers.
Triggers are accessible from the Query Analyzer as well. In SQL Server 2000, the left pane is the Object Browser, and contains lists of all SQL Server objects. Triggers appear under the table objects, and can be created, altered, or dropped from the Query Analyzer:
Figure 4:Creating, altering, or dropping a trigger from the Query Analyzer
You can then work with the trigger using the Query Analyzer and all of its functionality:
Figure 5:Editing a trigger in the Query Analyzer
You can also create, alter, and drop triggers from an Access interface. If you are using Access as a front end, you can right-click on a table to access the triggers that are applied to the table, or to add new triggers:
Figure 6:Accessing triggers from MS Access 2000
If there are already triggers applied, you will get a drop-down list of them, which you can then alter or drop. If there are no triggers, you will get an empty list to which you can add a new trigger:
Figure 7:Dropdown list of triggers on the Orders table
You can modify triggers from within Access as well:
Figure 8: Modifying a trigger from within Access
Working with triggers is one of those topics that seem more difficult than it really is. If you have worked with stored procedures, working with triggers should be second nature to you. The biggest differences that you have to consider are that you don’t call a trigger, and working with the inserted and deleted tables. Once you understand these two things, you should be able to write your own triggers when necessary, and know when a trigger is going to be the right task for the job at hand.