Need an Example

[Eric]

I have two servers. I have a server link, and can see both data bases.

When data is inserted in one table with the field called "Status" is = to the value of "pending" on server1, I would like a trigger to fire and populate a table on server 2 with this record .

Can anybody supply example code fo a trigger that would accomplish this task?

Thanks

Eric

[MAK]

CREATE TRIGGER PRODUCTS_INSERT_TRIGGER ON PRODUCTS FOR INSERT AS
declare @status varchar(20)
select @status = status from inserted

if ltrim(rtrim(@status)) = "Pending"
begin
insert into server2.database.dbo.products select * from inserted
end



------------
Eric at 8/6/2002 2:53:27 PM

I have two servers. I have a server link, and can see both data bases.

When data is inserted in one table with the field called "Status" is = to the value of "pending" on server1, I would like a trigger to fire and populate a table on server 2 with this record .

Can anybody supply example code fo a trigger that would accomplish this task?

Thanks

Eric

[Eric]

Thanks, but there is a little more. Fields are comming from mutilpe tables
from server1. All the tables have the same Key field. call it field1

So from server1, I'm getting field1 (key) field2 field3 from table 1
field4 field5 field6 from table 2, field 7 field8 field9
from table3

then populate on server2, table1 that has all the fields above, using a tirigger on one of the tables on server1.

Can I do this, using a trigger. I wish I could use a triger on a view in sql server 7, would make things easier.

Thanks again
Eric


------------
MAK at 8/7/2002 9:16:50 AM

CREATE TRIGGER PRODUCTS_INSERT_TRIGGER ON PRODUCTS FOR INSERT AS
declare @status varchar(20)
select @status = status from inserted

if ltrim(rtrim(@status)) = "Pending"
begin
insert into server2.database.dbo.products select * from inserted
end



------------
Eric at 8/6/2002 2:53:27 PM

I have two servers. I have a server link, and can see both data bases.

When data is inserted in one table with the field called "Status" is = to the value of "pending" on server1, I would like a trigger to fire and populate a table on server 2 with this record .

Can anybody supply example code fo a trigger that would accomplish this task?

Thanks

Eric

[MAK]

CREATE TRIGGER PRODUCTS_INSERT_TRIGGER ON PRODUCTS FOR INSERT AS
declare @status varchar(20)
select @status = status from inserted i, table1j where i.id = j.id


if ltrim(rtrim(@status)) = "Pending"
begin
insert into server2.database.dbo.products select * from inserted
end



------------
Eric at 8/7/2002 2:53:41 PM

Thanks, but there is a little more. Fields are comming from mutilpe tables
from server1. All the tables have the same Key field. call it field1

So from server1, I'm getting field1 (key) field2 field3 from table 1
field4 field5 field6 from table 2, field 7 field8 field9
from table3

then populate on server2, table1 that has all the fields above, using a tirigger on one of the tables on server1.

Can I do this, using a trigger. I wish I could use a triger on a view in sql server 7, would make things easier.

Thanks again
Eric


------------
MAK at 8/7/2002 9:16:50 AM

CREATE TRIGGER PRODUCTS_INSERT_TRIGGER ON PRODUCTS FOR INSERT AS
declare @status varchar(20)
select @status = status from inserted

if ltrim(rtrim(@status)) = "Pending"
begin
insert into server2.database.dbo.products select * from inserted
end



------------
Eric at 8/6/2002 2:53:27 PM

I have two servers. I have a server link, and can see both data bases.

When data is inserted in one table with the field called "Status" is = to the value of "pending" on server1, I would like a trigger to fire and populate a table on server 2 with this record .

Can anybody supply example code fo a trigger that would accomplish this task?

Thanks

Eric

[Eric]

just to keep it simple i created this trigger to test.
CREATE TRIGGER TRG_road_call_incident ON dbo.road_call_incident_form
FOR INSERT AS



Begin
Insert into
EAMSDEV.Satcomdb.dbo.Road_Call
select Incident from inserted
End

I then went to test it. Using a custom aplication that populates table (and I tried going strait to the table to insert data through Enterprise manager) I get this message.

SQL Code 7312 encountered

SQLSTATE = 01000
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message: Cannot start more transactions on this session.]

No changes made to database.

INSERT INTO dbo.road_call_incident_form ( incident_log_id, incident, issuer_of_report, direction_code_id, given_to, description, maintenance, given_to_2, disposition, meet, time, location ) VALUES ( 0, 'RC0208070024', 'Test', 0, ' ', ' ', ' ', ' ', ' ', 0, '1997-01-01 12:00:00.000', ' ' )

Whats the deal with "Cannot start more transactions on this session"

Thanks

Eric






------------
MAK at 8/7/2002 3:40:29 PM

CREATE TRIGGER PRODUCTS_INSERT_TRIGGER ON PRODUCTS FOR INSERT AS
declare @status varchar(20)
select @status = status from inserted i, table1j where i.id = j.id


if ltrim(rtrim(@status)) = "Pending"
begin
insert into server2.database.dbo.products select * from inserted
end



------------
Eric at 8/7/2002 2:53:41 PM

Thanks, but there is a little more. Fields are comming from mutilpe tables
from server1. All the tables have the same Key field. call it field1

So from server1, I'm getting field1 (key) field2 field3 from table 1
field4 field5 field6 from table 2, field 7 field8 field9
from table3

then populate on server2, table1 that has all the fields above, using a tirigger on one of the tables on server1.

Can I do this, using a trigger. I wish I could use a triger on a view in sql server 7, would make things easier.

Thanks again
Eric


------------
MAK at 8/7/2002 9:16:50 AM

CREATE TRIGGER PRODUCTS_INSERT_TRIGGER ON PRODUCTS FOR INSERT AS
declare @status varchar(20)
select @status = status from inserted

if ltrim(rtrim(@status)) = "Pending"
begin
insert into server2.database.dbo.products select * from inserted
end



------------
Eric at 8/6/2002 2:53:27 PM

I have two servers. I have a server link, and can see both data bases.

When data is inserted in one table with the field called "Status" is = to the value of "pending" on server1, I would like a trigger to fire and populate a table on server 2 with this record .

Can anybody supply example code fo a trigger that would accomplish this task?

Thanks

Eric

[MAK]

add this statement in the insert as first statement

set xact_abort on

make sure msdtc is started on your server.





------------
Eric at 8/7/2002 8:16:38 PM

just to keep it simple i created this trigger to test.
CREATE TRIGGER TRG_road_call_incident ON dbo.road_call_incident_form
FOR INSERT AS



Begin
Insert into
EAMSDEV.Satcomdb.dbo.Road_Call
select Incident from inserted
End

I then went to test it. Using a custom aplication that populates table (and I tried going strait to the table to insert data through Enterprise manager) I get this message.

SQL Code 7312 encountered

SQLSTATE = 01000
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message: Cannot start more transactions on this session.]

No changes made to database.

INSERT INTO dbo.road_call_incident_form ( incident_log_id, incident, issuer_of_report, direction_code_id, given_to, description, maintenance, given_to_2, disposition, meet, time, location ) VALUES ( 0, 'RC0208070024', 'Test', 0, ' ', ' ', ' ', ' ', ' ', 0, '1997-01-01 12:00:00.000', ' ' )

Whats the deal with "Cannot start more transactions on this session"

Thanks

Eric






------------
MAK at 8/7/2002 3:40:29 PM

CREATE TRIGGER PRODUCTS_INSERT_TRIGGER ON PRODUCTS FOR INSERT AS
declare @status varchar(20)
select @status = status from inserted i, table1j where i.id = j.id


if ltrim(rtrim(@status)) = "Pending"
begin
insert into server2.database.dbo.products select * from inserted
end



------------
Eric at 8/7/2002 2:53:41 PM

Thanks, but there is a little more. Fields are comming from mutilpe tables
from server1. All the tables have the same Key field. call it field1

So from server1, I'm getting field1 (key) field2 field3 from table 1
field4 field5 field6 from table 2, field 7 field8 field9
from table3

then populate on server2, table1 that has all the fields above, using a tirigger on one of the tables on server1.

Can I do this, using a trigger. I wish I could use a triger on a view in sql server 7, would make things easier.

Thanks again
Eric


------------
MAK at 8/7/2002 9:16:50 AM

CREATE TRIGGER PRODUCTS_INSERT_TRIGGER ON PRODUCTS FOR INSERT AS
declare @status varchar(20)
select @status = status from inserted

if ltrim(rtrim(@status)) = "Pending"
begin
insert into server2.database.dbo.products select * from inserted
end



------------
Eric at 8/6/2002 2:53:27 PM

I have two servers. I have a server link, and can see both data bases.

When data is inserted in one table with the field called "Status" is = to the value of "pending" on server1, I would like a trigger to fire and populate a table on server 2 with this record .

Can anybody supply example code fo a trigger that would accomplish this task?

Thanks

Eric