February 13, 2008 at 1:43 pm
Hi Guys,
I was struck with one trigger code.I'm trying to update multiple rows in one update statement on one table. I need a trigger to replicate those changes to another table.
Can any one help me with the code for the update trigger which is used to update multiple rows at a time(as I said it fires only once)?
I appreciate your help in this regard.
Thanks,
Swathi.
February 13, 2008 at 4:46 pm
Hi,
In simple words its not possible.
Thanks -- Vj
February 13, 2008 at 6:22 pm
You could actually do something like this:
create table Test1(
id int,
descr varchar(25),
Col char(1)
)
go
INSERT INTO Test1
select 1, 'test1', 'Y' union all
select 2, 'test1', 'Y'
go
create table Test2(
id int,
descr varchar(25),
Col char(1)
)
go
INSERT INTO Test2
select 1, 'test1', 'Y' union all
select 2, 'test1', 'Y'
go
create trigger TestTrigger
on test1
after update
as
UPDATE Test2
--add all columns here in set statement
SET descr = b.descr,
Col = b.Col
from deleted a inner join inserted b on a.id = b.id
--inner join your table on the same key field
inner join Test2 c on c.id = a.id
--add all columns in where
where a.descr = c.descr and a.col = c.col
go
update Test1
Set Col = 'N'
Where id = 2
go
select *
from test1
select *
from Test2
go
drop trigger TestTrigger
go
drop table Test1
go
drop table TEst2
go
February 13, 2008 at 7:00 pm
Like Adam said - there are a lot of things that can be done. It would be helpful to have some further specifics at to what you need to do. Otherwise - take a look at some of the examples here and in BOL as to using the INSERTED and DELETED virtual tables.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply