Solved

Before Insert validation in SQL Server 2000

Posted on 2004-04-06
5
2,400 Views
Last Modified: 2007-12-19
Experts:

I have to do a data validation on DB level to prevent the duplicate primary key issue.

I am thinking to implement this by a trigger.

My code is as following:

CREATE TRIGGER  VALIDATION ON [dbo].[AAA]
FOR INSERT
AS
DECLARE @A INT,@B INT, @C INT

SELECT @A=A,@B=B,@C=C
from INSERTED

IF NOT EXISTS (select * from AAA Where  @A=A AND @B=B AND @C=C)
      BEGIN
      INSERT INTO AAAA
      
      END
ELSE
      BEGIN
                :
      END

However,this validation needs to be done "Before Insert" which is a feature of Oracle. It seems that there no counterpart function available in SQL Server 2000 ( the one I am working with).

Could some one kindly provide a solution for this?

Thank you in advance.






 
0
Comment
Question by:ca_roland
  • 3
5 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
As you've noted, there is no "BEFORE INSERT" trigger in SQL Server.  You would have to write your own SP and call it before the INSERT.  That will be a pain for one row, a super big pain for multiple rows.

Btw, there is also no "FOR EACH ROW" trigger in SQL Server.  All triggers must be written to handle multiple modifications in one firing of the trigger.
0
 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
Wouldn't an "INSTEAD OF" trigger be very suitable in this case ?
It's available in SQL 2000
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 100 total points
Comment Utility
CREATE TRIGGER  VALIDATION ON [dbo].[AAA]
INSTEAD OF INSERT
AS
INSERT INTO AAAA(A,B,C)
select A,B,C
from inserted a
where not exists (select 1 from AAAA where A=a.A and B = a.B and C = a.C)
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
For a demo of instead of triggers (this is on a view in the example given - with code): http://www.danielb.connectfree.co.uk/Links.htm

To perform your trigger by inserting every row at a time you would require:
PSUDEO SQL:
CREATE TRIGGER  VALIDATION ON [dbo].[AAA]
FOR INSERT
AS
DECLARE @A INT,@B INT, @C INT, @MIN_PK_VALUE <suitable_datatype for holding pkvalues>

SET @MIN_PK_VALUE = (SELECT TOP 1 PK_FIELD from INSERTED ORDER BY PK_FIELD)
SELECT TOP 1 @A=A,@B=B,@C=C
from INSERTED
where PK_FIELD=@MIN_PK_VALUE
ORDER BY PK_FIELD(s)

And loop or alternativly a multiple row option is:
----------------------------------
IF NOT EXISTS (select top 1 * from AAA join inserted I on I.A = AAA.A AND I.B = AAA.B and AAA.C = I.C )
     BEGIN
     INSERT INTO AAAA
     
     END
ELSE
     BEGIN
                :
     END
0
 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
Just made a test, it works like a charm !
In the code above, AAAA should be AAA I guess


CREATE TRIGGER  VALIDATION ON [dbo].[AAA]
INSTEAD OF INSERT
AS
INSERT INTO AAA(A,B,C)
select A,B,C
from inserted a
where not exists (select 1 from AAA where A=a.A and B = a.B and C = a.C)

go

Hilaire
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 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

11 Experts available now in Live!

Get 1:1 Help Now