March 12, 2009 at 1:44 pm
Lynn,
Your suggestion is interesting.
I had never used SAVEPOINT before.
For beginers though, this is more rope to hang themselves with ...
We are truly entering territory for the brave.
March 12, 2009 at 1:44 pm
Lynn,
That's my point. The proper nesting is not supported by the SQL. Maybe it's because of the violation of the ACID rules but it is not there.
I had some cases when it would be nice to have it. For example we wanted to log certain exceptions (business rules as well as some SQL errors) in the database because it would be much easier to access but we had to log to Windows Events log instead because of the transactions.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 12, 2009 at 1:51 pm
JacekO (3/12/2009)
Lynn,That's my point. The proper nesting is not supported by the SQL. Maybe it's because of the violation of the ACID rules but it is not there.
I had some cases when it would be nice to have it. For example we wanted to log certain exceptions (business rules as well as some SQL errors) in the database because it would be much easier to access but we had to log to Windows Events log instead because of the transactions.
Here is how you get around that, you write the error records to a table variable prior to the ROLLBACK. When you come out of the top level TRANSACTION, you then use the table variables to accomplish the necessary updates you wanted to capture. The updates to the table variables are not included in the transactions.
March 12, 2009 at 2:03 pm
JacekO (3/12/2009)
Lynn,If I am not mistaken the SAVE POINTS still does not provide you with an option to nest. It allows you to rollback to a certain point but it is still sequential. By nesting I mean something like this
BEGIN TRANSACTION 1
do work A
do work B
BEGIN TRANSACTION 2
do work C
do work D
COMMIT TRANSACTION 2
do work E
ROLLBACK TRANSACTION 1
work C and work D is COMMITED
work A, work B and work E is ROLLED BACK
That would violate one of the key properties of a database, its ACIDity. Not a good idea. Read up on why ACID matters. It's just about as important as a subject can be in database work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 12, 2009 at 2:05 pm
That is not a bad idea if you do not deal with many levels of SPs calling other SPs. Otherwise you have to haul that table all across all SPs all the way to the top one and if one SP calls multiple SPs then you have to add extra code to combine the records.
We investigated this option but in the end decided to have the client deal with the transactions.
And for the logging it was easier to log directly to the Windows Event Log anyway.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 12, 2009 at 2:10 pm
JacekO (3/12/2009)
1. Calling SPs from another SP is not a big deal and has nothing to do with set based or RBAR programming. I use it quite extensively because it allows me to keep certain logic contained in a single unit and helps with testing.
Agree, if nested procedure does not take a set of values representing a recordset as a parameter and is not built to update table(s) with those values.
Advantage of triggers here is they accept tables as parameters, so you're not limited to a single row.
2. Using identity is another thing that has nothing to do with set based programming vs RBAR. It has several adventages but I guess some people do not like it because it is not 'pure SQL' enough.... If it works for you - use it.
I never told that using IDENTITY leads to RBAR processing. I use IDENTITY columns in most of my tables, they are perfect "aliases" for unique keys.
But if you use @@IDENTITY or SCOPE_IDENTITY() functions - it's a clear sign of RBAR processing.
[/quote]
_____________
Code for TallyGenerator
March 12, 2009 at 2:19 pm
Sergiy
Triggers ! :w00t:
I have this deep-rooted prejudice against "hidden" code that executes from somewhere else. Say I am trying to debug a stored proc and I forget that there is a trigger on one of the tables involved. The more I focus on what is wrong with the stored procedure from hell, the farther away I drift from the solution.
I admit to prejudice on this subject.
Can you share some pearl of wisdom as to why trrigers should not be discriminated against ?
Regards
March 12, 2009 at 2:27 pm
wild guess SAVE POINTS is a sql 2008 thing right
March 12, 2009 at 2:33 pm
Nope. I still have BOL for SQL Server 2000 on one of my desktop systems. Read about SAVE TRANSACTION in BOL.
March 12, 2009 at 2:38 pm
J (3/12/2009)
SergiyTriggers ! :w00t:
I have this deep-rooted prejudice against "hidden" code that executes from somewhere else. Say I am trying to debug a stored proc and I forget that there is a trigger on one of the tables involved. The more I focus on what is wrong with the stored procedure from hell, the farther away I drift from the solution.
I admit to prejudice on this subject.
Can you share some pearl of wisdom as to why trrigers should not be discriminated against ?
Regards
Why should they be?
What make them worse than constraints? Are not constraints also kind of "hidden code"?
And hidden from whom? From cowboys who don't know what is it and cannot use it properly?
BUT!
I always recommend extreme caution with triggers.
Use them only if you know what are you doing and only where you're sure triggers should be used.
This tool is very powerful and being used in a wrong place by not qualified hands it can damage whole system so badly...
P.S. Someone would point to the fact that you misspelled the word "triggers" 😉
_____________
Code for TallyGenerator
March 12, 2009 at 2:40 pm
GSquared (3/12/2009)
That would violate one of the key properties of a database, its ACIDity. Not a good idea. Read up on why ACID matters. It's just about as important as a subject can be in database work.
Point proven. No nested transactions in SQL. :w00t:
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 12, 2009 at 2:44 pm
PS.
I like my databases alkaline...
PS 2.
Just messing with you.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 12, 2009 at 3:25 pm
Sergyi
Good point on the constraints.
However, since I am working on a specific table, I have to look no further than the table itself to find out about constraints. Also, in the event a constraint is being offended, there is an immediate response as to why the statement is barfed out. I should already be alerted to the need to obey primary key - foreign key relationships, even though these are "hidden"...
In the case of triggers though, first off, I do not see a directory where triggers are listed, as are stored procedures, views, udfs... From my limited experience I have to first find the triggers on a table with the sp_helptrigger sp and then to view the code, use sp_helptext. This is from someone who has never used trigger and has not migrated to SS2K5 yet, maybe things have improved. But the triggers appear to me as less accessible. Form what I can see, SS2K is not friendly when it comes to managing triggers.
On top of that, triggers can be made recursive. Just what a vandal needs to make it fun. I am more worried about the cowboy using triggers in a "shoot first, ask questions later" mode than I am of someone who is wary of triggers.
Well that was my opinion on this. Thanks for your input.
March 12, 2009 at 3:42 pm
J (3/12/2009)
SergyiGood point on the constraints.
However, since I am working on a specific table, I have to look no further than the table itself to find out about constraints. Also, in the event a constraint is being offended, there is an immediate response as to why the statement is barfed out. I should already be alerted to the need to obey primary key - foreign key relationships, even though these are "hidden"...
In the case of triggers though, first off, I do not see a directory where triggers are listed, as are stored procedures, views, udfs... From my limited experience I have to first find the triggers on a table with the sp_helptrigger sp and then to view the code, use sp_helptext.
Triggers are created ON objects. So, they belong to objects, not to database itself.
So, you need to look into the object properties if you want to see triggers.
In QA expand the table, and here you are - constraints, triggers, all there.
On top of that, triggers can be made recursive. Just what a vandal needs to make it fun. I am more worried about the cowboy using triggers in a "shoot first, ask questions later" mode than I am of someone who is wary of triggers.
Well that was my opinion on this. Thanks for your input.
That was exactly my point. There is no space for cowboy programming in triggers.
If you're not sure in your TSQL skills, if you never brought a server down with a small mistake in a trigger, better practice more before you deploy your triggers to production system. 🙂
And before you create a trigger you must understand - the code you put in there is not a process, not a business rule. It's an object property. Like a length of varchar field or a step of identity.
Act correspondingly.
I'm not a big fan of triggers on tables, I believe there are really rare occasions when they should be used, e.g. "computed" columns which are not actually computed but populated by a trigger.
But when I need to pass a table parameter to SP I cannot see a better way than creating trigger on view. Cabn you?
_____________
Code for TallyGenerator
March 13, 2009 at 5:29 am
Thanks Sergyi.
No, I cannot propose any better alternative, never having faced and coped with such a situation.
Regards
Viewing 15 posts - 61 through 75 (of 99 total)
You must be logged in to reply to this topic. Login to reply