architecture question: triggers vs sps

[rravenn]

Hi.
I like triggers a lot cause in my view stored procedures have a lot of shortcomings; data consistensy is not guaranteed, there can be many SPs that do the same thing created by unaware developers; they require a lot of permission management and if they are numerous it's pita to answer questions like "is there a procedure to retrieve all agreements states or do I need to create one?".

Triggers however always fire so they ensure data consistency; they are tied to the table so it's easy to manage them.

I think of it as sps being procedural programming, and tirggers being object oriented sortof due to the aforementioned points.

However I see that most poeple don't like triggers and hesitate to use them even when they are needed for simple data consistency, trying to find some other way to deal with the problem. Why's that? What disadvantages do tirggers have?

 

[vongrunt]

> I think of it as sps being procedural programming, and tirggers being object oriented sortof due to the aforementioned points.

Sortof... triggers are event-driven. Do web page event handlers written in javascript (onclick, onsubmit, blah) make code object-oriented?

> However I see that most poeple don't like triggers and hesitate to use them even when they are needed for simple data consistency, trying to find some other way to deal with the problem. Why's that? What disadvantages do tirggers have?

For starters, there are many pitfalls people aren't aware of:

a) if INSERT/UPDATE/DELETE affects 5 rows, corresponding trigger(s) will fire once, not 5 times.
b) UPDATE() check returns true for dummy updates (SET blah = blah)
c) COLUMNS_UPDATED() = evil

Suppose there are details table w/ trigger and totals table maintained by that trigger. If details takes 10,000 individual INSERTS trigger will fire 10,000 times. Chances are totals table will become hotspot. Normally it would be nice to insert 10,000 rows all at once - but this is not always possible (if business logic is too complex) - and writing bullet-proof set-based triggers (see a)) is MUCH more complex than writing equivalent stored procedures.

Triggers make sense for extending data integrity/business rules, especially in cases when all DML cannot be channeled through sprocs. Problem is, their correct implementation require above-average skill level. IMHO.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[mrdenny]

there can be many SPs that do the same thing created by unaware developers;

Developers should be communicating with the other developers in the team to ensure that this doesn't happen.

If they think that they need to write new code to update something, they need to do a little leg work before they start writing.

they require a lot of permission management

You grant execute to them. That's it. All permissions to the tables are inherited from the procedure.

Procedures give you a much greater level of security on the database than direct table access (which is what you have when only using triggers).

Standard procedure for a properly secured database is that user accounts (or application accounts) can only access stored procedures. Access to any table (including access to system tables) is not granted. This prevents basic users from viewing data that they are not suppose to see.

"is there a procedure to retrieve all agreements states or do I need to create one?".

You can't write a triggers to select data from a table. Triggers also can not fire when selecting data.

If you need to log every time someone looks at a record there is no way to do that without either writing direct code to handle it or using a procedure to log the info, then return the record. This way it's done with a single transaction and a single call to the database.

Triggers are also VERY hard to debug since you can't fire them manually. You have to insert data into the table, then look at the after the fact.

I also ditto everything that vongrunt posted.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com