trigger on view

Question

trigger on view

tengtium

SSC-Addicted

Points: 457
More actions
April 25, 2005 at 1:45 am

#164340

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

There is not a direct way to fire a trigger on a select statement in SQL 2000.

There is a very unconventional way to do this bay using a trace and at cetain intervals import the file into an audit table where a trigger can be fired at insert time. This is very cumbersome is the activity is high but is probably the only way at least from back end perspective. Should you need details read this

hth

* Noel

Michael Girdler Right there with Babe Points: 780 More actions · Answer 2

Why don't you have a think about only giving access to the table through a stored procedure. You can do what you want in the stored procedure and so you could use this to keep statistics on who views the table....

Michael

Rob Farley SSCrazy Points: 2739 More actions · Answer 3

Using a stored procedure doesn't stop people from having access to the table as well though. Although, in SQL2005 you can tell the stored procedure to execute to impersonate an appropriately privileged user, so that you can stop people from accessing the table without going through the stored proc.

But I would actually use a user-defined function instead, so that people can use it in queries. Just like a view in the way you use it, but like stored procedure because you can audit it better.

Rob

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

Sergiy SSC Guru Points: 110208 More actions · Answer 4

It's absolutely natural to create INSTEAD OF trigger on a view.

This trigger suppose to contain set of statements taking data from view fields and put it into source tables.

Query Analizer contains standard template for such triggers.

Look in BOL for INSTEAD OF triggers for more information.

_____________
Code for TallyGenerator

Rob Farley SSCrazy Points: 2739 More actions · Answer 5

Sergiy,

But he's asking about a 'select trigger', not insert/update/delete.

So the concept of a trigger doesn't work.

Rob

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

tengtium SSC-Addicted Points: 457 More actions · Answer 6

that righ! i'm trying to audit all the select statement for each table, i believe trigger works only for insert, update and delete.. i'm trying to find a good solutions on this problem.. someone told me to use trace.. but i think this is overkill and might(possible) give additional overhead on the server...

any suggestions?

noeld SSC Guru Points: 96590 More actions · Answer 7

>>.. someone told me to use trace.. but i think this is overkill and might(possible) give additional overhead on the server...<<

That someone was me noeld

Is not that is not overkill, is that is your only choise with views or tables

This is one of the reasons that you may want to ecapsulate all your access to the server in stored procedures

When I said it was the only choise, I meant without third party tools. If you have $$$$, then you can always buy Entegra and do your reporting from there

* Noel

Sergiy SSC Guru Points: 110208 More actions · Answer 8

Freaky Dutch English...

But if you have any SELECT statement in your application???

Dynamic SQL is your choice???

I've forgotten somebody uses access data by "SELECT * FROM ..."

If you do it's a good reason to make all your developers not to write statements in applications. I tell you, it's possible.

And than one day your application may pass sequrity sertificaton...

_____________
Code for TallyGenerator

Rob Farley SSCrazy Points: 2739 More actions · Answer 9

Ignoring the (all valid) points about where queries should be written - you really should consider a user_defined function for it. If you want, you can make a view that has the same name as your original table, and have that view query the function... but by having a function, you can still include it in queries as you always have done, and put the auditing in too.

Rob

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au