|
Home > Archive > SQL server exams > December 2002 > Triggers better than stored procedures?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Triggers better than stored procedures?
|
|
| sjgross 2002-12-13, 12:36 pm |
| 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) | |
| 2lazybutsmart 2002-12-14, 2:25 am |
| 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 | |
| sjgross 2002-12-15, 9:14 pm |
| 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) |
|
|
|
|