asked on

Trigger and delete, update or insert more than one register?

Hello guys,

Sometimes I need to update more than on row, maybe 2 or 100 rows at the same time, but my trigger only fire on row.

I did an example very simple on it. Maybe you can help me on this:

create table alex (codcad int, nome varchar(100))

create trigger teste on arcad
for update
as 
begin   
  declare @codcad int
  declare @nomcad varchar(100)
  
  select @codcad=codcad, @nomcad=nomcad from inserted

  insert into alex (codcad, nome) values (@codcad, @nomcad)

end

update arcad set numdoc1=numdoc1 where codcad > 1 and codcad < 50

Open in new window

After run the update, only one row is recorded through my trigger. how to solve this?

thanks
alex

Shaun Kline

You can try:

INSERT INTO alex (codcad, nome)
SELECT codcad, nomcad
FROM Inserted

Open in new window

hidrau

ASKER

Hello Shaun,

I got it, but I have a situation and I noticed that I can do that way, I need to check a field it can insert it into the alex or not.

but I noticed that I cannot have a temporary table in trigger, :(

take a look at it:

alter trigger teste on arcad
for update
as 
begin   
  declare @codcad int
  declare @nomcad varchar(100)
  declare @orderclosed bit
  declare @id varchar(100)
  
  select * into #tmp from inserted

  alter table #tmp add lido varchar(5)
  alter table #tmp add id varchar(100)

  update #tmp set lido='N'
  --update #tmp set id=newid();

  while exists (select 'x' from #tmp where lido='N') 
  begin 
    select @id=id, @codcad=codcad, @nomcad=nomcad, @orderclosed=flagbax from #tmp

    if @orderclosed=1 
	begin 
      insert into alex (codcad, nome) 
      select codcad, nomcad from #tmp
    end
	update #tmp set lido='S' where id=@id
  End
end

Open in new window

ASKER CERTIFIED SOLUTION

Shaun Kline

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hidrau

ASKER

Thanks a lot for your help