[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Prevent data modification using trigger

Posted on 2004-11-23
8
Medium Priority
?
318 Views
Last Modified: 2008-03-17
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!!!
0
Comment
Question by:TactfulElf
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12656581
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
 
LVL 13

Expert Comment

by:KarinLoos
ID: 12656628
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
 
LVL 13

Expert Comment

by:KarinLoos
ID: 12656631
oops sorry muzzy
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:TactfulElf
ID: 12656651
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 12656685
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
 
LVL 13

Expert Comment

by:KarinLoos
ID: 12656712
maybe something like
if EXISTS ( Select * from deleted WHERE flag = 'Z')
  begin
     delete inserted where flag = 'z'
 end
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 12656751
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
 

Author Comment

by:TactfulElf
ID: 12663804
thats great!! cheers!!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question