insert tigger won't copy mulitple rows

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)

insert tigger won't copy mulitple rows

Author

Topic

kspiderman
Starting Member

4 Posts

Posted - 2005-10-20 : 19:58:12

below is my code to copy from invoices(header) to ca_temp_invoices.
then trigger should be work to copy x_invoic(detail) to ca_temp_x_invoic.

if I copy 4 invoice in to temp_invoices, then I should have all 4 x_invoic(deatil) information should be copyed in to ca_temp_x_invoic but only copy 1 of last inovoice number detail is copyed in to ca_temp_x_invoic. "CAN SOMEBODY HELP". I been reading professional SQL Server 2000 Programming book for couple days to find but couldn't.

"My Trigger begins"

CREATE Trigger CA_Temp_X_inv_Ins_Del on dbo.ca_temp_invoices for
insert as

Declare @status tinyint
Declare @doc_no varchar (15)

select @status=inserted.status, @doc_no=inserted.doc_no from inserted

if @@rowcount = 0 return -- no rows affected, exit immediately
if @doc_no = null return

BEGIN
Begin Tran

insert ca_temp_x_invoic
select * from x_invoic where status=@status and order_no=@doc_no and desc_type=1

--exec CA_Insert_T_inv @status, @doc_no
COMMIT
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-20 : 20:04:49

You can't use variables like this with the inserted/deleted tables as variables can only have one value. So you either need to loop through the rows, or use a set-based approach.

Here's an example:
http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

Tara

kspiderman
Starting Member

4 Posts

Posted - 2005-10-20 : 20:15:31

I first like to said thank you.
I read the tigger tip.
but still have question.
can i use select * from Tablename for inserted.
or should use the update command.

what I mean is, that I have 2 same table one is temp and one is actural data table. I'm trying to copy it to temp table.
in the tigger tip it's using update. (confuse due to update)..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-20 : 20:20:39

The tip is just showing you how to use set-based approach with triggers. It shows a join to the trigger table. This approach can be used for UPDATE/DELETE/INSERT.

And yes you can use:
INSERT INTO SomeTable(Colum1, Column2)
SELECT Column1, Column2
FROM inserted
WHERE...(if needed)

Tara

kspiderman
Starting Member

4 Posts

Posted - 2005-10-20 : 20:21:06

Once again, Tara (thank you)
let me condense the qustion.
even the trigger insert use use update command not the insert ????
questing mind

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-20 : 20:22:12

You don't have to use UPDATE. UPDATE is used as an example in the tip. inserted table contains the rows that were inserted. It can be used like any other table while inside the trigger.

Tara

kspiderman
Starting Member

4 Posts

Posted - 2005-10-20 : 21:06:08

Tara it worked great,
once again thank you very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources