구글와이드(336x280)_상단 2개


(영문) The Trouble with Triggers, Part I DataBase(MS SQL)

The Trouble with Triggers, Part I
Part 1 of a three-part article on triggers in SQL Server. An overview of triggers in SQL Server: When to use them, how to debug them, and more.

hat is a trigger? How does it work? When should one use them, and how does one debug them? Last, what are the enhancements that were made in SQL Server version 7? Let's explore some of these issues.
What Is a Trigger?

A trigger is a piece of code that SQL Server executes in response to a data modification statement; that is, an insert, update, or delete. The statement to create a trigger is:
  
CREATE TRIGGER [owner.]trigger_name
ON [owner.]table_name
FOR {INSERT, UPDATE, DELETE}
[WITH ENCRYPTION]
AS sql_statements
Unlike rules or defaults that can be attached to more than one column by using sp_bindrule or sp_bindefault, a trigger can only be attached to one table.
Each table can only have one trigger for each of the three possible events (insert, update, or delete); that is, you can't have two triggers on a table responsible for handling a delete. A single trigger can handle from one to all of the events. In all, a table can have as few as zero triggers or as many as three.
When Is a Trigger Executed?

A trigger is implicitly part of the transaction that causes the data modification. However, SQL Server executes the trigger only after the table constraints have been checked.
Why Would I Want to Use One?

One of the most important uses for triggers used to be to enforce referential integrity. Correct me if I'm wrong, but SQL Server version 4.2 did not support referential integrity constraints such as foreign keys. Instead, you could create a trigger that, upon the insert of a record, looked for the corresponding value of the foreign key in the parent table. If the value wasn't found, the transaction could be rolled back.
However, even though we now have support for foreign keys, sometimes triggers come in handy. For example, consider a system that uses an account number as the primary key in a table and contains another table that stores the transactions for each account such as debits and credits. Suppose that on occasion the account number must be changed. You can't change the account number in the parent table (accounts) without first updating the value of the account number in the child table (transactions). A trigger is perfect for this sort of situation. (By the way, this situation is typically referred to as "cascading updates.") In an update trigger on the account table, you place code that checks for the value that will change and updates all the corresponding records in the transaction table.
Similarly, a trigger is handy for keeping summary tables up to date. Continuing with our account table as an example, imagine a system that has many transactions coming in throughout the day for various accounts. It is important at any time for the users of this system to know what is the total value of the debits and credits transacted throughout the day. Depending on the number of transactions and the response time required, summarizing the information might take too long from the time when the user requests it. Instead, as each debit or credit is inserted into the transaction table, a trigger can be executed that contains code to update a summary table with the value of the transaction just entered.
Triggers are also useful for keeping audit logs of changes to sensitive data. The same trigger that updates the transaction table with the new value of the account can also insert a record into an audit table with the old account number, new account number, time changed, and the user name of the person that changed it.
In the future I'll compare triggers to stored procedures and discuss how to code them. Look for examples for each of the situations we've discussed.
Page 1 of 1

null



바보들의 영문법 카페(클릭!!)

오늘의 메모....

시사평론-정론직필 다음 카페
http://cafe.daum.net/sisa-1

바보들의 영문법 다음 카페
http://cafe.daum.net/babo-edu/

티스토리 내 블로그
http://earthly.tistory.com/

내 블로그에 있는 모든 글들과 자료에 대한 펌과 링크는 무제한 허용됩니다.
(단, 내 블로그에 덧글쓰기가 차단된 자들에게는 펌, 트랙백, 핑백 등이 일체 허용되지 않음.)

그리고 내 블로그 최근글 목록을 제목별로 보시려면....
바로 아래에 있는 이전글 목록의 최근달을 클릭하시면 됩니다.
그러면 제목을 보고 편하게 글을 골라 보실 수 있습니다.

그리고 내 블로그내 글을 검색하시려면 아래 검색버튼을 이용하시면 됩니다.


가가챗창

flag_Visitors

free counters