Solved

Before Insert validation in SQL Server 2000

Posted on 2004-04-06
5
2,407 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:Scott Pletcher
ID: 10766507
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
ID: 10766542
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
ID: 10766561
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
ID: 10766600
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
ID: 10766602
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.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

860 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