tailieunhanh - Microsoft SQL Server 2005 Express Edition for Dummies phần 7

Hãy tưởng tượng rằng cơ sở dữ liệu của bạn bao gồm các dữ liệu được lưu trữ trong cha mẹ (tiêu đề) và con (chi tiết) bảng. Hơn nữa, giả sử rằng bạn giữ một tổng hoạt động của thông tin trong bảng cha về các bản ghi trong bảng con. Một ví dụ là thông tin chi tiết khách hàng (tên, địa chỉ, tài chính tóm tắt) trong bảng cha, | Part V 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 @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. __Chapter 15 Understanding Triggers 237 Invoking triggers Because the SQL Server 2005 Express database engine monitors and runs triggers you do very little to make this process happen. In fact all that needs to .