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.

Problem with enterprise manager ( image attached )

Hi,
I have been having a problem for a couple of weeks, for some reason the
"Create Date" column of both the tables and SP views appear with weird
characters (as shown in the attached image) , This happen both on a local
server as well as on a remote server.
When I connect to the remote server using TS and see the same view the
"Create Date" column appear normally.
What could have happen? and even it's not a big issue I would like to know
how to solve it.
Thanks,
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of TransportationNop,
I haven't installed it, I have VS.NET 2005 installed , though.
Cheers,
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
news:F4BA581B-F664-469D-8C7B-301E72D72292@.microsoft.com...
> Did you by chance install YUKON Beta on the same box ?
>
> "Ignacio Machin ( .NET/ C# MVP )" wrote:
> > Hi,
> >
> > I have been having a problem for a couple of weeks, for some reason the
> > "Create Date" column of both the tables and SP views appear with weird
> > characters (as shown in the attached image) , This happen both on a
local
> > server as well as on a remote server.
> > When I connect to the remote server using TS and see the same view the
> > "Create Date" column appear normally.
> >
> > What could have happen? and even it's not a big issue I would like to
know
> > how to solve it.
> >
> >
> > Thanks,
> >
> > --
> > Ignacio Machin,
> > ignacio.machin AT dot.state.fl.us
> > Florida Department Of Transportation
> >
> >
> >

Problem with Enterprise Manager

When in em, I try to open a table and return all rows, I get a weird error:
"The query cannot be executed because some o fthe files are missing or not
registered."
Also, I cannot connect to the database with a dataadapter in vs (2003)
I can, however, execute an SQL in Query Analyzer.
I have reinstalled several times. Some of the Google articles suggest that
it might be MDAC. I cannot install MDAC either. It says that the operation
is not supported by the OS (xp Pro) The components are already installed.
Any input would be greatly appreciated.
Hi
A few questions!
Did you try and remove the software before re-installing?
Did you rung the MDAC component checker to see if it says your installation
is consistent?
Have you tried to apply any service packs?
You may wish to un-register/re-register all the dlls in {Installation
Directory}\80\Tools\Binn?
John
"walter1234" wrote:

> When in em, I try to open a table and return all rows, I get a weird error:
> "The query cannot be executed because some o fthe files are missing or not
> registered."
> Also, I cannot connect to the database with a dataadapter in vs (2003)
> I can, however, execute an SQL in Query Analyzer.
> I have reinstalled several times. Some of the Google articles suggest that
> it might be MDAC. I cannot install MDAC either. It says that the operation
> is not supported by the OS (xp Pro) The components are already installed.
> Any input would be greatly appreciated.
|||I just ran into this issue myself, I've tried the reinstall etc. I'm going
to try and un-register/re-register all of the DLLs in that directory but
there are 46 of them. Any suggestions on an easy way to do that?
I also tried the suggestions in Article ID: 315868.
Cory Blythe
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> A few questions!
> Did you try and remove the software before re-installing?
> Did you rung the MDAC component checker to see if it says your installation
> is consistent?
> Have you tried to apply any service packs?
> You may wish to un-register/re-register all the dlls in {Installation
> Directory}\80\Tools\Binn?
> John
> "walter1234" wrote:

Problem with Enterprise Manager

When in em, I try to open a table and return all rows, I get a weird error:
"The query cannot be executed because some o fthe files are missing or not
registered."
Also, I cannot connect to the database with a dataadapter in vs (2003)
I can, however, execute an SQL in Query Analyzer.
I have reinstalled several times. Some of the Google articles suggest that
it might be MDAC. I cannot install MDAC either. It says that the operation
is not supported by the OS (xp Pro) The components are already installed.
Any input would be greatly appreciated.Hi
A few questions!
Did you try and remove the software before re-installing?
Did you rung the MDAC component checker to see if it says your installation
is consistent?
Have you tried to apply any service packs?
You may wish to un-register/re-register all the dlls in {Installation
Directory}\80\Tools\Binn?
John
"walter1234" wrote:
> When in em, I try to open a table and return all rows, I get a weird error:
> "The query cannot be executed because some o fthe files are missing or not
> registered."
> Also, I cannot connect to the database with a dataadapter in vs (2003)
> I can, however, execute an SQL in Query Analyzer.
> I have reinstalled several times. Some of the Google articles suggest that
> it might be MDAC. I cannot install MDAC either. It says that the operation
> is not supported by the OS (xp Pro) The components are already installed.
> Any input would be greatly appreciated.|||I just ran into this issue myself, I've tried the reinstall etc. I'm going
to try and un-register/re-register all of the DLLs in that directory but
there are 46 of them. Any suggestions on an easy way to do that?
I also tried the suggestions in Article ID: 315868.
Cory Blythe
"John Bell" wrote:
> Hi
> A few questions!
> Did you try and remove the software before re-installing?
> Did you rung the MDAC component checker to see if it says your installation
> is consistent?
> Have you tried to apply any service packs?
> You may wish to un-register/re-register all the dlls in {Installation
> Directory}\80\Tools\Binn?
> John
> "walter1234" wrote:
> > When in em, I try to open a table and return all rows, I get a weird error:
> > "The query cannot be executed because some o fthe files are missing or not
> > registered."
> > Also, I cannot connect to the database with a dataadapter in vs (2003)
> > I can, however, execute an SQL in Query Analyzer.
> > I have reinstalled several times. Some of the Google articles suggest that
> > it might be MDAC. I cannot install MDAC either. It says that the operation
> > is not supported by the OS (xp Pro) The components are already installed.
> > Any input would be greatly appreciated.

Problem with Enterprise Manager

When in em, I try to open a table and return all rows, I get a weird error:
"The query cannot be executed because some o fthe files are missing or not
registered."
Also, I cannot connect to the database with a dataadapter in vs (2003)
I can, however, execute an SQL in Query Analyzer.
I have reinstalled several times. Some of the Google articles suggest that
it might be MDAC. I cannot install MDAC either. It says that the operation
is not supported by the OS (xp Pro) The components are already installed.
Any input would be greatly appreciated.Hi
A few questions!
Did you try and remove the software before re-installing?
Did you rung the MDAC component checker to see if it says your installation
is consistent?
Have you tried to apply any service packs?
You may wish to un-register/re-register all the dlls in {Installation
Directory}\80\Tools\Binn?
John
"walter1234" wrote:

> When in em, I try to open a table and return all rows, I get a weird error
:
> "The query cannot be executed because some o fthe files are missing or not
> registered."
> Also, I cannot connect to the database with a dataadapter in vs (2003)
> I can, however, execute an SQL in Query Analyzer.
> I have reinstalled several times. Some of the Google articles suggest tha
t
> it might be MDAC. I cannot install MDAC either. It says that the operatio
n
> is not supported by the OS (xp Pro) The components are already installed.
> Any input would be greatly appreciated.|||I just ran into this issue myself, I've tried the reinstall etc. I'm going
to try and un-register/re-register all of the DLLs in that directory but
there are 46 of them. Any suggestions on an easy way to do that?
I also tried the suggestions in Article ID: 315868.
Cory Blythe
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> A few questions!
> Did you try and remove the software before re-installing?
> Did you rung the MDAC component checker to see if it says your installatio
n
> is consistent?
> Have you tried to apply any service packs?
> You may wish to un-register/re-register all the dlls in {Installation
> Directory}\80\Tools\Binn?
> John
> "walter1234" wrote:
>