Solved

Before Insert validation in SQL Server 2000

Posted on 2004-04-06
5
2,406 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

809 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