The Examples

Let’s now look at a trigger from an actual deployment plan in which the business owner required being alerted to unusual sales activity:

 /* Script Name: Something Fishy Description: Trigger to report unusually high sales Usage: Placed on customers.dbo.orders for canship Return Code: N/A Author: Jeffrey R.Shapiro Version: 1.00 Date Created: 9/25/2005 Revision History: */ IF EXISTS (SELECT name FROM sysobjects    WHERE name=N'Fishy'    AND type='TR')          DROP TRIGGER Fishy GO CREATE TRIGGER Fishy ON customers.dbo.orders FOR UPDATE AS BEGIN   DECLARE @Qty int    SELECT @Qty=(SELECT SUM(CanShip) FROM Customers.dbo.Orders)    IF @Qty > 10000      BEGIN         EXEC DotNetMailer.Send 'JeffreyS',         'Business cannot be this good,         tere is something fishy going on'      RETURN     END  END GO

As a second example, make use of the RAISERROR message function. As discussed in Chapter 11, the RAISERROR is similar to the message dialog facility in the Win32 API (and wrapped by the .NET Framework), which has been exposed (or wrapped) by every major language capable of producing Windows applications. If you look at RAISERROR in Chapter 10 or Books Online you will see that it can take first a message string or a message ID that can get returned to the client. You can also add as parameters severity levels and replacement parameters. The replacement parameter placeholder in the function is that old familiar %d visiting T-SQL from the C language, as demonstrated next.

In the following example the trigger code tests for the number of items scheduled for shipping, and if it is over 10,000 units, an error message or alert is committed to the Windows Server 2003 application log by using the WITH LOG option. In addition I have also added the SET NOCOUNT line, which will ensure that the trigger does not return the “n rows affected” message to the user. The trigger code will not modify any rows, but SET NOCOUNT will suppress anything that might slip in when later versions of this trigger are installed.

 ALTER TRIGGER Fishy ON customers.dbo.orders FOR UPDATE AS BEGIN SET NOCOUNT ON DECLARE @Qty int    SELECT @Qty=(SELECT SUM(CanShip) FROM Customers.dbo.Orders)    IF @Qty > 10000      BEGIN        RAISERROR('Total CanShip items has exceeded %d.', 18, 1, @Qty)         WITH LOG        RETURN      END END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO