I'm currently studying for 70-229 and in reading one of my books, Professional SQL Server 2000 Database Design, the author states that triggers are better than stored procedures for dealing with inter-row or inter-table dependencies.

I'm trying to find out why that is. Can anyone help with this question?

Thanks,

Susan

http://ix.1sound.com/dbagirl
My blog for studying for MS 70-229 (Notes and Links)

Report this post to a moderator

When enforcing business rules, SQL server provides two primary mechanisms for that: constraints and triggers. Triggers are different from SP's in that they are commands executed when a certain insert, update, or delete actions happens to a table or view. Triggers are not executed by users, instead, they are executed automatically when a certain action happens.

Stored Procedures, on the other hand, are a set of statements compiled into a batch and executed together by a user. You can have tens of hundereds of actions happening in one SP. Stored procedures are usefull because they reduce network traffic since they are executed on the server. SP's can be modified independently of an application and can be used as a security mechanisim. That is so becuase users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly.

Now, lemme get back 2 ur question. Inter-row and Inter-Table dependecies are actions happening in tables or between tables. Triggers are the best option to use in such cases.

For Example, You want to flag a product in your products table "Out-of-Stock" when you the number of products in stock is equal to zero. And you might want to place an order and add a record in the "VendorOrders" table automatically. All this action must happen without user interaction. Now, you can use SP's or you can even execute the statements directly from your application. But this is not a good way of doing it. This is where triggers are much better, more reliable, and more secure than SP's or batch-statements.

I hope your not confused
cheers,
2lazybutsmart

__________________
Crime is Common, Logic is Rare.
TechExams.net moderator

Report this post to a moderator

Thanks 2lazybutsmart!

Triggers would also be faster as well as more secure, wouldn't they?

Best,

Susan

http://ix.1sound.com/dbagirl
My blog for studying for MS 70-229 (Notes and Links)

__________________
Best,

Susan
My Weblog about studying for MS 70-229 (Notes and Links)
http://ix.1sound.com/dbagirl

Report this post to a moderator