sends an email when an insert or delete occurs on the discounts table. : xp_sendmail « System Settings « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. System Settings
3. xp_sendmail

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