Create 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

SQL Server Development (2000)

Create trigger to insert into table2 from table1

Author

Topic

Chap
Starting Member

30 Posts

Posted - 2010-02-25 : 10:40:12

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 11:05:03

post your current trigger code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Chap
Starting Member

30 Posts

Posted - 2010-02-25 : 11:20:04

create TRIGGER [dbo].[tgr_upd_UpdateUser_Customer]
ON [dbo].[User_Customer]
AFTER INSERT, UPDATE, DELETE AS

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

George

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 11:25:28

it should be


create TRIGGER [dbo].[tgr_upd_UpdateUser_Customer] 
ON [dbo].[User_Customer] 
AFTER INSERT, UPDATE, DELETE AS

Insert into Table2(Userid,CustNum) 
SELECT Userid,CustNum
FROM inserted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Chap
Starting Member

30 Posts

Posted - 2010-02-25 : 11:42:44

Deosen't work because both columns are primary key. returns the following error:
Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in object 'Table1'.
The statement has been terminated.

George

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 11:51:53

If u see my query i'm not inserting to Table1 but to Table2 so not sure why you get this error unless you do it wrong way

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 14:23:27

What do you expect to happen when a records is inserted into Table1 and the UserID + CustNum already exists in Table2?

Error?

Just ignore it?

Something else?

Chap
Starting Member

30 Posts

Posted - 2010-02-25 : 15:21:40

Visakh16

That worked, I wasn't understanding the from inserted part but found it in some more research. Thanks

George

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources