Prevent One Single Row From Being Updated

Nov 8, 2006

I am doing customization for microsoft POS. I manually added a record
to a table. The manage and maintenance of this table are done by POS,
and user can update the contents of this table. Is there any way I can
lock this single row at database level to prevent it from being deleted
or changed by user? I am using SQL 2000 and vb.net.

Thanks.
Leanne

View 1 Replies


ADVERTISEMENT

Prevent Single Row Delete With Trigger

Jan 16, 2015

I have this table:

CREATE TABLE Workspaces (
AreaNr CHAR(2)
CONSTRAINT ck_a_areanr REFERENCES Areas(AreaNr)
ON DELETE CASCADE

[Code] ....

Now I want to create a trigger that prevents a delete on a single row (randomly chosen) from the table Workspaces. At the moment I have the following trigger, but this trigger still allows removal of single rows.

Current trigger:

CREATE TRIGGER deleteWorkspace ON Workspaces
FOR DELETE AS

BEGIN
DECLARE @Count int
SET @Count = @@ROWCOUNT;

[Code] ....

Desired result: I want to be able to prevent a delete on a single row on the table above.

View 1 Replies View Related

Save Updated Date When Row Is Updated

Apr 6, 2008

Hi,I want to save the last modification date when the row is updated. I have a column called "LastModification" in the table, every time the row is update I want to set the value of this column to the current date. So far all I know is that I need to use a trigger and the GetDate() function, but could any body help me with how to set the value of the column to getdate()? thanks for your help. 

View 3 Replies View Related

Combine Data In Single Row From Single Table

Apr 4, 2006

How can i combine my data in single row ? All data are in a single table sorted as employeeno, date


Code:

Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000


Should Appear


Code:

EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800

PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.

THANKS IN ADVANCE

View 3 Replies View Related

SQL Injection - How To Prevent It?

Jan 21, 2007

I am building my first ASP.Net app from scratch and while working on the DAL I came across the problem of SQL Injection. I searched on the web and read different articles but I am still unsure about the answer. My question is should I add
 
db.AddInParameter(dbCommand, "AvatarImageID", DbType.Int32, avatarImageID);
 
Add in Parameters to my C# code to avoid SQL Injection. What is the best practice. I am unclear if the stored procedure already helps me avoid SQl Injection or if I need the add in parameters in the C# methods to make it work. I need some help. Thanks, Newbie
 
My C# update method in the DAL (still working on the code)
 
private static bool Update(AvatarImageInfo avatarImage)
        {
            //Invoke a SQL command and return true if the update was successful.
            db.ExecuteNonQuery("syl_AvatarImageUpdate",
                avatarImage.AvatarImageID,
                avatarImage.DateAdded,
                avatarImage.ImageName,
                avatarImage.ImagePath,
                avatarImage.IsApproved);
 
            return true;
        }
 
 
I am using stored procedures to access the data in the database.
 
My update stored proc
 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[syl_AvatarImageUpdate]
      @AvatarImageID          int, 
      @DateAdded              datetime,  
      @ImageName              nvarchar(64),    
      @ImagePath              nvarchar(64),    
      @IsApproved             bit
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      BEGIN TRY
            UPDATE      [syl_AvatarImages]
            SET
                        [DateAdded]       = @DateAdded,
                        [ImageName]       = @ImageName,
                        [ImagePath]       = @ImagePath,
                        [IsApproved]      = @IsApproved
            WHERE [AvatarImageID] = @AvatarImageID
            RETURN
      END TRY
      BEGIN CATCH
            --Execute LogError SP  
            EXECUTE [dbo].[syl_LogError];
            --Being in a Catch Block indicates failure.
            --Force RETURN to -1 for consistency (other return values are generated, such as -6).
            RETURN -1
      END CATCH
END

View 2 Replies View Related

Prevent Sql Job Failure.

May 21, 2008

Have a job that calls a DTS package, DTS is an Export & Import wizard to copy tables. Someone deleted a table from source and my job failed last night. Inputs appreaciated.

View 12 Replies View Related

Best Way To Prevent Deadlocks

May 29, 2008

I'm going thru my application log, and just seeing what errors are popping up. I have a relatively intense search feature, thats causing alot of deadlocks.

Exception type: SqlException
Exception message: Transaction (Process ID 105) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

In general, what's the best way to resolve this ?

Should I see if I can apply "WITH (NOLOCK)" to my data ?

Any suggestions are greatly appreciated !


thanks again!
mike123

View 4 Replies View Related

How To Prevent Db Deletion

Nov 22, 2006

Hi

I want to try and protect myself from my own stupidity. I have a number of sql databases, but one is LIVE. It is easy to drop tables but I want to set something (e.g. a password) which will help prevent me from dropping tables on the live database.

Any help/direction here would be appreciated.

View 8 Replies View Related

Prevent SP Compilation

Jul 20, 2005

Hi,I'm using SQL Server 2000 MSDE on a laptop running Windows XP.I have a couple of SP's that that quite some time to compile. So I waswondering: is there any way to have the database *not* recompile them everytime after a reboot?BOL says: "As a database is changed by such actions as adding indexes orchanging data in indexed columns, the original query plans used to accessits tables should be optimized again by recompiling them. This optimizationhappens automatically the first time a stored procedure is run afterMicrosoft® SQL ServerT 2000 is restarted."Now the SQL Server is restarted a lot, because laptops don't have endlessbatteries <g>Cheers,Bas

View 3 Replies View Related

Prevent Duplication On UPDATE

Apr 15, 2007

Hello
I noticed a spelling mistake in the data in a column of several tables, I used the following syntax to alter the spelling:
UPDATE [dbo].[Prod_Cat]     SET  [ProdName]=N'merseyside'    WHERE ProdName = 'mmserseyside'
The above code correctly updated the spelling error, but it also inserted a new row with the corrected data. 
So I found myself with two Identical rows containing the corrected information. I had to manually delete the extra row. Because if I had put in a DELETE statement, I would have then lost both rows.
What do I need to do to prevent this happening next time.  As I find that I need to update the names of some products, but I don't want to duplicate them.
Thanks
Lynn

View 3 Replies View Related

Prevent Duplicate Insert

Oct 18, 2007

How can I prevent duplicate inserts or entries to a table?
Thank you.
Note: I am using SQL Server and coding ASP.net pages in VB.

View 7 Replies View Related

What Are Sql Injection Attacks And How To Prevent?

Jan 24, 2004

this is a question I put in the sql community in microsoft, but havent be answered in full

------------

I am using dynamic sql to do a query with differents 'order' sentences and/or 'where' sentences depending on a variable I pass to the sp

ex:

create proc ex
@orden varchar(100)
@criterio varchar(100)

as
declare consulta varchar(4000)

set consulta=N'select pais from paises where '+@criterio' order by '+@orden

------------

I'd like to know it it uses 2 sp in the cache, as I read, the main sp and the query inside the variable of the dynamic sql. if so, as I imagine, then I suppose I have to do the main sp without any 'if' sentence to be the same sp, and so taking it from the cache and not recompile the sp

now, I have various 'if' sentences in the main sp (the caller of the dynamic sql) but I plan to remove them and do the 'if' by program -it is in asp.net-, so I suppose it is better because in this way the main sp is took from the cache, supposing this uses the cache different that the dynamic sql in the variable

what do u think? does the dynamic sql use 2 caches? if so, u think it is better to try to do the main sp same in all uses (no 'if' statements)?


-----

They told me this coding is not good (dynamic sql) because it can give control to the user?

I ask, how does it give control to use? what ar sql injection attack and how to prevent them?

I use dynamis sql because I have 150 queries to do, and thought dynamic sql is good

is it true that dynamic sql have to be recompiled in each execution? I suppose so only if the sql variable is different, right?

can u help me?

View 4 Replies View Related

How To Prevent SQL Injection Attacks

Apr 8, 2004

Hi,

On my site I have a simple textbox which is a keyword search, people type a keyword and then that looks in 3 colums of an SQL database and returns any matches

The code is basic i.e. SELECT * FROM Table WHERE Column1 LIKE %searcg%

There is no validation of what goes into the text box and I am worried about SQL injection, what can I do to minimize the risk

I have just tried the site and put in two single quotes as the search term, this crashed the script so I know I am vunerable.

Can anyone help, perhaps point me in the direction of furthur resources on the subject?

Thanks

Ben

View 3 Replies View Related

Prevent Duplicate Records

Jun 10, 2004

I have a web form that I use to insert data into a sql database. I want to know how to prevent inserting duplicate records into the database. Thanks.

View 6 Replies View Related

How Do I: Prevent Race Condition?

Mar 22, 2006

I want to be able to read and update a value in the database without entering a race condition.
For example:
User #1 reads a row from the database, changes a value then writes the value back.
User #2 reads the same row AFTER user #1 has read it, but BEFORE user #1 writes it back. User #2 then changes the value and writes it back, overwriting the value that user #1 wrote.
I thought I could do this with transactions, but it just makes user #2 wait until user #1 is done writing before user #2 can write. It doesn't stop user #2 from reading while user #1 has it out.
Does that make sense?

View 2 Replies View Related

How To Prevent Doublicate Name Entry

Feb 23, 2001

hi I have a table I need to have a process which prevent a user from entering a name value( company Name ) in a field. how can I do that .

Ahmed

View 2 Replies View Related

Prevent ODBC By USER/APP

Jun 20, 2003

Is there a way to monitor all ODBC connections to a MSSQL server and prevent a username/ODBC combination. My problem is that we have many frontends for viewing reports, but we managed them all and users are not allow to make their own connections. Some users now uses MSAccess over ODBC to draw their own reports - they have all the permissions as neededd by the other apps.

thanx

View 4 Replies View Related

Trigger To Prevent Duplicates

Aug 21, 2001

Hi all,

I'm writing a trigger to prevent duplicates. I know that this can be done through primary key or unique constraints but in the real world my uniqueness is defined by 8 columns which is too a big an index to maintain on the primary / unique key.

If I create a table with 2 columns

CREATE TABLE Table1
(CentreCHAR(10),
Month CHAR(3)
)


Then create a trigger to prevent duplicates

CREATE TRIGGER trigger_Check_Duplicates
ON Table1
FOR INSERT, UPDATE
AS
-- This trigger has been created to check that duplicate rows are not inserted into AudioVisual table.
DECLARE @IsDuplicate INTEGER
-- Check if row exists
SELECT @IsDuplicate = 1
FROM Inserted i, Table1 t
WHERE t.Centre = i.Centre
AND t.Month = i.Month
IF (@IsDuplicate = 1)
-- Display Error and then Rollback transaction
BEGIN
RAISERROR ('This row already exists in the table', 16, 1)
ROLLBACK TRANSACTION
END

Then insert a row into the new table (no other data is in there)

INSERT Table1
VALUES('0691040176','AUG')

I get the Trigger error message that the row already exists. Why is this the case? I though that Table 1 (target table) would show no entries as it has no data - it should be a before image of the table and the inserted table should be an after image.


Please help!!!


Thanks

Neill

View 1 Replies View Related

Prevent Trigger From Firing

Jan 13, 2004

Hi,

I have an update tigger on one of my tables.
I want to fire an Update SQL but somehow prevent trigger from firing.

Any Ideas..

View 5 Replies View Related

How To Prevent Displaying Names Of DB?

May 18, 2004

I get an issue, that is: Once i expand databases from SQL Enterprise Manager, it takes a long time to wait displaying all names of databases. How should i do to prevent displaying all names of databases?

View 1 Replies View Related

How To Prevent My Co-Workers From Accessing My SQL

May 17, 2008

Hi all

In our office we have simple Network (LAN) and for some reason in each workgroup Computer in this network we have installed SQL-Server 2000.

So far everything was ok but recently i noticed that my co-workers can easily access my Sql Server Instance through their Enterprise manager installed on their computers and then they can open my tables,view...and manipulate other objects.

for better illustration i have created a Flash Movie from this process , please check the following Link :

http://www.Parsisoftco.com/movie/Sql/Sql.htm

How can i prevent others from doing so?
Any help greatly Appreciated.
Kind Regards.

View 3 Replies View Related

Prevent From Copying Database ?

Jun 24, 2008

Hi,

How can prevent from stoping sql-server sevice and copying database,
note user has a access right to pc/server.

Kind Regards,
sasan.

View 4 Replies View Related

Trigger To Prevent Update If No Changes?

Jul 10, 2013

Is it possible to create a trigger to prevent update of a row if no signifiant colums are updated

Example : I need to allow the update only if colums a,b or c have changes ?

how it should works to keep or discard the update according to condition ?

View 1 Replies View Related

Prevent DELETE And/or UPDATE

Jul 23, 2005

Hi all!Are there any other way than using rights or Triggers to prevent aDELETE or an UPDATE on a specific column.The "problem" with rights is that they dont apply to all DB-usersThe "problem" with triggers is that they generate lots of extraSQL-codeI would like a solution something like below. If there are anyprimitives like this or other more neat solutions I would be glad toknowCREATE TABLE some_table NO DELETE/* ^^^^^^^^^*/(some_column SOME_TYPE NO UPDATE/* ^^^^^^^^^*/)For clarity, here is a trigger that currently solves the problemCREATE TRIGGER check_updateable_columns ON some_tableFOR UPDATEASIF UPDATE(some_column)RAISERROR(...)GOorCREATE TRIGGER delete_not_allowed ON some_tableINSTEAD OF DELETEASRAISERROR(...)GO

View 1 Replies View Related

How Can I Prevent That All Data Can Be Seen With Notepad?

Jan 10, 2007

Hello,

is there a way to say to SQL Server to make the data not readable?

Regards
Markus





View 16 Replies View Related

Prevent SQL/CLR Auto Deploy

Feb 13, 2008

Hi,

I have a solution (VS 2005, C#) that includes a SQL 2005 CLR project.

When I run the solution (in debug mode, and with a test harness in a different project in the solution set as the startup project) the IDE often wants to deploy my CRL assembly.

How do I tell it NOT to try to do the deployment?

(i.e. to only do it when I right-click the project and tell it to).

Cheers,

View 7 Replies View Related

Prevent Data Being Inserted Twice

Nov 14, 2006

I have a table with 3 columns: ID, Status, DateTime.

I created a stored procedure to insert a staus value for each ID. This will run every hour. The DateTime stores the time, date when the Status was inserted.

If the procedure was to be run a second time in hour window I do not want any Status to be inserted.

Note: that I cannot rely on the procedure being run at exactly the right time - if it was scheduled to run on the hour (i.e at 1:00, 2:00, 3 :00 etc) but didn't run until 1:20 it sould still be able to run at 2:00.

Does anyone know if there is anyway I can gaurd against this?

View 3 Replies View Related

Is That Possible To Prevent Modifying The Name Of An IDTSInputColumn90?

Dec 7, 2006

Hi,

I'm building a custom SSIS data flow component and I create myself input and output columns from a custom property of my pipeline component. That's why I don't want the user to modify the name of the input and output columns by using the advanced editor.

Is there a way either to make input/output column properties - at least the name - readonly or to override any PipelineComponent method to throw an exception like we do when we want to prevent the user from adding/removing input/output colulmns with methods like PipelineComponent.DeleteOutputColumn?

Thanks for your help,

David

View 2 Replies View Related

How To Prevent Ordering Of Records....

Jan 9, 2007

hello,

i use VC# and sql server 2005.

i create a table using the following command :

create table history(name varchar(20) primary key);

I insert records in a particular order.After all the records are inserted and when I try to retrieve the records, all of them are sorted in a ascending order.Is there any way to prevent them from being sorted.

pls reply asap.....

- Sweety

View 1 Replies View Related

Prevent MS Access To Connect

Sep 23, 2007

Hello

I'm facing a dilemma. I have quite a lot of users who need read access to data for analysis purposes. By granting them read access you also give them the option to connect to the database using MS Access which puts locks on the data.

First I thought of generating stored procedures for all tables within a database those users need to query but when users execute a stored procedure with the name of the table, they get a resultset of all the data. They cannot filter the data by for example using select top 100* from usp_table where name like '%worf%'

I'm sure I'm not the only one here but I don't see any alternative then grant them those rights.

Thanks!
Best Regards,
Worf

View 1 Replies View Related

Prevent Email Authentication

Aug 15, 2007

Due to the 'interesting' set up of AD, email etc we are unable to send reports as attachments.

It would appear that it is trying to authenticate the user to see if they have permission to view the report, as though they were viewing it in real time.

Is there any way to override this behaviour?

If not then all we can do is email them a link to the report, which sort of defeats the object.

View 2 Replies View Related

Is It Possible To Prevent Databases From Being Copied?

Mar 16, 2007

Hi,

We have a point of sale application (C# .NET 2.0) and a Sql Server 2005 database back end.

Our customers are concerned that employees could create a backup of the SQL Server database (or even of the MDF file) and use it to steel customer data.

Very often, the application is running on a single PC in a shop using Sql Server Express Edition 2005 under Windows XP. The users usually log on as local administrator. It's hard for us to force our customers to change their local security policies.

Ideally, I would like some form of security mechanism that prevents a backup from being restored on to another PC without either a password or some other form of authentication.

Is this possible?

Regards,



Sigol.

View 4 Replies View Related

How Can I Prevent From Inserting Duplicate Data?

Dec 31, 2006

 
I have a table storing only 2 FKs, let's say PID, MID
Is there any way that I can check distinct data before row is added to this table?
For example, current data is
PID MID------------100 2001100 2005101 3002102 1009102 7523102 2449
If my query is about to insert PID 100, MID 2001, since it's existing data, i don't want to add it. Can I use trigger to solve this issue?
 
Thanks.  
 
 

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved