Very Computer

Creating a trigger to insert a row into a table whenever
a row is inserted or updated in triggering table causes
insert statements into a third table to fail.  

I have an application that inserts or updates a row in
one database table followed by one or more inserts into a
second table.  This process has been working for several
months without failute.  I created a trigger insert a row
into a third table whenever the first table has a row
inserted or updated.  The new trigger functioned
correctly inserting records into the new table.  However,
all inserts into the secondary table are no longer
successful.  

Does anyone know about this problem and what the
resolution to this problem would be?

Can you help us out by posting your table and trigger
creation SQL?

Quote:>-----Original Message-----
>Creating a trigger to insert a row into a table whenever
>a row is inserted or updated in triggering table causes
>insert statements into a third table to fail.  

>I have an application that inserts or updates a row in
>one database table followed by one or more inserts into
a
>second table.  This process has been working for several
>months without failute.  I created a trigger insert a
row
>into a third table whenever the first table has a row
>inserted or updated.  The new trigger functioned
>correctly inserting records into the new table.  
However,
>all inserts into the secondary table are no longer
>successful.  

>Does anyone know about this problem and what the
>resolution to this problem would be?
>.

The SQL to create the new table is as follows:

CREATE TABLE [Registry_event_Ron] (
        [Custcode] [varchar] (12)  NULL ,
        [ActualTimestamp] [datetime] NULL
) ON [PRIMARY]

The SQL to create the trigger is as follows:

CREATE TRIGGER [UpdateTime] ON [dbo].[REGISTRY_EVENT]
FOR INSERT, UPDATE
AS insert into Registry_event_ron select custcode,
getutcdate() from deleted

Quote:>-----Original Message-----
>Can you help us out by posting your table and trigger
>creation SQL?

>>-----Original Message-----
>>Creating a trigger to insert a row into a table
whenever
>>a row is inserted or updated in triggering table causes
>>insert statements into a third table to fail.  

>>I have an application that inserts or updates a row in
>>one database table followed by one or more inserts into
>a
>>second table.  This process has been working for
several
>>months without failute.  I created a trigger insert a
>row
>>into a third table whenever the first table has a row
>>inserted or updated.  The new trigger functioned
>>correctly inserting records into the new table.  
>However,
>>all inserts into the secondary table are no longer
>>successful.  

>>Does anyone know about this problem and what the
>>resolution to this problem would be?
>>.

>.

The only problem I see with your code is when you do an
insert there is no deleted information. You could either
create two triggers, one for insert, one for update od
perform some logic in your trigger to determine if it is
an update or insert. If you are using triggers you
probably already figured this out so I may not be getting
the drift of your question.

CREATE TABLE [Registry_event_Ron] (
        [Custcode] [varchar] (12)  NULL ,
        [ActualTimestamp] [datetime] NULL
) ON [PRIMARY]

CREATE TABLE [Registry_event] (
        [Custcode] [varchar] (12)  NULL ,
        [ActualTimestamp] [datetime] NULL
) ON [PRIMARY]

--The SQL to create the trigger is as follows:

create TRIGGER [UpdateTime] ON [dbo].[REGISTRY_EVENT]
FOR UPDATE
AS insert into Registry_event_ron select custcode,
getutcdate() from deleted

CREATE TRIGGER [insert_Time] ON [dbo].[REGISTRY_EVENT]
FOR insert
AS
insert into Registry_event_ron select custcode,
getutcdate() from inserted

insert into registry_event (custcode,actualtimestamp)
values ('110',getutcdate())
select * from registry_event
union
select * from registry_event_ron

update registry_event set custcode = '120'

select * from registry_event
union
select * from registry_event_ron

HTH

Quote:>-----Original Message-----
>The SQL to create the new table is as follows:

>CREATE TABLE [Registry_event_Ron] (
>    [Custcode] [varchar] (12)  NULL ,
>    [ActualTimestamp] [datetime] NULL
>) ON [PRIMARY]

>The SQL to create the trigger is as follows:

>CREATE TRIGGER [UpdateTime] ON [dbo].[REGISTRY_EVENT]
>FOR INSERT, UPDATE
>AS insert into Registry_event_ron select custcode,
>getutcdate() from deleted

