• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1130
  • Last Modified:

Stop trigger from inserting new row if ID already exists

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
0
Type25
Asked:
Type25
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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?
0
 
Type25Author Commented:
angelll, i'll be doing this after, for a really quick fix i need to put a trigger in place.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
do you have a primary key on the table?
0
 
Type25Author Commented:
Table is like this:

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

We're getting duplicate job no's.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
create trigger trg_dup_jobno
on yourtable
for insert
as
  if exists ( select null from yourtable t group by t.jobno having count(*) > 1 )
  begin
    rollback
  end
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now