dcsimg
Results 1 to 3 of 3

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

  1. #1
    Join Date
    Apr 2005
    Posts
    3

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

    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

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  3. #3
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    You cannot have a TRIGGER for SELECT.

    Trigger can only be on Data manipulation statements

    INSERT, UPDATE, or DELETE statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •