Stop trigger from inserting new row if ID already exists

Posted on 2007-11-14
Last Modified: 2008-02-01
For some reason we are getting duplicate values inserted into a table with the same foreign key.

As an interim solution i need to stop this happening via a trigger.

Could someone knock up a quick trigger which checks for an ID and if it exists it cancels the insert statement.

Note: The insert statement isn't inside the trigger
Question by:Type25
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
  • 2
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20278554
maybe putting a unique index (or primary key) on the ID field would even be better, as it would give you the indication where/how these duplicates get inserted...

what do you think?
ie, how do you "generate" those ID values?

Author Comment

ID: 20278589
angelll, i'll be doing this after, for a really quick fix i need to put a trigger in place.
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20278613
do you have a primary key on the table?

Author Comment

ID: 20278621
Table is like this:

EOWID int primary key
JobNo foreign key
other fields.....

We're getting duplicate job no's.
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 500 total points
ID: 20278660
create trigger trg_dup_jobno
on yourtable
for insert
  if exists ( select null from yourtable t group by t.jobno having count(*) > 1 )

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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 ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

733 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