[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Before Insert validation in SQL Server 2000

Posted on 2004-04-06
5
Medium Priority
?
2,426 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 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.
Suggested Courses

834 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