SQL Server Performance Forum – Threads Archive
Problems with Instead Of Insert Trigger
I’m having a rather weird problem with an Instead Of Insert trigger. Its supposed to check to see if the record exists in the destination table, and if so update it, otherwise insert the row in the table. All pretty straightforward, but I’m getting weird behavior. If the destination table is empty, each row gets properly inserted. If there are records in the destination table, any that are found get properly updated, but no new records get inserted. Here is my table layout: CREATE TABLE [dbo].[TestProspect] ([ID] [int] IDENTITY (1, 1) NOT NULL ,
[ProspectNumber] [int] NULL ,
[iFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iLastname] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pLastName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] And here is my trigger: CREATE TRIGGER [tr_IMPRMSProspect] ON [dbo].[IMP_Prospect]
INSTEAD OF INSERT
AS –try to update the inserted record to an existing record
UPDATE p
SET
pLastName = ISNULL(i.LastName,”),
pFirstName = ISNULL(i.FirstName,”),
pPhone = ISNULL(i.Phone1,”),
Type = ‘UPDATE’
FROMTestProspect p INNER JOIN INSERTED i ON
p.iPhone = i.Phone1 AND
p.iLastName = i.LastName AND
p.iFirstName = i.FirstName
WHERE p.ProspectNumber < 4000000 or p.ProspectNumber >= 4999999 –if it isn’t in the table, insert it
INSERT INTO TestProspect
SELECT i.ProspectNumber, i.FirstName, i.LastName, i.Phone1, ”, ”, ”, ‘INSERT’
FROM INSERTED i
WHERE NOT EXISTS (
SELECT p.ProspectNumber
FROM TestProspect p INNER JOIN INSERTED i ON
p.iPhone = i.Phone1 AND
p.iLastName = i.LastName AND
p.iFirstName = i.FirstName) I’ve tried all kinds of things, like wrapping it in an if-else clause or rearranging the insert/update order, to no avail. Anybody see something that I’m missing? Thanks in advance.
perhaps because your firstname and lastname are CHAR type. so "a" != "a ". so try converting the first/lastnames to varchar in the where condition. …..
FROM TestProspect p INNER JOIN INSERTED i ON
p.iPhone = i.Phone1 AND
convert(varchar(40),p.iLastName) = convert(varchar(40),i.LastName) AND
convert(varchar(40),p.iFirstName) = convert(varchar(40),i.FirstName)) ***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
Thanks for the suggestion, but it didn’t work. After the initial insert, no new records get inserted in the table. This is the first time I’ve used an instead of trigger, and I’m wondering if I’m missing something.
Because the values in inserted table will not be equal to the values in TestProspect. The inserted will have new values that you are trying to insert. lets say your table TestProspect and IMP_Prospect tables have a record with values a ,b, 1111111 lets say you are updating the row with e, d, 2222222 based on your query, you are trying to match the new row with the old values in TesTProspect. Does that help? ***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
So because there are some records that exist in the destination table, the not exist clause will always fail. Hmm…. So, does anyone have any suggestions on how to do this? I need to merge all the rows from one table into another table, updating rows that match by phone and name, and inserting the ones that don’t match.
The NOT EXISTS clause of the INSERT query fails only because you are joining the target table in the subquery too. You need only a correlated subquery, without the join: INSERT INTO TestProspect
SELECT i.ProspectNumber, i.FirstName, i.LastName, i.Phone1, ”, ”, ”, ‘INSERT’
FROM INSERTED i
WHERE NOT EXISTS (
SELECT p.ProspectNumber
FROM TestProspect p
WHERE p.iPhone = i.Phone1 AND
p.iLastName = i.LastName AND
p.iFirstName = i.FirstName)
That was it. Much thanks!
]]>