monitoring changes on a view

I have a number of tables, each with many fields. I put together
information by a view onto the relevant tables and fields. Now I'd like
to monitor if data in my view has been updated or inserted. I don't care
about the fields not included in the view. If something changes, the ID
of the corresponding dataset should be inserted into a log table.
I have no idea how to handle this with a trigger. If I put a trigger on
the basetables, it will fire on every change. On the other hand i can't
put a trigger on the view. Any ideas?
Thanks in advance
Michael
Example:
Table1
a1
a2
a3
a4
Table2
b1
b2
b3
b4
Table3
c1
c2
c3
c4
c5
View combines
a1,a2,b1,c4,c5Michael Schroeder" wrote:

> I have a number of tables, each with many fields. I put together
> On the other hand i can't
> put a trigger on the view. Any ideas?
>
Do you mean that certain business rules prevent you from putting a trigger
on the view? You can put an INSTEAD OF trigger on the view, and put logic
into the trigger to update the underlying base tables, as well as put an
entry into an audit log.|||I don't know what type of events you are wanting to audit, but you can
create an insert, update, or delete triggers on the base tables. Whether or
not this presents a problem depends on how complex the logic of the trigger
is. Below is an example of a simple and low cost implementation of an
auditing trigger that I have used in the past. In this case, the table
EmployeeAudit has the same column layout of Employee but with the addition
of a column named AuditDate and AuditType (delete or insert). An update is a
delete immediately followed by an insert.
CREATE trigger EmployeeAudit on Employee
AFTER INSERT, UPDATE, DELETE
AS
insert into EmployeeAudit select getdate(), 'D', * from deleted
insert into EmployeeAudit select getdate(), 'I', * from inserted
GO
"Michael Schroeder" <schroeder@.idicos.[germany]> wrote in message
news:%23zqtnQ1JGHA.1544@.TK2MSFTNGP11.phx.gbl...
>I have a number of tables, each with many fields. I put together
>information by a view onto the relevant tables and fields. Now I'd like to
>monitor if data in my view has been updated or inserted. I don't care about
>the fields not included in the view. If something changes, the ID of the
>corresponding dataset should be inserted into a log table.
> I have no idea how to handle this with a trigger. If I put a trigger on
> the basetables, it will fire on every change. On the other hand i can't
> put a trigger on the view. Any ideas?
> Thanks in advance
> Michael
> --
> Example:
> Table1
> a1
> a2
> a3
> a4
> Table2
> b1
> b2
> b3
> b4
> Table3
> c1
> c2
> c3
> c4
> c5
> View combines
> a1,a2,b1,c4,c5|||Mark Williams schrieb:
> Michael Schroeder" wrote:
>
> Do you mean that certain business rules prevent you from putting a trigger
> on the view? You can put an INSTEAD OF trigger on the view, and put logic
> into the trigger to update the underlying base tables, as well as put an
> entry into an audit log.
No. I can put an INSTEAD OF trigger on that view. But it does not fire
when something in the underlying basetables changes or something is
beeing inserted/deleted.
Maybe I have to mention that the basetables a beeing updated by
replication and the view and its trigger are just for monitoring and
reporting.
Putting triggers on each basetable is not a good idea, because there are
many of them and just a fraction of their data is interesting for
processing.
Thanks for your reply
Michael