| 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 help for trigger
A copy of this was sent to "Venkat Mundrathi" <vmundrathi_at_cartech.com>
(if that email address didn't require changing)
On Mon, 22 Feb 1999 12:31:11 -0500, you wrote:
>I am new to oracle(triggers). I am using personal oracle 8.0,
>
>1)The purpose of trigger is when ever I insert a new row in the emp
>table(hope you know the description) with the same empno or ename it has to
>fire and give an error message to user that the row with empno or ename is
>already exist.
its not possible to do this in a trigger UNLESS you have your trigger place a full table lock on the EMP table in case you care. The reason is multi-versioning. If 2 people insert into emp:
insert into emp ( ename, empno ) values ( 'tom', 1 );
simultaneously, neither will see eachothers insert (non blocking reads). When they commit you end up with dups. the only solution would be to lock the entire emp table for each update/insert and completely serialize access if you really really had to use a trigger.
the correct answer for this problem is a unique constraint on ename and a primary key on empno.
>2) I created a new table U_OBJECTS( name varchar(40),type
>varchar(15),created date). I inserted the values from user_objects table.
> Now i tried to create a trigger, it should fire such that when ever a new
>row is inserted in user_objects the table U_OBJECTS has to be updated( does
>this sounds ridiculous). when i was compiling thetrigger it is give me the
>following error
>
>ORA 25001- CANNOT CREATE THIS TRIGGER TYPE ON VIEWS
>
>can you please comment on this.
>
SQL> create view foo as select * from dual;
View created.
SQL> create trigger foo_trigger
2 after insert on foo
3 for each row
4 begin
5 null;
6 end;
7 /
create trigger foo_trigger
*
ERROR at line 1:
ORA-25001: cannot create this trigger type on views
you can create instead of triggers on views as such:
SQL> create trigger foo_trigger
2 instead of insert on foo
3 for each row
4 begin
5 null;
6 end;
7 /
Trigger created.
(see the docs for more info on an instead of trigger) HOWEVER:
2) you cannot create triggers on objects owned by sys and user_objects is owned by sys:
SQL> create trigger user_objects_trigger
2 instead of insert on sys.user_objects
3 for each row
4 begin
5 null;
6 end;
7 /
create trigger user_objects_trigger
*
ERROR at line 1:
ORA-04089: cannot create triggers on objects owned by SYS
but....
3) it would not matter if you could anyway because no one every INSERTS into user_objects (the instead of trigger fires when the view is inserted into). Only the base tables upon with user_objects is built are inserted into and you are back at 2 again (don't even try to create a trigger on those tables -- it won't work)
>thanks in advance
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |