?
Solved

SQL trigger for duplicate row test before insert- How?

Posted on 2007-10-04
3
Medium Priority
?
1,465 Views
Last Modified: 2008-01-09
Yet another 'PREVENTING DUPLICATE ROWS' database question...

Situation:

Using SQLServer2000

Two tables-TABLE1,TABLE2

TABLE1 already has a TRIGGER which will attempt to INSERT a row in TABLE2.
Each table has 5 fields that I want to compare in my duplicate row test.
I would like to use a TRIGGER (FOR INSERT) on TABLE2  to do the test.
If the row from TABLE1 already exists in TABLE2 then I want to rollback the INSERT transaction on TABLE2.


Best practices for this?
0
Comment
Question by:kkamm
3 Comments
 
LVL 15

Accepted Solution

by:
dbbishop earned 2000 total points
ID: 20018636
CREATE TRIGGER triggername FOR Table2 INSTEAD OF INSERT AS
IF NOT EXISTS (SELECT 1 FROM Table2 t JOIN INSERTED i ON t.col1 = i.col1 AND t.col2 = i.col2 AND t.col3 = i.col3 AND t.col4 = i.col4 AND t.col5 = i.col5)
BEGIN
  INSERT INTO Table2 (col1, col2, col3, col4, col5)
  SELECT col1, col2, col3, col4, col5 FROM INSERTED
END
0
 
LVL 14

Expert Comment

by:twoboats
ID: 20023096
or create a key on the 5 fields - as essentially, you are saying the 5 fields unquiely identify a row.
0
 
LVL 1

Author Comment

by:kkamm
ID: 20040755
Even though both suggestions will work I needed a trigger-based solution so I am awarding points to dbbishop.

Thanks for the help.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

862 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