Triggers - Updating Multi rows problem

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)

Triggers - Updating Multi rows problem

Author

Topic

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-12-04 : 07:11:49

Hi,
I need to create a log table that should combine fields from 2 tables.
For every action of Insert to table_1 a trigger will be called to fill the Log table with the records that were added.
The same routine is done for table_2.
1.with the folloing SP I do insert of records from LanTable to table_1:

CREATE PROCEDURE Lan_Insert_Products
as

insert into tabel_1 (Product_Num,Sticker_type)
Select LanTable.ProductNum,LanTable.StickerType From LanTable left Join tabel_1 p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null

tabel_1:
Product_Num Sticker_type
11111111111 22
22222222222 33
55555555555 12
etc..

The trigger inserts records into the Log table.
LogTable:
Product_Num Sticker_type Second_Num
11111111111 22 Null
22222222222 33 Null
55555555555 12 Null

2.I am doing the same thing for table_2

table_2:
Product_Num Second_Num
11111111111 1010101010
22222222222 2020202020
55555555555 5050505050

The result of all my actions is to create the following Log table:

Product_Num Sticker_type Second_Num
11111111111 22 1010101010
22222222222 33 2020202020
55555555555 12 3030303030

After running the following trigger:

CREATE TRIGGER ocdb_history_trigger_SecProduct ON table_2
FOR INSERT, UPDATE, DELETE
NOT FOR REPLICATION
AS
Declare @Sec_Products varchar(200)
select @Sec_Products=deleted.SecondNum from deleted
UPDATE LogTable
SET
Second_Num = @Sec_Products
FROM
deleted,LogTable
WHERE
LogTable.Product_Num = deleted.Product_Num

I get the following LogTable:
Product_Num Sticker_type Second_Num
11111111111 22 3030303030
22222222222 33 3030303030
55555555555 12 3030303030

I need the Second_Num to be according to table_2
What did i do wrong?

Thanks
Yossi

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-04 : 07:26:48

Declare @Sec_Products varchar(200)
select @Sec_Products=deleted.SecondNum from deleted

this will just get the one value from deleted - the last one it accesses in the table.
It then updates all entries to that value.

you need something more like
UPDATE LogTable
SET
Second_Num = deleted.SecondNum
FROM
deleted,LogTable
WHERE
LogTable.Product_Num = deleted.Product_Num

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-12-04 : 07:46:18

quote:

Declare @Sec_Products varchar(200)
select @Sec_Products=deleted.SecondNum from deleted

this will just get the one value from deleted - the last one it accesses in the table.
It then updates all entries to that value.

you need something more like
UPDATE LogTable
SET
Second_Num = deleted.SecondNum
FROM
deleted,LogTable
WHERE
LogTable.Product_Num = deleted.Product_Num

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Tahnks alot, It works
Yossi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources