how to create a trigger on a view to detect update/insert/delete of view

Question

Hi

I'd like to ask how to create a trigger on a view to detect update/insert/delete of view.

My view's name is View_Test, it is "select * from remote_server.datetable". I need to detect if there is a change(insert/update/delete) of the view.

I wrote the trigger like

CREATE TRIGGER [trigger_test_insert] ON [View_Test]

after insert

AS insert into [dbo].[db_history]

select 'Test','a','I',GETDATE()

but the system forbid me to create such trigger, it says " object 'View_Test' not exist, or this operation is invalid"(the tips is in chinese, and i translate it)

may I ask how to solve the problem? thank you

btw I wrote

CREATE TRIGGER [trigger_test_insert] ON [View_Test]

instead of insert

AS insert into [dbo].[db_history]

select 'Test','a','I',GETDATE()

the trigger can be created but there is no inserted data in table db_history

Answer 1

A view is a logical concept, so you cannot create a trigger on a view to track changes. Basically, a view is a stored query. Let's that the view includes ten tables. A change in any of these ten queries can change the result of the view.

You can create INSTEAD OF triggers on a view. This permits you run direct INSERT/UPDATE/DELETE on the view and define what actually is going to happen. This is a fairly advanced feature, only used in special conditions.

I think we need to know what you really want to achieve. That is, what drove you to think that you wanted this trigger and what is your root problem?

By the way, what is the definition of the view?

Answer 2

Hi @Martin Wang

but when there is a update in the view(which is ,update in one_remote_server.database.table)、

Is this update on the original table or on the view?

I did a test. If the update occured on the view, the trigger takes effect and [table_history] has a record inserted. If the update occurred on the original table, no record inserts.

Best regards,

Percy Tang