trigger on view--is it possible or i missing something

[tengtium]

hi...

is it possible to create a trigger on view object....

usually we divide our table in to a smaller pieces and use view to get the big picture of it..

my task is to record any/every select statement on each table.. so logically we create view and is it possible to use trigger(to record the accesses) in view..

please help..

or just give me a link to get that info..
tnx

[MAK]

you have to use Instead Of Trigger....

example:

Create table myt (id int, name varchar(100))
insert into myt select 1,'a1'
insert into myt select 21,'a2'
insert into myt select 516,'a3'
insert into myt select 12,'a4'
insert into myt select 1333,'a5'
insert into myt select 1560,'a6'
insert into myt select 2001,'a7'
insert into myt select 2671,'a8'


Create View myt_range500 as
select id,name from dbo.myt where id<=500

Create View myt_range1500 as
select id,name from dbo.myt where id<=1500 and id>500

Create View myt_rangeothers as
select id,name from dbo.myt where id>1500


create trigger myt_range500_ins_tr on myt_range500 instead of insert
as
insert into myt select * from inserted
print 'You are in Range 0-500'

create trigger myt_range1500_ins_tr on myt_range1500 instead of insert
as
insert into myt select * from inserted
print 'You are in Range 500-1500'

create trigger myt_rangeothers_ins_tr on myt_rangeothers instead of insert
as
insert into myt select * from inserted
print 'You are in Range > 1500'



insert into myt_rangeothers select 2500,'qqwww'

--result
(1 row(s) affected)
(1 row(s) affected)
You are in Range > 1500

insert into myt_range500 select 120,'qqw'
--result
(1 row(s) affected)
(1 row(s) affected)
You are in Range 0-500

[MAK]

You cannot have a TRIGGER for SELECT.

Trigger can only be on Data manipulation statements

INSERT, UPDATE, or DELETE statement.