Prevent data modification using trigger

I'm trying to prevent anyone running an update on my table and updating the data in a row if one of the fields is equal to 'Z' if the field is equal to anything else they can do what they like.

Unfortunately I cant think of a way to do this though i have a notion that it could be done with an "instead of update" trigger - however my attempts at doing this have only led to me deleting all my data!!!
TactfulElfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

muzzy2003Commented:
This should do it, though it's a bit broad in its approach - any update that includes one of the 'Z' flags will cause the whole update to fail. It's something to work with, though.

ALTER TRIGGER TU_Table
ON Table
FOR UPDATE
AS

      IF EXISTS      (SELECT      *
                  FROM      deleted
                  WHERE      Flag = 'Z')
            ROLLBACK TRAN
0
KarinLoosCommented:
You could try the following ( a normal trigger)

CREATE TRIGGER trg_SomeTriggerName
ON SomeTable
FOR UPDATE
AS
BEGIN
    If EXISTS ( select somefield from Deleted where somefield = 'z' )  
      begin
           rollback transaction
      end    
 

END
0
KarinLoosCommented:
oops sorry muzzy
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

TactfulElfAuthor Commented:
i really dont want to rollback the entire transaction - i just want to stop the rows already containing a 'Z' from being updated, i had already considered the approach you mention thnx.
0
Scott PletcherSenior DBACommented:
If will probably be clearer for the caller/pgm if you specify a reason for the rollback:


IF EXISTS (SELECT * FROM deleted
                 WHERE Flag = 'Z')
    RAISERROR('Row(s) cannot be updated because Flag = "Z", update cancelled', 16, 1)
    ROLLBACK TRANSACTION
0
KarinLoosCommented:
maybe something like
if EXISTS ( Select * from deleted WHERE flag = 'Z')
  begin
     delete inserted where flag = 'z'
 end
0
Scott PletcherSenior DBACommented:
Maybe something like this then:


CREATE TRIGGER yourTable_Trg_Upd
ON yourTable
AFTER UPDATE
AS
-- if no rows with flag = 'Z' were changed, we can exit, don't need to do anything
IF NOT EXISTS(SELECT 1 FROM deleted WHERE flag = 'Z')
      RETURN

-- at least one "Z" row was updated;
-- back out changes to those rows and notify caller/pgm about it
DECLARE @zCount INT
SET @zCount = (SELECT COUNT(*) FROM deleted WHERE flag = 'Z')

-- "undo" UPDATE on "Z" rows
UPDATE yourTable
SET col1 = d.col1, col2 = d.col2, col3 = d.col3  --, ...
FROM yourTable
INNER JOIN deleted d ON yourTable.keyCol = d.keyCol
WHERE d.flag = 'Z'

RAISERROR('%d Row(s) had flag = "Z", Updates on those rows were cancelled', 16, 1, @zCount)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TactfulElfAuthor Commented:
thats great!! cheers!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.