flexible back-end data handling in .net + sql project

[athos]

Hi guys,

Got a problem now :( please help...

now we got a project handling records saved in a table in a sql
2000(will upgraded to 2005 soon) server. every month around a million
records will be inserted.

now user raised a request, that is, once criterios are matched, the
project should do some backend handle, for example, if

SELECT colID, fieldC, fieldD FROM dataTable WHERE fieldA =
'fieldA_valueB'

returns some recordset, for each @fieldC, @fieldD we shall do some
back-end trick, maybe

UPDATE dataTable SET fieldE = 'fieldE_valueF' WHERE fieldC = @fieldC
AND fieldD = @fieldD

let's say such a rule is named as rule01. Hope I'm expressing the
problem clearly?

my questoin is, shall we do this in Database side, using triggers, or
by informing our .net project to do it?

1. since the records are coming around 1 millison per month, how can we
handling the performance issue?

2. now the rules are still somewhat simple, seems at least we could do
it by EXEC or SP_EXECUTESQL. but rules may turn quite complex, for
example, audit log, or more complex issues, shall we do it in .net
program? but how can a trigger in SQL database informing a .net
program, or webservice, or windows service? by executeing an executable
console program?

3. user may raise more and more rules, how could we provied a flexible
solution? i mean we're trying to build it less hard-coded. seems in sql
database EXEC or SP_EXECUTESQL are still somewhat flexible, while in
..net to do something like eval() in javascript or EXEC in sql server is
a little bit troublesome. but, put all these bussiness logic in stored
procedure sounds a little bit weired.

guys, hope i have made myself clear. any suggestion? Thanks very mcuh

yours,
athos.