Insert Trigger causes new record to disappear

Hello, I am using the following trigger. After I insert a new record a new
record into the table, the record disappears and a previous record will show
up as the last record. If I change it to just an update trigger, it works
fine. My question is why would an insert trigger cause this anomaly?
Thanks, Steven
CREATE TRIGGER tgrBusinessEmployee_i ON dbo.tblBusinessEmployee
FOR INSERT
AS
INSERT into tlogBusinessEmployee
(EmployeeID, BusinessLocation, LastName, FirstName, NickName,
WebPassword, Title, --CurriculumVitae,
ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
yn_ActiveEmployee, yn_PublishToWeb,
yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
LogProcess)
SELECT
Employee_ID, BusinessLocation, LastName, FirstName, NickName,
WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
yn_ActiveEmployee, yn_PublishToWeb,
yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
'InsertUpdate'
FROM insertedSteven,
Perhaps the insert into tlogBusinessEmployee inside the trigger is failing,
causing the insert into tblBusinessEmployee to fail.
If you add error handling to the trigger, you can detect an insert error and
debug (through a print statement) what the trigger is actually doing.
Alternatively, you can step through an insert in Query Analyzer or Visual
Studio, and debug the trigger.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Steven K0" <stroy@.api.com> wrote in message
news:efT2ywFnEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hello, I am using the following trigger. After I insert a new record a
new
> record into the table, the record disappears and a previous record will
show
> up as the last record. If I change it to just an update trigger, it works
> fine. My question is why would an insert trigger cause this anomaly?
> Thanks, Steven
> CREATE TRIGGER tgrBusinessEmployee_i ON dbo.tblBusinessEmployee
> FOR INSERT
> AS
> INSERT into tlogBusinessEmployee
> (EmployeeID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --CurriculumVitae,
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> LogProcess)
> SELECT
> Employee_ID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> 'InsertUpdate'
> FROM inserted
>|||Sounds like a problem with the client code. Are you using an identity
column as the PK for this table? Does the log table also have an identity
column?
"Steven K0" <stroy@.api.com> wrote in message
news:efT2ywFnEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hello, I am using the following trigger. After I insert a new record a
new
> record into the table, the record disappears and a previous record will
show
> up as the last record. If I change it to just an update trigger, it works
> fine. My question is why would an insert trigger cause this anomaly?
> Thanks, Steven
> CREATE TRIGGER tgrBusinessEmployee_i ON dbo.tblBusinessEmployee
> FOR INSERT
> AS
> INSERT into tlogBusinessEmployee
> (EmployeeID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --CurriculumVitae,
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> LogProcess)
> SELECT
> Employee_ID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> 'InsertUpdate'
> FROM inserted
>|||Scott,
The answer is yes to both questions, but I am not using the Employee_ID as
the PK and identity column in the log table:
CREATE TABLE [tblBusinessEmployee] (
[Employee_ID] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [pk_BusinessEmployee] PRIMARY KEY CLUSTERED
(
[Employee_ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [tlogBusinessEmployee] (
[EmployeeLog_ID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeID] [int] NULL ,
CONSTRAINT [pk_LogBusinessEmployee] PRIMARY KEY CLUSTERED
(
[EmployeeLog_ID]
) ON [PRIMARY]
) ON [PRIMARY]
INSERT into tlogBusinessEmployee
(EmployeeID, BusinessLocation, LastName, FirstName, NickName,
WebPassword, Title, --CurriculumVitae,
ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
yn_ActiveEmployee, yn_PublishToWeb,
yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
LogProcess)
SELECT
Employee_ID, BusinessLocation, LastName, FirstName, NickName,
WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
yn_ActiveEmployee, yn_PublishToWeb,
yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
'InsertUpdate'
FROM inserted
"Scott Morris" <bogus@.bogus.com> wrote in message
news:e9BhBwLnEHA.1236@.TK2MSFTNGP09.phx.gbl...
> Sounds like a problem with the client code. Are you using an identity
> column as the PK for this table? Does the log table also have an identity
> column?
> "Steven K0" <stroy@.api.com> wrote in message
> news:efT2ywFnEHA.3392@.TK2MSFTNGP15.phx.gbl...
> > Hello, I am using the following trigger. After I insert a new record a
> new
> > record into the table, the record disappears and a previous record will
> show
> > up as the last record. If I change it to just an update trigger, it
works
> > fine. My question is why would an insert trigger cause this anomaly?|||As I indicated, the problem is in the client application. Most likely, the
application is using @.@.identity to identify the ID of the inserted employee
row, which would be incorrect in this case (this is supported by the remark
that the application works correctly when the insert trigger logic is
removed). use scope_identity() if using sql2k. Use of the profiler may help
to identify issues with the conversation between the client and the server.
"Steven K" <skaper@.troop.com> wrote in message
news:e352JZOnEHA.952@.TK2MSFTNGP10.phx.gbl...
> Scott,
> The answer is yes to both questions, but I am not using the Employee_ID as
> the PK and identity column in the log table:
> CREATE TABLE [tblBusinessEmployee] (
> [Employee_ID] [int] IDENTITY (1, 1) NOT NULL ,
> CONSTRAINT [pk_BusinessEmployee] PRIMARY KEY CLUSTERED
> (
> [Employee_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
> CREATE TABLE [tlogBusinessEmployee] (
> [EmployeeLog_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [EmployeeID] [int] NULL ,
> CONSTRAINT [pk_LogBusinessEmployee] PRIMARY KEY CLUSTERED
> (
> [EmployeeLog_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>
>
> INSERT into tlogBusinessEmployee
> (EmployeeID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --CurriculumVitae,
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> LogProcess)
> SELECT
> Employee_ID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> 'InsertUpdate'
> FROM inserted
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:e9BhBwLnEHA.1236@.TK2MSFTNGP09.phx.gbl...
> > Sounds like a problem with the client code. Are you using an identity
> > column as the PK for this table? Does the log table also have an
identity
> > column?
> >
> > "Steven K0" <stroy@.api.com> wrote in message
> > news:efT2ywFnEHA.3392@.TK2MSFTNGP15.phx.gbl...
> > > Hello, I am using the following trigger. After I insert a new record
a
> > new
> > > record into the table, the record disappears and a previous record
will
> > show
> > > up as the last record. If I change it to just an update trigger, it
> works
> > > fine. My question is why would an insert trigger cause this anomaly?
>