John,

>You could do it with SPT. But having a SP that cannot be screwed with and that will 100% succeed or fail right there at the server, carries an extra later of security blanket IMHO.

Again, a matter of abstraction IMO. If the transaction is specified at a business object, a layer or tier specifically aimed to this, You also have one interface to the data in very much the same way as SPs. It would be hard to pin down the technical difference here.

One of the technical difference might be that the initiation of the transaction does not take place on the server process itself, but on another process or machine. But this is not a big deal, database servers are specifically designed to do this.

I think the argument is more a phychological/organisational one. If using SPs, you're forced to think in single units of transaction whereas with SPT you have way more flexibility and thus more chance of abusing it or doing it wrong. The developers are more tempted to write their own transactions and with more developers on a team there is no central point that is responsible for those transactions. In the case of SPs, you can clearly pinpoint one developer/DBA to be the responsible one for all SPs.

So, again there is little room for technical arguments, but I recognise that in these cases organisationally there might be an advantage for SPs as on person could act as the gate keeper.

That beeing said, that is not to say that you can't do the same with SPT, but it requires that you run at least run through layers where database access is exclusively done through a layer which is maintained by one or a very few persons, who actually know what they are doing. If you're doing that there really is no difference between the SP and SPT approach, except for the location of the logic (Database vs business layer)

As I'm been told by a friend who works as a oracle administrator/trouble shooter in holland and the UK, some corporations choose to have NO DATABASE CONSTRAINTS OR PROCEDURES at all on the database. The database is only a place to store data all the business logic and constraints have to be implemented through a database logic layer/tier. SPs are completely banned here (maybe except for the built in ones)

Walter,