"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 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.

"Brian Levine" <Brian Lev***@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.