Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Updating from within a trigger

ninchagoraninchagora Member Posts: 82
Hello.

I have tables A(idA, X) and B(idB, idA, Delta).
idA is prim.key in A, and B.idA references A(idA).

I need to update X values, so that X = X - Delta for every A and B where A.idA = B.idA.

I managed to compose an UPDATE statement which does this by calling function get_delta(idA), which returns corresponding Delta from B.

Here's the statement (MS SQL Server 2000):

[code]
update A set X = X - dbo.get_delta(idA) where idA in (select idA from B);
[/code]


I'm wondering if there is a way to avoid using a function, because I'd like to perform this update inside a trigger (after insert into table B).

So table B here would be [b]inserted[/b] table in a trigger.

And I don't know - is it possible to define function get_delta so that it performs search within [b]inserted[/b] table? If it is, it might solve the problem.

And another possible solution -
- to use temporary table inside the trigger, copy [b]inserted[/b] into temporary, and use function which reads from that temporary table (it has fixed columns)


I'm satisfied with the present solution, but I'd like to have it done with a trigger.
I was just wondering if there is a way to do this.

Thanks for your consideration.



Here's the whole SQL script:

[code]
-- prepare
/*
create trigger UpdateA on B
after insert
as
begin
-- ??
end
*/

create function get_delta ( @idA int )
returns int
as
begin
declare @retval int
set @retval = (select delta from B where idA = @idA);
return @retval
end
go

create Table A (
idA int primary key,
X int not null
);

create table B (
idB int primary key,
idA int not null,
delta int not null
);

insert into A values (1, 15);
insert into A values (2, 10);
insert into A values (3, 20);

insert into B values (1001, 1, 6);
insert into B values (1002, 3, 7);

-- update - should not be done here, but with the trigger
update A set X = X - dbo.get_delta(idA) where idA in (select idA from B);


-- aftermath
select * from A;

-- clean-up after experiment
delete from A;
delete from B;

drop table A;
drop table B;

drop function get_delta;
[/code]

Comments

  • ninchagoraninchagora Member Posts: 82
    : [code]
    : update A set X = X - dbo.get_delta(idA) where idA in (select idA from B);
    : [/code]

    [code]
    update A
    set X = X - (select delta from inserted where idA = A.idA)
    where idA in (select idA from inserted)
    [/code]

    or even better

    [code]
    update A
    set X = X - (select SUM(delta) from inserted where idA = A.idA)
    where idA in (select idA from inserted)
    [/code]

    I can't believe I overlooked such an obvious thing.
Sign In or Register to comment.