Solved

Constraint on Update and Delete

Posted on 2007-03-30
6
234 Views
Last Modified: 2010-03-20
Hi

I am trying to write a create statement whereby I create a new table with two constraints.  They are that there can be no updates or deletes.  I know that the syntax for constraints is CONSTRAINT XXX, but am not quite sure on how to use it.  Here is my current statement:

CREATE TABLE  `ordertbl` (
  `OrdNo` varchar(8) NOT NULL,
  `OrdDate` date NOT NULL,
  `CustNo` varchar(8) NOT NULL,
  `EmpNo` varchar(8) default NULL,
  PRIMARY KEY  (`OrdNo`));

Greg
0
Comment
Question by:greg_c
6 Comments
 
LVL 7

Expert Comment

by:jeepstyx22
ID: 18827764
Couldn't you just remove Update and Delete rights from the table?  See this MSDN page on denying permssions: http://msdn2.microsoft.com/en-us/library/ms177518.aspx

Also, you can view this forum,http://www.webservertalk.com/archive132-2005-7-1147074.html, which has a few ideas for locking down these things.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18828248
in addition to DENY the UPDATE and DELETE to the users, you could also implement a trigger that rollsback any updates/deletes.
you cannot implement this with constraints.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18828528
you can't do that with constraints as angeliii says... a trigger is the only ultimate safeguard...

what are you actually trying to do?  
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:greg_c
ID: 18829735
I am trying to prevent a user from updating and deleting a row, through SQL.

Greg
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18832918
So I repeat my comment:
in addition to DENY the UPDATE and DELETE to the users, you could also implement a trigger that rollsback any updates/deletes.
do you need help with those?
0
 

Author Comment

by:greg_c
ID: 18833057
No, Thanks for your help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with stripping out character in SQL LEFT/RIGHT/REPLACE 2 41
Remove alpha from alphanumeric 4 60
SQL Union 20 44
SQL Query Producing decimal places when it shouldn;t be 8 35
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now