Re: How to create trigger after update on the simple slide

From: Krista <ywanip_at_gmail.com>
Date: Sun, 21 Oct 2007 22:43:46 -0700
Message-ID: <1193031826.240987.311380@i38g2000prf.googlegroups.com>

On Oct 21, 9:27 pm, "Ana C. Dent" <anaced..._at_hotmail.com> wrote:
> Krista <ywa..._at_gmail.com> wrote in news:1193022691.631666.152270
> @i38g2000prf.googlegroups.com:
>
>
>
>
>
>
>
> > On Oct 21, 7:57 pm, "Ana C. Dent" <anaced..._at_hotmail.com> wrote:
> >> Krista <ywa..._at_gmail.com> wrote in news:1193017317.584469.269100
> >> @e34g2000pro.googlegroups.com:
>
> >> > 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
>
> >> SQL> SHOW ERROR- Hide quoted text -
>
> >> - Show quoted text -
>
> > 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
>
> It would help if you actaully posted the code along with the results.- Hide quoted text -
>
> - Show quoted text -

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 Received on Mon Oct 22 2007 - 00:43:46 CDT