Problem with Intead 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'
FROM TestProspect 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.Verify that ANSI_NULLS was set ON when the trigger was created. Also, if
{iFirstName, iLastName, iPhone} is a key, then they shouldn't be nullable,
and you should create a unique constraint to ensure that you don't get any
duplicates.
"Brian Levine" <Brian Levine@.discussions.microsoft.com> wrote in message
news:462FFE90-CF5E-4575-B357-2B7D64E3B382@.microsoft.com...
> 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'
> FROM TestProspect 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.|||Try this for the insert portion:
INSERT INTO TestProspect
SELECT i.ProspectNumber, i.FirstName, i.LastName, i.Phone1, '', '', '',
'INSERT'
FROM INSERTED i
WHERE NOT EXISTS (
SELECT 1
FROM TestProspect p WHERE
p.iPhone = i.Phone1 AND
p.iLastName = i.LastName AND
p.iFirstName = i.FirstName)
"Brian Levine" wrote:

> 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 the
re
> 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'
> FROM TestProspect 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 th
at
> I'm missing?
> Thanks in advance.