>>-----Original Message-----
>>Can you help us out by posting your table and trigger
>>creation SQL?

>>>-----Original Message-----
>>>Creating a trigger to insert a row into a table
>whenever
>>>a row is inserted or updated in triggering table
causes
>>>insert statements into a third table to fail.  

>>>I have an application that inserts or updates a row in
>>>one database table followed by one or more inserts
into
>>a
>>>second table.  This process has been working for
>several
>>>months without failute.  I created a trigger insert a
>>row
>>>into a third table whenever the first table has a row
>>>inserted or updated.  The new trigger functioned
>>>correctly inserting records into the new table.  
>>However,
>>>all inserts into the secondary table are no longer
>>>successful.  

>>>Does anyone know about this problem and what the
>>>resolution to this problem would be?
>>>.

>>.

>.

The problem I am looking into is not with the trigger
execution but the effect of the trigger execution on
other database activity.  In normal operation the table
(registry_event) has a row inserted into it or an
existing row is updated.  Then rows are inserted into
another table (registry_person).  When this trigger is
activated the rows are inserted or updated in
(registry_event) but the rows do not get inserted into
registry_person.  When the trigger is removed the system
returns to normal operation.  This appears to me to be a
major internal problem in Sql Server 2000 and its use of
triggers.  I am just trying to find out if anyone else
has the same problem or is aware of this problekm.

Quote:>-----Original Message-----
>The only problem I see with your code is when you do an
>insert there is no deleted information. You could either
>create two triggers, one for insert, one for update od
>perform some logic in your trigger to determine if it is
>an update or insert. If you are using triggers you
>probably already figured this out so I may not be
getting
>the drift of your question.

>CREATE TABLE [Registry_event_Ron] (
>    [Custcode] [varchar] (12)  NULL ,
>    [ActualTimestamp] [datetime] NULL
>) ON [PRIMARY]

>CREATE TABLE [Registry_event] (
>    [Custcode] [varchar] (12)  NULL ,
>    [ActualTimestamp] [datetime] NULL
>) ON [PRIMARY]

>--The SQL to create the trigger is as follows:

>create TRIGGER [UpdateTime] ON [dbo].[REGISTRY_EVENT]
>FOR UPDATE
>AS insert into Registry_event_ron select custcode,
>getutcdate() from deleted

>CREATE TRIGGER [insert_Time] ON [dbo].[REGISTRY_EVENT]
>FOR insert
>AS
>insert into Registry_event_ron select custcode,
>getutcdate() from inserted

>insert into registry_event (custcode,actualtimestamp)
>values ('110',getutcdate())
>select * from registry_event
>union
>select * from registry_event_ron

>update registry_event set custcode = '120'

>select * from registry_event
>union
>select * from registry_event_ron

>HTH

>>-----Original Message-----
>>The SQL to create the new table is as follows:

>>CREATE TABLE [Registry_event_Ron] (
>>        [Custcode] [varchar] (12)  NULL ,
>>        [ActualTimestamp] [datetime] NULL
>>) ON [PRIMARY]

>>The SQL to create the trigger is as follows:

>>CREATE TRIGGER [UpdateTime] ON [dbo].[REGISTRY_EVENT]
>>FOR INSERT, UPDATE
>>AS insert into Registry_event_ron select custcode,
>>getutcdate() from deleted

>>>-----Original Message-----
>>>Can you help us out by posting your table and trigger
>>>creation SQL?

>>>>-----Original Message-----
>>>>Creating a trigger to insert a row into a table
>>whenever
>>>>a row is inserted or updated in triggering table
>causes
>>>>insert statements into a third table to fail.  

>>>>I have an application that inserts or updates a row
in
>>>>one database table followed by one or more inserts
>into
>>>a
>>>>second table.  This process has been working for
>>several
>>>>months without failute.  I created a trigger insert a
>>>row
>>>>into a third table whenever the first table has a row
>>>>inserted or updated.  The new trigger functioned
>>>>correctly inserting records into the new table.  
>>>However,
>>>>all inserts into the secondary table are no longer
>>>>successful.  

>>>>Does anyone know about this problem and what the
>>>>resolution to this problem would be?
>>>>.

>>>.

>>.

>.