SQL Server/T-SQL Tutorial/System Settings/xp sendmail

sends an email when an insert or delete occurs on the discounts table.

   <source lang="sql">

4> 5> 6> CREATE TABLE discounts( 7> discounttype varchar(40) NOT NULL, 8> stor_id char(4) NULL , 9> lowqty smallint NULL, 10> highqty smallint NULL, 11> discount dec(4,2) NOT NULL 12> ) 13> GO 1> 2> insert discounts values("Initial Customer", NULL, NULL, NULL, 10.5) 3> insert discounts values("Volume Discount", NULL, 100, 1000, 6.7) 4> insert discounts values("Customer Discount", "8042", NULL, NULL, 5.0) 5> GO (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE TRIGGER trDiscounts_InsDel ON discounts 3> FOR INSERT, DELETE 4> AS 5> DECLARE @intRowCount INTEGER, 6> @chvMsg VARCHAR(255) 7> SELECT @intRowCount = @@RowCount 8> SELECT @chvMsg = CONVERT(VARCHAR(10), @intRowCount ) + " record(s) were " 9> SELECT COUNT(*) FROM inserted 10> IF @@error <> 0 11> SELECT @chvMsg = @chvMsg + " deleted from the discounts table." 12> ELSE 13> SELECT @chvMsg = @chvMsg + " inserted into the discounts table." 14> EXEC master..xp_sendmail "Colleen", @chvMsg 15> RETURN 16> GO 1> 2> drop trigger trDiscounts_InsDel; 3> GO 1> 2> 3> drop table discounts; 4> GO</source>