trigger on view
-
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
-
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 -
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
-
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 -
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 -
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 -
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?
-
>>.. 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 -
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 -
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply