Oracle - emulating a trigger on SELECT

views:

answers:

+1 Q:

Oracle - emulating a trigger on SELECT

(I had already written this question out when I figured out the answer, so I'll post it anyway since Google didn't give me any useful answers).

I'm looking to create something much like a trigger which fires on an Oracle SELECT. The goal is remove specific rows from the result of the SELECT depending which user is doing the SELECT.

This needs to work on the Standard Edition version of Oracle 11g (not Enterprise), so I don't have access to Row Level Security or Fine-Grained Auditing.

I've got an existing (very) legacy application which has thousands of embedded selects all over the place, so ideally I don't want to have to change every one...

The table I'm looking to work with has a million rows in it, which somewhat limits me from a performance perspective.

+2 A:

Since I was only really looking to modify the select on 1 particular table, the answer for me was to rename the existing table and create a view with the original name.

Colin Pickard 2010-09-27 11:07:35

Then you have to create triggers INSTEAD OF INSERT and DELETE on your view to stay compatible.

Benoit 2010-09-27 11:08:49

@Benoit - Not necessarily. A simple view that is just filtering out rows from a table is generally going to be updatable in Oracle. While it is certainly possible to create a view that filters out rows that is not updatable, in which case the triggers would be necessary, you'd probably need to do something like a non key-preserved join to another table for that to be an issue.

Justin Cave 2010-09-27 15:04:19

@Justin Cave You are correct. In my case the view is very simple - it looks like `select * from emp where (<trivial condition>)`. It is updateable.

Colin Pickard 2010-09-27 16:21:52

I think, simple if condition in PL SQL block can resolve your problem.

Pravin Satav 2010-09-27 12:40:42

Of course. But I'm maintaing a VERY large application - in fact one huge app and several smaller apps - and there are many hundreds, probably thousands of places from where this table is accessed. If I were to go round adding my simple if condition to all of them, I would be either mad or retired before I finished :)

Colin Pickard 2010-09-27 16:24:45

ansaurus

tags:

views:

answers:

Oracle - emulating a trigger on SELECT

related questions