June 27, 2018 at 5:53 pm
Hi all
I'm looking for a way to use RAISERROR with severity 16 (or at least >10) in a trigger, but still get the trigger to do an insert to another table. Is this possible and if so, how?
If not, can anyone suggest a better solution for what we're trying to achieve here (see detail below)?
Background:
I have a table that users update through an application and a trigger that does some validation (including data on other tables that typically contain thousands of related transactional rows) and logs an error message in a different table if it finds critical issues. Users then get e-mail notifications (not part of this trigger) every few minutes of new errors logged to alert them to issues so they can go and fix it (in reality they only get an e-mail at most once a month, but the job that checks for new errors runs every few minutes). I know this may seem like a really stupid way to do basic data validation, and it is, but there is also a good reason behind it. In short the application's business logic is supposed to prevent this scenario from occurring, but for some reason at one specific client it sometimes doesn't (like less than once every six months and affecting less than 1/10000 related transactional records). The same application has >100 live instances elsewhere where we've never had the issue, some going back 14 years with several million transactional records. We haven't been able to figure out why this happens at this one client once in a blue moon, so as a workaround while we try to figure that out we introduced the trigger.
Problem is that the users seem to ignore the emails (or claim that they received them too late), so what we would like to do is to refine the trigger a bit and stop the update, but we still want to log the errors, and we would like to display some kind of error to the user on the application so they know to go and check the log.
RAISERROR with a severity >10 stops the update and displays the error in the app frontend, but it rolls back the whole transaction, including the error logging.
RAISERROR with severity <=10 does the error logging just fine, but doesn't display the error in the frontend, nor does it roll back the original update.
------------------------------------------
Anton
www.sqlsqllittlestar.com
June 28, 2018 at 9:39 am
antonstar - Wednesday, June 27, 2018 5:53 PMHi allI'm looking for a way to use RAISERROR with severity 16 (or at least >10) in a trigger, but still get the trigger to do an insert to another table. Is this possible and if so, how?
If not, can anyone suggest a better solution for what we're trying to achieve here (see detail below)?
Background:
I have a table that users update through an application and a trigger that does some validation (including data on other tables that typically contain thousands of related transactional rows) and logs an error message in a different table if it finds critical issues. Users then get e-mail notifications (not part of this trigger) every few minutes of new errors logged to alert them to issues so they can go and fix it (in reality they only get an e-mail at most once a month, but the job that checks for new errors runs every few minutes). I know this may seem like a really stupid way to do basic data validation, and it is, but there is also a good reason behind it. In short the application's business logic is supposed to prevent this scenario from occurring, but for some reason at one specific client it sometimes doesn't (like less than once every six months and affecting less than 1/10000 related transactional records). The same application has >100 live instances elsewhere where we've never had the issue, some going back 14 years with several million transactional records. We haven't been able to figure out why this happens at this one client once in a blue moon, so as a workaround while we try to figure that out we introduced the trigger.Problem is that the users seem to ignore the emails (or claim that they received them too late), so what we would like to do is to refine the trigger a bit and stop the update, but we still want to log the errors, and we would like to display some kind of error to the user on the application so they know to go and check the log.
RAISERROR with a severity >10 stops the update and displays the error in the app frontend, but it rolls back the whole transaction, including the error logging.
RAISERROR with severity <=10 does the error logging just fine, but doesn't display the error in the frontend, nor does it roll back the original update.
Sorry, but that's just not realistic. The only things that can survive a rollback are changes that occur outside of the database, such as a file that get deposited in a file share by BCP, for example. If you changed the nature of the check job that runs every few minutes, you could have it check for the existence of any output files, which would have to be uniquely named.
Oh wait... what about an INSTEAD OF UPDATE trigger that when fired, checks for the problem and only if it does NOT find a problem, perform the update. You have to be sure that recursive trigger firing is NOT enabled.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 28, 2018 at 9:44 am
Why don't you fix this at the application layer?
June 28, 2018 at 9:53 am
I am assuming you issue the rollback inside the trigger as that would affect all updates to that point.
I am also assuming that the error logging is occurring inside the trigger.
Instead of logging the errors directly to physical tables inside the trigger, log the errors to table variables. Write the data from the table variables to the physical tables after the rollback and before the trigger execution ends.
June 28, 2018 at 10:03 am
Lynn Pettis - Thursday, June 28, 2018 9:53 AMI am assuming you issue the rollback inside the trigger as that would affect all updates to that point.
I am also assuming that the error logging is occurring inside the trigger.Instead of logging the errors directly to physical tables inside the trigger, log the errors to table variables. Write the data from the table variables to the physical tables after the rollback and before the trigger execution ends.
Dang, ... I forgot about that... Always good to learn stuff, even when it's a re-learn.... :hehe:
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 28, 2018 at 10:11 am
This sounds like a good example of the things that should NOT be done in a trigger.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 28, 2018 at 10:28 am
Michael L John - Thursday, June 28, 2018 10:11 AMThis sounds like a good example of the things that should NOT be done in a trigger.
Depends on how quickly the trigger code works. I would prefer it all be done in the stored procedure and not use a trigger. Still, log the errors to table variables and after the rollback write the data from the table variables to the physical tables. A good use for table variables since they are not affected by the rollback.
June 28, 2018 at 10:31 am
sgmunson - Thursday, June 28, 2018 9:39 AMantonstar - Wednesday, June 27, 2018 5:53 PMHi allI'm looking for a way to use RAISERROR with severity 16 (or at least >10) in a trigger, but still get the trigger to do an insert to another table. Is this possible and if so, how?
If not, can anyone suggest a better solution for what we're trying to achieve here (see detail below)?
Background:
I have a table that users update through an application and a trigger that does some validation (including data on other tables that typically contain thousands of related transactional rows) and logs an error message in a different table if it finds critical issues. Users then get e-mail notifications (not part of this trigger) every few minutes of new errors logged to alert them to issues so they can go and fix it (in reality they only get an e-mail at most once a month, but the job that checks for new errors runs every few minutes). I know this may seem like a really stupid way to do basic data validation, and it is, but there is also a good reason behind it. In short the application's business logic is supposed to prevent this scenario from occurring, but for some reason at one specific client it sometimes doesn't (like less than once every six months and affecting less than 1/10000 related transactional records). The same application has >100 live instances elsewhere where we've never had the issue, some going back 14 years with several million transactional records. We haven't been able to figure out why this happens at this one client once in a blue moon, so as a workaround while we try to figure that out we introduced the trigger.Problem is that the users seem to ignore the emails (or claim that they received them too late), so what we would like to do is to refine the trigger a bit and stop the update, but we still want to log the errors, and we would like to display some kind of error to the user on the application so they know to go and check the log.
RAISERROR with a severity >10 stops the update and displays the error in the app frontend, but it rolls back the whole transaction, including the error logging.
RAISERROR with severity <=10 does the error logging just fine, but doesn't display the error in the frontend, nor does it roll back the original update.Sorry, but that's just not realistic. The only things that can survive a rollback are changes that occur outside of the database, such as a file that get deposited in a file share by BCP, for example. If you changed the nature of the check job that runs every few minutes, you could have it check for the existence of any output files, which would have to be uniquely named.
Oh wait... what about an INSTEAD OF UPDATE trigger that when fired, checks for the problem and only if it does NOT find a problem, perform the update. You have to be sure that recursive trigger firing is NOT enabled.
An update statement inside of an INSTEAD OF trigger won't re-fire the INSTEAD OF trigger, at least that is what I remember reading. It has been a while.
June 28, 2018 at 10:48 am
Lynn Pettis - Thursday, June 28, 2018 10:31 AMsgmunson - Thursday, June 28, 2018 9:39 AMantonstar - Wednesday, June 27, 2018 5:53 PMHi allI'm looking for a way to use RAISERROR with severity 16 (or at least >10) in a trigger, but still get the trigger to do an insert to another table. Is this possible and if so, how?
If not, can anyone suggest a better solution for what we're trying to achieve here (see detail below)?
Background:
I have a table that users update through an application and a trigger that does some validation (including data on other tables that typically contain thousands of related transactional rows) and logs an error message in a different table if it finds critical issues. Users then get e-mail notifications (not part of this trigger) every few minutes of new errors logged to alert them to issues so they can go and fix it (in reality they only get an e-mail at most once a month, but the job that checks for new errors runs every few minutes). I know this may seem like a really stupid way to do basic data validation, and it is, but there is also a good reason behind it. In short the application's business logic is supposed to prevent this scenario from occurring, but for some reason at one specific client it sometimes doesn't (like less than once every six months and affecting less than 1/10000 related transactional records). The same application has >100 live instances elsewhere where we've never had the issue, some going back 14 years with several million transactional records. We haven't been able to figure out why this happens at this one client once in a blue moon, so as a workaround while we try to figure that out we introduced the trigger.Problem is that the users seem to ignore the emails (or claim that they received them too late), so what we would like to do is to refine the trigger a bit and stop the update, but we still want to log the errors, and we would like to display some kind of error to the user on the application so they know to go and check the log.
RAISERROR with a severity >10 stops the update and displays the error in the app frontend, but it rolls back the whole transaction, including the error logging.
RAISERROR with severity <=10 does the error logging just fine, but doesn't display the error in the frontend, nor does it roll back the original update.Sorry, but that's just not realistic. The only things that can survive a rollback are changes that occur outside of the database, such as a file that get deposited in a file share by BCP, for example. If you changed the nature of the check job that runs every few minutes, you could have it check for the existence of any output files, which would have to be uniquely named.
Oh wait... what about an INSTEAD OF UPDATE trigger that when fired, checks for the problem and only if it does NOT find a problem, perform the update. You have to be sure that recursive trigger firing is NOT enabled.
An update statement inside of an INSTEAD OF trigger won't re-fire the INSTEAD OF trigger, at least that is what I remember reading. It has been a while.
I can't say I remember exactly, but I did want to be sure that couldn't happen, which was the important part. I'll take corrections to stuff I don't have right all day long!
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 28, 2018 at 10:55 am
sgmunson - Thursday, June 28, 2018 10:48 AMLynn Pettis - Thursday, June 28, 2018 10:31 AMsgmunson - Thursday, June 28, 2018 9:39 AMantonstar - Wednesday, June 27, 2018 5:53 PMHi allI'm looking for a way to use RAISERROR with severity 16 (or at least >10) in a trigger, but still get the trigger to do an insert to another table. Is this possible and if so, how?
If not, can anyone suggest a better solution for what we're trying to achieve here (see detail below)?
Background:
I have a table that users update through an application and a trigger that does some validation (including data on other tables that typically contain thousands of related transactional rows) and logs an error message in a different table if it finds critical issues. Users then get e-mail notifications (not part of this trigger) every few minutes of new errors logged to alert them to issues so they can go and fix it (in reality they only get an e-mail at most once a month, but the job that checks for new errors runs every few minutes). I know this may seem like a really stupid way to do basic data validation, and it is, but there is also a good reason behind it. In short the application's business logic is supposed to prevent this scenario from occurring, but for some reason at one specific client it sometimes doesn't (like less than once every six months and affecting less than 1/10000 related transactional records). The same application has >100 live instances elsewhere where we've never had the issue, some going back 14 years with several million transactional records. We haven't been able to figure out why this happens at this one client once in a blue moon, so as a workaround while we try to figure that out we introduced the trigger.Problem is that the users seem to ignore the emails (or claim that they received them too late), so what we would like to do is to refine the trigger a bit and stop the update, but we still want to log the errors, and we would like to display some kind of error to the user on the application so they know to go and check the log.
RAISERROR with a severity >10 stops the update and displays the error in the app frontend, but it rolls back the whole transaction, including the error logging.
RAISERROR with severity <=10 does the error logging just fine, but doesn't display the error in the frontend, nor does it roll back the original update.Sorry, but that's just not realistic. The only things that can survive a rollback are changes that occur outside of the database, such as a file that get deposited in a file share by BCP, for example. If you changed the nature of the check job that runs every few minutes, you could have it check for the existence of any output files, which would have to be uniquely named.
Oh wait... what about an INSTEAD OF UPDATE trigger that when fired, checks for the problem and only if it does NOT find a problem, perform the update. You have to be sure that recursive trigger firing is NOT enabled.
An update statement inside of an INSTEAD OF trigger won't re-fire the INSTEAD OF trigger, at least that is what I remember reading. It has been a while.
I can't say I remember exactly, but I did want to be sure that couldn't happen, which was the important part. I'll take corrections to stuff I don't have right all day long!
Reading it just now it doesn't really say, but much older versions of BOL, iirc, were explicit on the subject. If you think about it, it also makes logical sense that it wouldn't because it would defeat the purpose of the INSTEAD OF triggers.
June 28, 2018 at 11:58 am
Lynn Pettis - Thursday, June 28, 2018 10:31 AMAn update statement inside of an INSTEAD OF trigger won't re-fire the INSTEAD OF trigger, at least that is what I remember reading. It has been a while.
This is correct, I use INSTEAD OF triggers in a certain database and it won't re-fire.
June 28, 2018 at 12:01 pm
Chris Harshman - Thursday, June 28, 2018 11:58 AMLynn Pettis - Thursday, June 28, 2018 10:31 AMAn update statement inside of an INSTEAD OF trigger won't re-fire the INSTEAD OF trigger, at least that is what I remember reading. It has been a while.This is correct, I use INSTEAD OF triggers in a certain database and it won't re-fire.
Thanks for confirming the correctness. It's what I suspected but wasn't sure enough to want to not mention my concern.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 16, 2018 at 3:53 pm
Hi Folks. Sorry, didn't mean to disappear after posting once, just got a bit snowed under. Thanks for all the replies (esp Steve & Lynn), some responses:
Michael L John - Thursday, June 28, 2018 10:11 AMThis sounds like a good example of the things that should NOT be done in a trigger.
I agree - that's why we're using a trigger only as a workaround until we have a way of fixing it properly.
ZZartin - Thursday, June 28, 2018 9:44 AMWhy don't you fix this at the application layer?
We are trying to do that, but it's complicated and we don't want to break something that works for 99.999% of cases to fix a problem that we haven't been able to replicate and that only happens in 0.001% of cases.
Lynn Pettis - Thursday, June 28, 2018 9:53 AMI am assuming you issue the rollback inside the trigger as that would affect all updates to that point.
I am also assuming that the error logging is occurring inside the trigger.Instead of logging the errors directly to physical tables inside the trigger, log the errors to table variables. Write the data from the table variables to the physical tables after the rollback and before the trigger execution ends.
I don't explicitly issue the rollback, this happens automatically with the RAISERROR.
Yes, the error logging happens inside the trigger.
I've already tried to log the errors in table variables (was doing this anyway) and only writing this to the DB after the RAISERROR but this does not work (execution seems to stop completely when the RAISERROR happens).
I think it may be important to point out here that the application catches a RAISERROR with severity 16 and displays the error text on the front-end, hence my preference for using this as it provides a mechanism to alert the user to what's going on in the DB in a way that is at least somewhat understandable to end users of the application.
I get the impression I may be missing something here by not explicitly doing a rollback, but not sure if this will work in combination with the RAISERROR. I'm going to give it a shot and if it still doesn't work, I'll try to post some of the code (need to simplify a bit first to avoid spamming everyone with the unimportant details).
sgmunson - Thursday, June 28, 2018 9:39 AM...
Oh wait... what about an INSTEAD OF UPDATE trigger that when fired, checks for the problem and only if it does NOT find a problem, perform the update. You have to be sure that recursive trigger firing is NOT enabled.
Thanks (also to Lynn & Chris for confirming that I needn't worry about the recursive trigger), I have not yet tried the INSTEAD OF option and this sounds like it should work. Will give it a try when I have a chance to work on this again and give some feedback.
------------------------------------------
Anton
www.sqlsqllittlestar.com
July 16, 2018 at 4:06 pm
antonstar - Monday, July 16, 2018 3:53 PMHi Folks. Sorry, didn't mean to disappear after posting once, just got a bit snowed under. Thanks for all the replies (esp Steve & Lynn), some responses:Michael L John - Thursday, June 28, 2018 10:11 AMThis sounds like a good example of the things that should NOT be done in a trigger.I agree - that's why we're using a trigger only as a workaround until we have a way of fixing it properly.
ZZartin - Thursday, June 28, 2018 9:44 AMWhy don't you fix this at the application layer?We are trying to do that, but it's complicated and we don't want to break something that works for 99.999% of cases to fix a problem that we haven't been able to replicate and that only happens in 0.001% of cases.
Lynn Pettis - Thursday, June 28, 2018 9:53 AMI am assuming you issue the rollback inside the trigger as that would affect all updates to that point.
I am also assuming that the error logging is occurring inside the trigger.Instead of logging the errors directly to physical tables inside the trigger, log the errors to table variables. Write the data from the table variables to the physical tables after the rollback and before the trigger execution ends.
I don't explicitly issue the rollback, this happens automatically with the RAISERROR.
Yes, the error logging happens inside the trigger.
I've already tried to log the errors in table variables (was doing this anyway) and only writing this to the DB after the RAISERROR but this does not work (execution seems to stop completely when the RAISERROR happens).
I think it may be important to point out here that the application catches a RAISERROR with severity 16 and displays the error text on the front-end, hence my preference for using this as it provides a mechanism to alert the user to what's going on in the DB in a way that is at least somewhat understandable to end users of the application.
I get the impression I may be missing something here by not explicitly doing a rollback, but not sure if this will work in combination with the RAISERROR. I'm going to give it a shot and if it still doesn't work, I'll try to post some of the code (need to simplify a bit first to avoid spamming everyone with the unimportant details).sgmunson - Thursday, June 28, 2018 9:39 AM...
Oh wait... what about an INSTEAD OF UPDATE trigger that when fired, checks for the problem and only if it does NOT find a problem, perform the update. You have to be sure that recursive trigger firing is NOT enabled.Thanks (also to Lynn & Chris for confirming that I needn't worry about the recursive trigger), I have not yet tried the INSTEAD OF option and this sounds like it should work. Will give it a try when I have a chance to work on this again and give some feedback.
Are you using TRY CATCH blocks in the code and trigger? That is the way to handle the logging when you first raise an error in the trigger. Execution is then transferred to the CATCH BLOCK where you capture the error information, ROLLBACK the transaction explicitly then copy the error information for the table variables to the permanent audit/error tables, then re-raise the error (or use THROW to accomplish this).
July 16, 2018 at 4:12 pm
Lynn Pettis - Monday, July 16, 2018 4:06 PM...
Are you using TRY CATCH blocks in the code and trigger? That is the way to handle the logging when you first raise an error in the trigger. Execution is then transferred to the CATCH BLOCK where you capture the error information, ROLLBACK the transaction explicitly then copy the error information for the table variables to the permanent audit/error tables, then re-raise the error (or use THROW to accomplish this).
Thanks for the pointers. I'm not using TRY CATCH blocks for this at the moment, but sounds like it's a better way of doing it anyway. I'm going to bed now, but will hopefully have a chance to work on this tomorrow.
------------------------------------------
Anton
www.sqlsqllittlestar.com
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply