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

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

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