• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1123
  • 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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

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