Hi everyone,

I am practice on create trigger in Oracle. I found some simples online
and tried to put it in my computer. However, it pops up an error
messge "warning: trigger created with compilation errors."

Question: we want the net worth of any executive to be below $50000.

First i created the table:
create table movieexce(name varchar2(50) primary key, address
varchar2(50), networth number(9,2));

Second i tried to create trigger: ( actually, i put the same thing on
the net to test it)
create or replace trigger avgnetworthafterupdate
after update of networth on movieexce
referencing
old as oldstuff
new as newstuff
begin
if (50000>(select avg(networth) from movieexce)) then
delete from movieexce where (name, address, networth) in newstuff;
insert into movieexce (select * from oldstuff);
end if;
end avgnetworthafterupdate;
/
Result:warning: trigger created with compilation errors.

any one has clue what is wrong with that trigger?

Thanks,
Krista

Krista <ywanip (AT) gmail (DOT) com> wrote in news:1193017317.584469.269100
@e34g2000pro.googlegroups.com:

SQL> SHOW ERROR

On Oct 21, 7:57 pm, "Ana C. Dent" <anaced... (AT) hotmail (DOT) com> wrote:
after i typed show error, it shows

LINE/COL ERROR
-------- -----------------------------------------------------
3/5 PL/SQL: SQL Statement ignored
3/62 PL/SQL: ORA-00920: invalid relational operator

i am just beginner, so i am not quit sure what it mean....3/5 mean
line 3? but how about 5? what is the meaning column 5 ?anyone can
help?

krista

Krista <ywanip (AT) gmail (DOT) com> wrote in news:1193022691.631666.152270
@i38g2000prf.googlegroups.com:

It would help if you actaully posted the code along with the results.

On Oct 21, 9:27 pm, "Ana C. Dent" <anaced... (AT) hotmail (DOT) com> wrote:
SQL> create or replace trigger avgnetworthafterupdate
2 after update of networth on movieexce
3 referencing
4 old as oldstuff
5 new as newstuff
6 begin
7 if (50000>(select avg(networth) from movieexce)) then
8 delete from movieexce where (name, address, networth) in
newstuff;
9 insert into movieexce (select * from oldstuff);
10 end if;
11 end avgnetworthafterupdate;
12 /

Warning: Trigger created with compilation errors.

SQL>
SQL> show error
Errors for TRIGGER AVGNETWORTHAFTERUPDATE:

LINE/COL ERROR
--------
-----------------------------------------------------------------
3/5 PL/SQL: SQL Statement ignored
3/62 PL/SQL: ORA-00920: invalid relational operator

This is the code and the result.
I almost copied the slide 27 from pages.stern.nyu.edu/~mjohnson/oracle/
archive/jan05/Lecture09.ppt
but don't know why it doesnt work.
Thank you very much. I am very appreciated
Krista

"Ana C. Dent" <anacedent (AT) hotmail (DOT) com> schreef in bericht
news:skUSi.10962$oC3.5760 (AT) newsfe08 (DOT) phx...
I think it's better to ask what is right!

1) you try to select the average networth over the complete table (with
wrong syntax) and compare it to 50000 in stead of the networth of an
employee (newstuff.networth)
2) In stead of deleting the new record (wrong syntax again, and I don't
think you can delete a record which is the cause of the update trigger) and
re-inserting the old one (syntax wrong again) the update should just fail,
or networth should be set to 50000 (whatever you want)
3) A delete statement should look like "delete from my_table t1 where
t1.name = newstuff.name" (and since name is primary key (aaargh) the rest of
the columns should NOT be there).
4) The insert should look like (though wrong at all) "insert into my_table
(name, address,networth) values (oldstuff.name, oldstuff.address,
oldstuff.networth)" Which of course, in ths example, makes absolutely no
sense......
5) I think your datamodel is wrong...

Note: Oldstuff and newstuff do not reference a table, but a record.

Shakespeare

"Krista" <ywanip (AT) gmail (DOT) com> schreef in bericht
news:1193017317.584469.269100 (AT) e34g2000pro (DOT) googlegroups.com...
How about:

if newstuff.networth > 50000 then newstuff.networth := oldstuff.networth;
end if;

or

if newstuff.networth > 50000 then newstuff.networth := 50000; end if;

I think this meets your specs (though no messages or whatever to the
performer of the update....)

Shakespeare

On Oct 21, 8:41 pm, Krista <ywa... (AT) gmail (DOT) com> wrote:
Pretty much everything, sad to say. Your comparison is incorrectly
written, you can't delete from the table which fired the trigger
(mutating table error), same for the insert (mutating table error).
And your select gets the average (as stated in another post), which
doesn't do you any good. A 'proper' solution would be:

SQL> create table movieexec(
2 name varchar2(30),
3 address varchar2(30),
4 networth number,
5 constraint movieexec_pk
6 primary key(name));

Table created.

SQL>
SQL> create or replace trigger netwrth
2 before insert or update on movieexec
3 for each row
4 begin
5 if :new.networth >= 50000 then
6 :new.networth := 49999;
7 end if;
8 end;
9 /

Trigger created.

SQL>
SQL> insert all
2 into movieexec
3 values ('Trouthammer Krautwig', '1 Crustacean Circle', 45000)
4 into movieexec
5 values ('Troutwig Krauthammer', '17 Lobster Drive', 50000)
6 into movieexec
7 values ('Grackle Smacknerster', '413 Oyster Way', 55000)
8 select * from dual;

3 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select name, address, networth
2 from movieexec;

NAME ADDRESS
NETWORTH
------------------------------ ------------------------------
----------
Trouthammer Krautwig 1 Crustacean Circle
45000
Troutwig Krauthammer 17 Lobster Drive
49999
Grackle Smacknerster 413 Oyster Way
49999

SQL>

Notice the networth values of 50000 and higher resulted in a stored
value of 49999, which satisfies your condition of "we want the net
worth of any executive to be below $50000." Also notice how that was
accomplished:

SQL> create or replace trigger netwrth
2 before insert or update on movieexec
3 for each row
4 begin
5 if :new.networth >= 50000 then
6 :new.networth := 49999;
7 end if;
8 end;
9 /

A simple assignment is all that's necessary, and the value compared is
the value submitted for the current insert/update transaction. No
average is required, or desired, to make this work.

I hope this helps.


David Fitzjarrell

On Sun, 21 Oct 2007 18:41:57 -0700, Krista <ywanip (AT) gmail (DOT) com> wrote:

Why use a TRIGGER? Perhaps a CONSTRAINT can be ADDed to the TABLE to
only only less then that salary.

ALTER TABLE MovieExec ADD CONSTRAINT xxx CHECK (NetWorth < 50000);

This way the record is rejected with an error, as opposed to secretly
changed.

B.

On Oct 22, 9:19 am, Brian Tkatch <N/A> wrote:
It depends, really, on the goal the instructor has set and which tools
are available to meet that goal. Both are valid 'solutions' to the
basic problem, however we know nothing of the specifics of
implementation the instructor has set forth. The OP is learning how
to write triggers as stated in the original post; I expect that's the
solution with which she's expecting assistance.


David Fitzjarrell