[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Before Insert validation in SQL Server 2000

Posted on 2004-04-06
5
Medium Priority
?
2,424 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 70

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 400 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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.

656 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