SQl Statement for Trigger

Question

SQl Statement for Trigger

nazaninahmady_sh

Default port

Points: 1465
More actions
November 13, 2008 at 12:54 am

#383371

In SQL Server 2005 i have two tables in two different Databases, i want to write a Trigger that when a record add to Table_1 in DB1 , we can have that record in Table_2 in DB2 ,
please help me to write the SQL Statement :
the Structure of Table_1 in DB1 :
Filenumber int , not null
Name char,
Surename char,
and where should i write this Trigger ?
Shoud i write in in Table_1 in DB1?
thanks

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

The trigger has to go on the table that the insert is been done into. In this case, that'll be on Table_1 in DB1

The trigger just has to insert the contents of the inserted table into the second table, assuming the structures of the two are the same. So the contents of the trigger would just be

INSERT INTO DB2.dbo.Table_2 (Filenumber, name, surename)

SELECT Filenumber, name, surename FROM inserted

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nazaninahmady_sh Default port Points: 1465 More actions · Answer 2

What should i do about the primary keys:

in DB1.Table_1 , "Filenumber" is Primary key , but

i want to have "Code" and "Filenumber" (both of them) be the primary key in DB2.Table_2

"Code" isnt exist in DB1.Table1

i want "Code" automatically increase.

i use uniqueidentifier for type of "Code" but an error ccured

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

nazaninahmady_sh (11/13/2008)
i want "Code" automatically increase.

Use an int and make it an identity column.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nazaninahmady_sh Default port Points: 1465 More actions · Answer 4

Thank you very much

but another error occured

i wrote Trigger and i create the primary keys

but every time (when i add a new record to DB1.Table1) i have 2 copies of that record in DB2.Table2 for exampe:

i add this record to DB1.Table1

Filenumber (Primary key (nchar)) Name Surename

10 a aa

and in DB2.Table2 i have these:

Code(Primary key- int) Filenumber(Primary key-nchar) Name Surename

1 10 a

2 New a

Code is( Identiti Column)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

Post the trigger code please, the table structure a couple of example insert statements and what you want Table_2 to look like afterwards.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass