I am trying to either write a trigger or a check constraint to prevent
duplicates in my table. There are two columns I need to look at for the
duplicates and only one combo value for both columns is allowed in the
table. For e.g.
Column Serial can have only one '123456' value with testresult value as
'PASS'. This serial can be in the table many times with any other combo
so for e.g.
The table could contain 100 entries for serial column value '123456'
with testresult value 'FAIL', 'PENDING' etc.
** TESTED SCHEMA BELOW **
-- create table
CREATE TABLE bstresult
(ID int IDENTITY (1, 1) NOT NULL ,
serial char (10) NULL ,
testresult char (10) NULL
)
-- Insert valid values
insert into bstresult values ('123456','PASS')
insert into bstresult values ('123456','FAIL')
insert into bstresult values ('123456','FAIL')
insert into bstresult values ('123456','PENDING')
-- insert invalid value this should fail
insert into bstresult values ('123456','PASS')
If I simply create a unique constraint on both columns it will not
allow the FAIL combo or PENDING combo with the same serial which I need
to allow.
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.
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.
Hi,I need to enforce that a table does not have "duplicates" for aspecific status type in the table.If the column "STATUS" = 2, then there can not be more than one rowwith a specific "ID" column.I can not use a unique key constraint because duplicate values for thiscombo of columns is valid for the status = 1.Just when the status = 2, there can not be any other rows with the sameID and status = 2.Any ideas?-Paul
I am trying to create table with following SQL script:
Code Snippet
create table Projects( ID smallint identity (0, 1) constraint PK_Projects primary key, Name nvarchar (255) constraint NN_Prj_Name not null, Creator nvarchar (255), CreateDate datetime );
When I execute this script I get following error message:
Error source: SQL Server Compact ADO.NET Data Provider Error message: Named Constraint is not supported for this type of constraint. [ Constraint Name = NN_Prj_Name ]
I looked in the SQL Server Books Online and saw following:
CREATE TABLE (SQL Server Compact) ... < column_constraint > ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ PRIMARY KEY | UNIQUE ] | REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ]
As I understand according to documentation named constraints should be supported, however error message says opposite. I can rephrase SQL script by removing named constraint.
Code Snippet
create table Projects( ID smallint identity (0, 1) constraint PK_Projects primary key, Name nvarchar (255) not null, Creator nvarchar (255), CreateDate datetime ); This script executes correctly, however I want named constraints and this does not satisfy me.
I am trying to create a check constraint that prevents entry of a date range that overlaps any other date range in the table. The below example script results in the error message: The INSERT statement conflicted with the CHECK constraint "ck_dt_overlap". what I am doing wrong?
CREATE TABLE t1 (d1 date,d2 date) go create function dbo.dt_overlap (@d1 date, @d2 date) RETURNS BIT AS BEGIN Â IF EXISTS (SELECT 1 from t1 where d1 between @d1 and @d2 or d2 between @d1 and @d2) Â Â RETURN 1 Â Â RETURN 0 END go alter table t1 add constraint ck_dt_overlap CHECK (dbo.dt_overlap(d1,d2)=0) go insert into t1 values ('2015-01-01','2015-02-01') insert into t1 values ('2015-01-15','2015-02-15')
--BOTH inserts result in the following message:Â The INSERT statement conflicted with the CHECK constraint "ck_dt_overlap".
I have a patient record and emergency contact information. I need to find duplicate phone numbers in emergency contact table based on relationship type (RelationType0 between emergency contact and patient. For example, if patient was a child and has mother listed twice with same number, I need to filter these records. The case would be true if there was a father listed, in any cases there should be one father or one mother listed for patient regardless. The link between patient and emergency contact is person_gu. If two siblings linked to same person_gu, there should be still one emergency contact listed.
Below is the schema structure:
Person_Info: PersonID, Person Info contains everyone (patient, vistor, Emergecy contact) First and last names Patient_Info: PatientID, table contains patient ID and other information Patient_PersonRelation: Person_ID, patientID, RelationType Address: Contains address of all person and patient (key PersonID) Phone: Contains phone # of everyone (key is personID)
The goal to find matching phone for same person based on relationship type (If siblings, then only list one record for parent because the matching phones are not duplicates).
I have a table with 22 million Business records. I can see that there are duplicates when I group by BusinessName and Address and Phone. I'd like to place only the duplicates into a table, with a ranking, oldest business key gets a ranking of 1.
As a bonus I'd like each group to have a distinct group name (although not necessary, just want to know how to do this)
Later after I run more verifications to make sure these are not referenced elsewhere I'll delete everything with a matchRank > 1 out of the main Business table.
DROP TABLE [dbo].[TestBusiness]; GO CREATE TABLE [dbo].[TestBusiness]( [Business_pk] INT IDENTITY(1,1) NOT NULL, [BusinessName] VARCHAR (200) NOT NULL, [Address] VARCHAR(MAX) NOT NULL,
We are using SQL CE 3.5 on tablet PCs, that synchs with our host SQL 2005 Server using Microsoft Synchronization Services. On the tablets, when inserting a record, we get the following error: A duplicate value cannot be inserted into a unique index. [ Table name = refRegTitle,Constraint name = PK_refRegTitle But the only PK on this table is RegTitleID.
The table structure is: [RegTitleID] [int] IDENTITY(1,1) NOT NULL, [RegTitleNumber] [int] NOT NULL, [RegTitleDescription] [varchar](200) NOT NULL, [FacilityTypeID] [int] NOT NULL, [Active] [bit] NOT NULL,
The problem occurs when a Title Number is inserted and a record with that number already exists. There is no unique constraint on Title Number. Has anyone else experienced this?
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
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
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.
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 ?
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.
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
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
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?
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?
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?
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.
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?
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.
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
I am doing customization for microsoft POS. I manually added a recordto 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 canlock this single row at database level to prevent it from being deletedor changed by user? I am using SQL 2000 and vb.net.Thanks.Leanne
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).
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?