calculate maximum value of childrecords in a trigger?

I have a parent record en some child records.

Let's say the child records have a field with a value in the range 1-9

Now I want to find the maximum value in the child records.

When I have that value, I want to write this value in a field in the parent record.

1) Should I do this in a (update) trigger?

2) How do you write such a trigger in sql-express?

thanks Klaas

Netherlands

Is the trigger on the child table or the parent table?|||

something like:

CREATE TRIGGER trig

ON children for INSERT,UPDATE

AS

BEGIN

SET NOCOUNT ON;

declare @.parentID int

declare @.maxVal int

declare @.val int

select @.parentID = parentID

from deleted

select @.val = val

from deleted

select @.maxVal = max(val)

from children

where parentID = @.parentID

if @.val > @.maxVal

begin

update parent

set maxVal = @.maxVal

where id = @.parentID

end

SET NOCOUNT OFF

END

GO

I am assuming you want the trigger on the child table, and make it update the parent table if the new value is greater than the old maximum value.

HTH