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

From: <fitzjarrell_at_cox.net>
Date: Mon, 22 Oct 2007 06:34:36 -0700
Message-ID: <1193060076.294091.59320@i38g2000prf.googlegroups.com>

On Oct 21, 8:41 pm, Krista <ywa..._at_gmail.com> wrote:
> 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

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 Received on Mon Oct 22 2007 - 08:34:36 CDT