Updating from within a trigger

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

  • : [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.

Howdy, Stranger!

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

Categories

In this Discussion