Putting the tools to work: Programming with SQL server 2005 express

Now that you have this information, you can take action if necessary: -- If there isn’t enough inventory, then order some more IF (@quantity_on_hand + @part_request_count > @minimum_number) INSERT INTO reorder VALUES (@part_number, @minimum_number) -- If it’s a white elephant, tell the buyer the good news IF @bad_item > 0 exec sqlexpress.msdb.dbo.sp_send_dbmail @recipients = @decision_maker_email, @subject = ‘Just sold some of the white elephants!’ , @body = ‘Check the sales activity report’; This trigger closes out by starting the reorder process when there is insufficient inventory. Finally, if a customer was foolish enough to buy one of the problem products, the trigger executes a system stored procedure to send a congratulatory e-mail to the person who made the decision to stock the troubled product. 5. Run the Transact-SQL that creates the trigger. This trigger is fairly basic; you can write much more powerful code that encodes much more sophisticated business logic, invokes other triggers, and launches stored procedures. Here’s an example of a DDL trigger that’s designed to block people from creating new indexes without first checking with you: CREATE TRIGGER no_new_indexes ON DATABASE FOR CREATE_INDEX AS PRINT ‘Please call your friendly neighborhood DBA before creating a new index’ ROLLBACK Now, if users try to sneak a new index into the database, your new trigger stops them cold in their tracks: Please call your friendly neighborhood DBA before creating a new index .Net SqlClient Data Provider: Msg 3609, Level 16, State 2, Line 1 The transaction ended in the trigger. The batch has been aborted. You can use DDL triggers to safeguard your entire SQL Server 2005 Express installation.