trigger to insert into table2 from table1

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

trigger to insert into table2 from table1

Author

Topic

Chap
Starting Member

30 Posts

Posted - 2010-02-25 : 13:28:03

Createing a trigger to insert into table2 from table1
Each table has two columns, UserId and CustNum
Both are primary keys with UserId a foreign key to another table. The combination of the two columns must be unique.
Ex:
Table1: Table2
UserId CustNum UserId CustNum
5 1 5 1
5 2 5 2
5 3 5 3
6 22 6 22

Trigger works if new UserId is entered in table1 Ex:

Table1: Table2
UserId CustNum UserId CustNum
5 1 5 1
5 2 5 2
5 3 5 3
6 22 6 22
7 15 7 15

But if a new CustNum is entered in table1 with the same UserId

Table1: Table2
UserId CustNum UserId CustNum
5 1 5 1
5 2 5 2
5 3 5 3
6 22 6 22
7 15 7 15
7 17
Table2 does not update. I am using the following query:

Insert into [Table2(Userid,CustNum)
SELECT Userid,CustNum
FROM Table1
WHERE UserId not in (select UserId from Table1 )

Tried adding
and CustNum not id(select custnum from Table1)
with no success. Where am I going wrong?

George

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-25 : 14:03:47

in a trigger you can make use of a trigger table called inserted at runtime.
So your statement for new rows should be:
insert table2(userid, CustNum)
select userid, custnum from inserted

Read about triggers in books online...

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 14:22:11

Duplicate: Please answer here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=140457

Chap: If you post duplicate posts in multiple forums different people start answering each post and then find they have wasted their time and probably feel less inclined to help you in the future.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources