INSERT TRIGGER - For you Guru's.

Why is it that this trigger fails to pick up the duplicate on an Insert from Select Statement?

INSTEAD OF INSERT
AS

BEGIN
SET NOCOUNT ON
-- CHECK FOR DUPLICATE PERSON
IF NOT EXISTS (SELECT HOMEPHONE
FROM
CBIZ_CONSULTANTS N,
INSERTED I
WHERE
N.HOMEPHONE = I.HOMEPHONE
)

INSERT INTO [dbo].[CBIZ_Consultants] (HOMEPHONE)
SELECT (HOMEPHONE)
FROM INSERTED

ELSE
PRINT 'SEND TO DUPLICATE TABLE'

END

FOR EXAMPLE IF I HAVE THE FOLLOWING RECORDS.

RECORDID PERSON HOMPHONE
1 JONN 415-555-1212
2 JON 415-555-1212

IF I DO AN INSERT MULTPLE TIMES
WHERE RECORDID = 1 IT WILL PRINT 'SEND TO DUPLICATE TABLE'

HOWEVER...

IF I DO AN INSERT INTO CBIZ_CONSULTANTS (RECORDID, PERSON, HOMEPHONE)
SELECT RECORDID, PERSON, HOMEPHONE FROM PERSON WHERE PERSON LIKE 'JON%'

THIS IS INSERT BOTH RECORDS INTO THE TABLE AND NOT PICK UP THE DUPLICATE?

David:

The way I understand you, at the beginning of the insert you have an empty CBIZ_CONSULTANTS table with the two records listed in the PERSON table. You attempt to insert these two records from the PERSON table into the CBIZ_CONSULTANTS table and you want your trigger to block the insert because the two records have the same HOMEPHONE entry.

In this case both records will be inserted because at trigger execution time the INSERTED pseudo table contains both entries and the CBIZ_Consultants table doesn't contain any entries. Under these conditions the IF part activates instead of the ELSE condition because there are NO records in the CBIZ_CONSULTANTS table; therefore, the NOT EXISTS statement is true. Remember: This trigger does not fire once for each row inserted; it fires once and loads both records inserted into the INSERTED pseudo table. This is a common misunderstanding related to triggers. Many triggers get incorrectly written such that they work with single record situations and fail to handle multiple records properly.

Suppose your first JON record is written to the CBIZ_CONSULTANTS table. Now you attempt to insert these two records:
(2, JON, 415-555-1212) and (3, JIM 416-555-1212). Now when your triggers fires you will get the 'SEND TO DUPLICATE TABLE' message and the (3, JIM 416-555-1212) record will NOT get inserted. (MAN this keyboard is the pits)

What would be more appropriate to hand the insert is something like:

INSERT INTO dbo.CBIZ_consultants (homephone)
SELECT homephone
FROM INSERTED i
WHERE NOT EXISTS
( SELECT 0 FROM CBIZ_consultants c
WHERE i.homephone = c.homephone
)

This will work properly for both single record and multi-record inserts.

Dave

|||Dave,

thank you very much for the reply I couldn't figure out why it was treating multi inserts different from single inserts. Very helpful!

I am left in somewhat of a jam with the above solution since I need to perform conditional logic based on NOT EXISTS.

I guess I could delete the rows from my source table that EXISTS in cbiz_consultants after the above query and treat that like my duplicate table...

Do you know if there are any other solutions than this?|||

David:

Much of the answer to your question lies in the data and what you want to do with it. If ultimately what you want to do is (1) update records with pre-existing matches and (2) insert records that have no pre-existing matches then you can put an

UPDATE
WHERE EXISTS

at the beginning of the trigger and follow it with an

INSERT
WHERE NOT EXISTS

However, this only works if the INSERTED pseudo table contains no records with the same key. In the example you cited an insert attempts to insert two records with the same home phone number. This requires a little more work. You now probably need to sequence your records and (1) insert the first record of the sequence and (2) update with the last record of the sequence. Also, does your table include a primary key?

Try doing a search here for INSTEAD OF TRIGGER; you should be able to get some better information that what I have shown. In a couple of the ones I just looked at Louis Davidson has some really good examples. These previous posts should be helpful.

Dave

|||Following Dave's suggestion, you could have a second insert into the duplicates table using [EXISTS] instead of [NOT EXISTS].