Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rollback Update Statement Expand / Collapse
Author
Message
Posted Tuesday, April 3, 2012 3:16 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 11, 2014 8:40 AM
Points: 66, Visits: 418
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?
Post #1277688
Posted Tuesday, April 3, 2012 8:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Post #1277758
Posted Tuesday, April 3, 2012 10:52 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 11, 2014 8:40 AM
Points: 66, Visits: 418
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

Post #1277777
Posted Wednesday, April 4, 2012 9:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:26 AM
Points: 10,381, Visits: 13,436
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

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
Post #1278199
Posted Wednesday, April 4, 2012 2:47 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 11, 2014 8:40 AM
Points: 66, Visits: 418
I believe this worked. My update did not occur and I did not get the message I was getting before.
Thanks!!
Post #1278411
Posted Wednesday, April 4, 2012 3:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:26 AM
Points: 10,381, Visits: 13,436
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

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
Post #1278420
Posted Thursday, April 5, 2012 12:51 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 1,793, Visits: 5,044
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];

Post #1279096
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse