I have a table that records data and I want to be able to copy a few of the feilds to another table in a second database whenever the data is inserted or updated. I assume the way to do that is thru the use of a trigger, but have no clue how to begin.

For example:

db #1 Table User has fields;
id, name, address, zip, password, date

db #2 table n_user has fields;
id, name, password

Hope thats enough information to get some assistance...

use [Your active DB]
GO
create trigger [TR_User(,I,U,)]
on dbo.[user]
for insert,update
as begin
insert [Your history DB].dbo.[n_user] ([id], [name], [password])
select [id], [name], [password]
from inserted
end

Thanks for you help.
I made the trigger but get this error on update of one field value.

Cannot insert explicit value for identity column in table 'FORUM_MEMBERS' when IDENTITY_INSERT is set to OFF.

Any clue????

Difference between history and active table is difference between picture and movie.
You cannot use the same PK in both tables.

In [Your history DB].dbo.[n_user] table:
1,Add [oldid] column with datatype of [user].[id] column, PK will be on [n_user].[id] column.
2,Also consider using larger datatype on [n_user].[id] column, if [user] table is modified frequently.

3,Modify trigger:

use [Your active DB]
GO
alter trigger [TR_User(,I,U,)]
on dbo.[user]
for insert,update
as begin
insert [Your history DB].dbo.[n_user] ([oldid], [name], [password])
select [id], [name], [password]
from inserted
end
GO
delete [Your history DB].dbo.[n_user]
declare @x int
update dbo.[user] set @x=1

4...This will help sometimes
Adding FingerPrint timestamp column NOT NULL
Adding CreatedDate datetime column NOT NULL with default getdate().

5,Securing history table:

use [Your history DB]
GO
Deny insert,update,delete,references on dbo.[n_user] to public

I see parts in the code for insert and delete.... what about update?

Again... Thank you very much for your help.

/*
You wrote:
"I have a table that records data and I want to be able to copy a few of the feilds to another table in a second database
WHENEVER THE DATA IS INSERTED OR UPDATED."
So you did not specify DELETED info.
*/

/* COMMENTED CODE */
--Switching to your active DB
use [Your active DB]
GO
--Creates trigger FOR INSERT,UPDATE on dbo.[user]
--This trigger uses INSERTED table of NEW VALUES for BOTH INSERT AND UPDATE
--( Look at "inserted tables" topic in BOL )
if object_id('TR_User(,I,U,)') is not null drop trigger [TR_User(,I,U,)]
GO
create trigger [TR_User(,I,U,)]
on dbo.[user]
for insert,update
as begin
insert [Your history DB].dbo.[n_user] ([oldid], [name], [password])
select [id], [name], [password]
from inserted
end
GO
--Deleting test rows in HISTORY
delete [Your history DB].dbo.[n_user]
--Filling history table with previosly inserted data (prehistoric)
declare @x int
update dbo.[user] set @x=1

Ok... looks like the insert new member fires off the trigger just fine. Both tables are updated correctly.

However, when a user tries to update their profile... They recieve an sql error stating a primary key violation.

Here is the exact trigger that causes that error.

==============

CREATE trigger [TR_Players(,I,U,)]
on dbo.[players]
for insert,update
as begin
insert [Forum].dbo.[FORUM_MEMBERS] (M_name, M_username, M_password, M_email, M_quote)
select [PEmail], [PEmail], [Ppassword], [PEmail], [pcomments]
from inserted
end

=============

Now I went ahead and edited the trigger to be this,

=============

CREATE trigger [TR_Players(,I,)]
on dbo.[players]
for Insert
as begin
Insert [Forum].dbo.[FORUM_MEMBERS] (M_name, M_username, M_password, M_email, M_quote)
select [PEmail], [PEmail], [Ppassword], [PEmail], [pcomments]
from inserted
end

=============

As you might expect... this fires off correctly and both tables get their new information.

Now the question is how do I get the update trigger to update table 2 (FORUM_MEMBERS) without attempting to add another row with the same information, thus violating the pk constraint.

I tried to add a second trigger to the same table just for updates like such.

=============

CREATE trigger [TR_Players(,U,)]
on dbo.[players]
for Update
as begin
Insert [Forum].dbo.[FORUM_MEMBERS] (M_name, M_username, M_password, M_email, M_quote)
select [PEmail], [PEmail], [Ppassword], [PEmail], [pcomments]
from inserted
end

=============

Too bad that gave me the exact same error. Also, syntax wise... im kind of confused why I had to use "inserted" and not "updated" to get successful syntax checking??? Does the temp table updated not exist with triggers? Can I not run two seperate triggers against the same table?

Oh and as an FYI. I did not want delete syntax... I was just commenting that in your post i saw the keyword delete?



Like always.... thanks very much for your time to deal with my issues..

CREATE trigger [TR_Players(,U,)]
on dbo.[players]
for Update
as begin
update fm set
fm.M_name = i.[PEmail]
, fm.M_username = i.[PEmail]
, fm.M_password = i.[Ppassword]
, fm.M_email = i.[PEmail]
, fm.M_quote = i.[pcomments]
from [Forum].dbo.[FORUM_MEMBERS] fm
join inserted i
on fm.M_name=i.[PEmail] --PK join - verify
end

--for more See http://dbforums.com/showthread.php?threadid=640545

CREATE trigger [TR_Players(,U,)]
on dbo.[players]
for Update
as begin
update fm set
fm.M_name = i.[PEmail]
, fm.M_username = i.[PEmail]
, fm.M_password = i.[Ppassword]
, fm.M_email = i.[PEmail]
, fm.M_quote = i.[pcomments]
from [Forum].dbo.[FORUM_MEMBERS] fm
join inserted i
on fm.M_name=i.[PEmail] --PK join - verify
end

--for more See http://dbforums.com/showthread.php?threadid=640545

You are like a God to me! Works great. Thank You Thank you!

It seems that I need to make another trigger to pass changed passwords back to the original table. I attempted to modify the previous trigger to work in reverse. But no suck luck. I could use alittle assistance here.

I want the trigger to take the M_password field from the forum_members table (when its updated) and update the other database
table players with the new password. Here is what I came up with...

------------------------------------------------
CREATE trigger [TR_pw(,U,)]
on [Forum].dbo.[FORUM_MEMBERS]
for Update
as begin
update pl set
pl.Ppassword = i.[M_password]
from dbo.[players] pl
join inserted i
on pl.pemail=i.m_name --PK Join - verify
end
------------------------------------------------

Thanks.

Your triggers are probably chaining, learn more about nesting
http://dbforums.com/showthread.php?threadid=640545

you know your prolly correct... cuz the error message im recieving says something about exceeding the number of database connections.

Ive read the post you refered me to... but am still not sure what it said. Sorry... im new to this. Can I assume that my trigger is formed correctly? But I need to some how limit its ability to fire from an update by another trigger?

Stike that...
I went to BOL and found how to turn off recursive triggers... I turned them off and it looks like its working. Is there any effect on regular system operation due to this trigger setting?

Thanks for your help.

You can put "if trigger_nestlevel(@@procid)>1 return" in the beginning of your data modifying trigger. Checking-only triggers stay active in the trigger chain. Also even with switching nested triggers off I cannot make Ver1 (table mirroring) working. Also when you are using recursive algorithm for trigger, you must have recursive triggers on.
In most cases you do not need it.

--Ver1
create table A(X int)
create table B(X int)
GO
create trigger tiA on A for insert as
insert B select * from inserted
GO
create trigger tiB on B for insert as
insert A select * from inserted
GO
insert A values (1)
GO
drop table A
drop table B

--Ver2
create table A(X int)
create table B(X int)
GO
create trigger tiA on A for insert as
if trigger_nestlevel(@@procid)>1 return
insert B select * from inserted
GO
create trigger tiB on B for insert as
if trigger_nestlevel(@@procid)>1 return
insert A select * from inserted
GO
insert A values (1)
GO
drop table A
drop table B

Good luck !