Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

Is it possible to create a trigger that fires when a view is created on a user schema?

I want to create a meta data table which will hold all the views and a listing containing what they are supposed to do. In this system (Oracle 9.2.0.8) there is only one user who has the create view privilege so that may make it easier.

The sequence of events should be something like view is created trigger fires and inserts the name of the view in the table of meta data table

I could write up some package to create views using dynamic sql but that seems a bit of overkill

share|improve this question
Isn't there a catalog/information schema on Oracle that would allow to query the views and their comments? I'm not knowing Oracle but would assume that it exposes the information somehow that is needed and prensent internally anyway. You could save yourself from the extra table then and avoid risking to get out of sync with the present views (unless a history of the DDL statements is also necessary). – Augustus Kling Aug 25 '11 at 20:38
A good idea but I wish to add a descriptive comment about what the view is for so I need a table for that anyway. – kevinsky Aug 25 '11 at 21:54
It should be possible to store the comment on the views with comment on view viewname is 'test comment'. – Augustus Kling Aug 26 '11 at 4:44
this is how you can comment on a view: comment on table aschema.myview is 'number of issues by calendar year'; (notice how even if it is a view you have to say table) SELECT * FROM all_tab_comments WHERE owner = 'aschema'; but you will need privileges to query these system data views – kevinsky Aug 26 '11 at 17:10
I'm sorry for the confusion. I did not remember Oracle calls views table when adding comments – others call them view, too. Regarding the permissions thing: You would create a view with read permissions set for your user to get the meta data. I'd say since gbn already gave a helpful answer, this discussion can be stopped here. References: download.oracle.com/docs/cd/B10501_01/server.920/a96540/… and postgresql.org/docs/8.3/static/sql-comment.html – Augustus Kling Aug 26 '11 at 20:21

1 Answer

up vote 4 down vote accepted

You can use DDL triggers in Oracle.

I haven't actually done this myself, so here are some links:

share|improve this answer
That works! I can't see how to accept this as an answer. Maybe it's my browser. – kevinsky Aug 25 '11 at 17:25
Near the answer there should be a check mark sign. Change the browser if the current doesn't show it :-). – Marian Aug 25 '11 at 20:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.