| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Need urgent help with Oracle triggers
A copy of this was sent to "Damon Nicholas" <so_excited_at_excite.com>
(if that email address didn't require changing)
On Mon, 4 Jan 1999 09:47:24 +0800, you wrote:
>Hi, i'm very new in Oracle triggers (in fact in anything that is Oracle) and
>is encountering some problems with them.
>
>I tried to create an Oracle trigger on a table but it did not work properly.
>According to my DBA, the trigger did not fired because the end-users access
>the table through views and i am pretty confused. The view on the table is a
>simple one i.e. one view for one table.
>
Triggers are defined on tables. Views are defined on tables. A table may be inserted into via a view and that will fire all of the triggers and constraints defined on the base table. The end user only needs INSERT access on the view and that will fire any INSERT trigger defined on the base table in the view.
>My question is can i by giving rights to both the table and view to the
>user, get the trigger to fire off when an insert SQL statement is executed
>that writes a new record to a view?
>
Try this:
SQL> create table t ( x varchar2(25) ); Table created.
SQL> create trigger t_trigger
2 before insert on t
3 for each row
4 begin
5 :new.x := upper(:new.x);
6 end;
7 /
Trigger created.
so, we have your table and a trigger on it. now someone has created a view V on T:
SQL> create or replace view v as select * from t; View created.
We now create a user with very little priveleges, only enough to log in:
SQL> create user little_privs identified by test;
User created.
SQL> grant create session to little_privs;
Grant succeeded.
and grant his access to the VIEW V, not to T:
SQL> grant all on v to little_privs;
Grant succeeded.
Now, log in as that user and try it out:
SQL> connect little_privs/test
Connected.
SQL> insert into tkyte.v values ( 'this is in lower case' ); 1 row created.
SQL> select * from tkyte.v;
X
See, the trigger fired causing the text to be uppercased. Also, notice that little_privs cannot see the base table upon which the trigger is defined.
something else must be going awry with your trigger. Can you explain what you mean by "it did not work properly"
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Jan 03 1999 - 20:00:47 CST
 |
 |