Mega Code Archive

 

Categories / MSSQL Tutorial / System Settings

 

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