SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rollback Update Statement


Rollback Update Statement

Author
Message
K Currie
K Currie
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 444
I'm trying to prevent a user from updating a value in a table from b to a. Once the value becomes b, it should never revert back to a.

To accomplish this, I decided to add a trigger on the table and if they try to update from value b back to value a I don't want to allow this.

In my trigger, I used Rollback to prevent the update from happening. This trigger works well in the sense that it does not allow value a into the field however, when I do this, I get a message from SQL saying:
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

Is this message simply informative and not hurting anything or is it causing harm. Should I be doing this differently?
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7765 Visits: 25280
Error Level 16 indicates that the error can be corrected by the user.

From what you have stated, I am led to believe that there is more to your problem than what you have posted. There is no need to use a trigger to enforce your requirement.
For example your UPDATE T-SQL code could be as simple as:



DECLARE @U VARCHAR(1)
SET @U = 'A'
UPDATE #RB SET X = @U WHERE X <> 'b'




The fallacy of the simple code is that if the value is NOT 'B', but lets say it is 'Z'. then the above T-SQL would update the 'Z' to an 'A'.

To get tested help please post table definition, sample data and required results when using the sample data. To do so quickly and easily please read the article in the first link of my signature block.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
K Currie
K Currie
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 444
Thanks. I can't control the update statement that runs, as this is done by the application.
(When the user clicks save, the application updates the entire row.)

Because I can't control this, my trigger is simply a workaround. Perhaps my trigger can be rewritten a bit?


CREATE TRIGGER [dbo].[TRG_PreserveActiveStatus] ON [dbo].[FD__MEDICATIONORDERS]
FOR UPDATE
AS

SET NOCOUNT ON

DECLARE @MEDORDERKEY INT
DECLARE @NOTED VARCHAR(25)
DECLARE @UPDATEDSTATUS VARCHAR(25)
-- STATUS OF ORDER IS PENDING WHEN ORIGINALLY SAVED, ONCE ORDER IS SUBMITTED, IT CHANGES TO ACTIVE
-- IF USER SAVES FORM AGAIN, STATUS CHANGES FROM ACTIVE TO PENDING WHICH IS WRONG SINCE DOSES EXIST
-- THIS ROLLS THE PENDING STATUS CHANGE BACK


SELECT @MEDORDERKEY = MEDORDERKEY
,@UPDATEDSTATUS = STATUS
FROM inserted

IF @UPDATEDSTATUS = 'Pending'
BEGIN
SELECT @NOTED = NOTED
FROM FD__MEDICATIONORDERS
WHERE OP__DOCID = @MEDORDERKEY

IF @NOTED = 'T'

ROLLBACK

END


Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18272 Visits: 14889
Yes you could and should re-write the trigger, but not just because of the error message, which you get whenever you have a rollback in a trigger, there's no way to avoid the message you just need to expect it.

The reason you should re-write the trigger is because it cannot handle a set-based update. It assumes that there will NEVER be a case where multiple rows will be updated by a statement. I suggest you read this article, http://www.sqlservercentral.com/articles/Triggers/64214/, which explains more about what I'm saying here.

In this case I think your best option is to use an INSTEAD OF trigger instead of the default AFTER trigger. So you're trigger would look something like this:


CREATE TRIGGER [dbo].[TRG_PreserveActiveStatus] ON [dbo].[FD__MEDICATIONORDERS]
INSTEAD OF UPDATE
AS
BEGIN;
SET NOCOUNT ON;

/* This trigger only UPDATES dbo.FD__MEDICATIONORDERS when the status isn't back to pending and noted is not 'T' */

UPDATE dbo.FD__MEDICATIONORDERS
SET STATUS = I.STATUS, column1 = I.column1, ...
FROM
INSERTED AS I
WHERE
dbo.FD__MEDICATIONORDERS.PrimaryKey = I.PrimaryKey AND
I.STATUS <> 'Pending' AND I.NOTED <> 'T' ;

END;



I probably didn't get the logic right, but basically what happens is that when an update is issued the code in the trigger replaces what actually happens in the update, so you just need the where clause to EXCLUDE those rows that are being updated that would violate the business rules.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
K Currie
K Currie
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 444
I believe this worked. My update did not occur and I did not get the message I was getting before.
Thanks!!
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18272 Visits: 14889
I assume you mean the INSTEAD OF trigger worked?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Eric M Russell
Eric M Russell
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11968 Visits: 10622
If you can implement this requirement as a table check constraint, then that would perhaps be the best solution, because it would involve no programming work arounds. For example, let's assume that the column in question is an enrollment status code that is logically tied to enrolled_date and disenrolled_date columns.
check
(
(disenrolled_date is null and enrolled_status = 'E')
or (disenrolled_date is not null and enrolled_status = 'D')
)


If the application should NEVER update this specific column, only insert rows and perhaps update other columns, then you can DENY UPDATE on the column to the application account.
deny update on [table] ( [column] ) to [user];




"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search