Problema con un trigger de actualizacion
Realice un Trigger pero creo que estoy cayendo en recursividad, pues el campo dispara el suceso, lo nesecito cambiar dentro del trigger.
create trigger valocclienteup2
on soheader
for update
as
declare @vccustid as char(15)
declare @vcordcom as char(25)
declare @vccantoc as int
if update(adminhold)
begin
set @vccustid = (select custid
from inserted)
set @vcordcom = (select custordnbr
from inserted)
set @vccantoc = (select count(custordnbr)
from soheader
where custid = @vccustid
and custordnbr = @vcordcom)
if @vccantoc = 1
update soheader
set adminhold = 0
from soheader inner join inserted on soheader.ordnbr = inserted.ordnbr
else
update soheader
set adminhold = 1
from soheader inner join inserted on soheader.ordnbr = inserted.ordnbr
end
Es posible realisar esta operacion?
Que alternativa podria seguir?
create trigger valocclienteup2
on soheader
for update
as
declare @vccustid as char(15)
declare @vcordcom as char(25)
declare @vccantoc as int
if update(adminhold)
begin
set @vccustid = (select custid
from inserted)
set @vcordcom = (select custordnbr
from inserted)
set @vccantoc = (select count(custordnbr)
from soheader
where custid = @vccustid
and custordnbr = @vcordcom)
if @vccantoc = 1
update soheader
set adminhold = 0
from soheader inner join inserted on soheader.ordnbr = inserted.ordnbr
else
update soheader
set adminhold = 1
from soheader inner join inserted on soheader.ordnbr = inserted.ordnbr
end
Es posible realisar esta operacion?
Que alternativa podria seguir?
Respuesta de rume83
1
Pues no, deberías crearte un cursor para poder realizarlo
create trigger valocclienteup2
on soheader
for update
as
declare @vccustid as char(15)
declare @vcordcom as char(25)
declare @vccantoc as int
DECLARE @v_clave int
DECLARE @v_adminhold int
if update(adminhold)
begin
set @vccustid = (select custid
from inserted)
set @vcordcom = (select custordnbr
from inserted)
set @vccantoc = (select count(custordnbr)
from soheader
where custid = @vccustid
and custordnbr = @vcordcom)
create cursor mi_cursor
for select soheader.adminhold, soheader.ordnbr from soheader inner join inserted on soheader.ordnbr = inserted.ordnbr;
if @vccantoc = 1
BEGIN
open cursor mi_cursor
FETCH NEXT FROM mi_cursor INTO @v_adminhold, @v_clave
WHILE (@@FETCH_STATUS <> -1)
BEGIN
UPDATE soheader set adminhold = 0 where ordnbr = @v_clave
FETCH NEXT FROMmi_cursor INTO @v_adminhold, @v_clave
END
END
ELSE
BEGIN
open cursor mi_cursor
FETCH NEXT FROM mi_cursor INTO @v_adminhold, @v_clave
WHILE (@@FETCH_STATUS <> -1)
BEGIN
UPDATE soheader set adminhold = 1 where ordnbr = @v_clave
FETCH NEXT FROMmi_cursor INTO @v_adminhold, @v_clave
END
END
CLOSE mi_cursor
DEALLOCATE mi_cursor
END
create trigger valocclienteup2
on soheader
for update
as
declare @vccustid as char(15)
declare @vcordcom as char(25)
declare @vccantoc as int
DECLARE @v_clave int
DECLARE @v_adminhold int
if update(adminhold)
begin
set @vccustid = (select custid
from inserted)
set @vcordcom = (select custordnbr
from inserted)
set @vccantoc = (select count(custordnbr)
from soheader
where custid = @vccustid
and custordnbr = @vcordcom)
create cursor mi_cursor
for select soheader.adminhold, soheader.ordnbr from soheader inner join inserted on soheader.ordnbr = inserted.ordnbr;
if @vccantoc = 1
BEGIN
open cursor mi_cursor
FETCH NEXT FROM mi_cursor INTO @v_adminhold, @v_clave
WHILE (@@FETCH_STATUS <> -1)
BEGIN
UPDATE soheader set adminhold = 0 where ordnbr = @v_clave
FETCH NEXT FROMmi_cursor INTO @v_adminhold, @v_clave
END
END
ELSE
BEGIN
open cursor mi_cursor
FETCH NEXT FROM mi_cursor INTO @v_adminhold, @v_clave
WHILE (@@FETCH_STATUS <> -1)
BEGIN
UPDATE soheader set adminhold = 1 where ordnbr = @v_clave
FETCH NEXT FROMmi_cursor INTO @v_adminhold, @v_clave
END
END
CLOSE mi_cursor
DEALLOCATE mi_cursor
END
