Take the 2-minute tour ×

I have created triggers for INSERT and UPDATE separately. The trigger is going to insert a row in Schema2 when an insert is made in Schema1. Tables:

  • Schema1.Temp1
  • Schema2.Temp2

The trigger creation is successful.

But when I am inserting data in Temp1, it is giving me error for Temp2 -- duplicate key. Temp2 has constraints for two other tables. What can be causing this, and how can it be resolved?

share|improve this question
I don't think that this issue has anything to do with triggers. You're just inserting duplicate values. Error show which constraint is violated. –  Alex Aza Jun 1 '11 at 0:43
Please update with the CREATE TABLE statement for Temp2. Stopping duplicate data is a Good Thing (tm) - sounds like you're setting up a "poor mans" replication, when there's better alternatives depending on what you really want these triggers to do. –  OMG Ponies Jun 1 '11 at 1:10
Can you show us the structure of Table2 (columns, their datatypes, what indices you have on the table) and the statement that your trigger uses to insert the data, and the complete and exact error message you're getting?? –  marc_s Jun 1 '11 at 4:46
add comment

1 Answer

When your trigger is called try to write on Table2 (as you've told).

Peraphs you haven't written INSERT query using a condition on existence of your Temp1 row in Temp2.

Your query must be of this type:

INSERT INTO Table2 (field list)
SELECT field list
FROM inserted
WHERE NOT EXISTS(SELECT 'key' in Table2 t2 where t2.id = inserted.field_of_key)

In this way you prevent duplicate key, so if you want to update your table2 in insert too, you can write an UPDATE stament when that key is already existing.

Tell me if it's ok

share|improve this answer
add comment

Your Answer


