How to create trigger after update on the simple slide

#11

On Mon, 22 Oct 2007 08:55:14 -0700, "fitzjarrell (AT) cox (DOT) net"
<fitzjarrell (AT) cox (DOT) net> wrote:

Quote:

On Oct 22, 9:19 am, Brian Tkatch <N/A> wrote:
On Sun, 21 Oct 2007 18:41:57 -0700, Krista <ywa... (AT) gmail (DOT) 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

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.- Hide quoted text -

- Show quoted text -

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

Ah, i see. I thought "I am practice on create trigger in Oracle."
meant that she was trying to learn to use TRIGGERs because she thought
ti was the solution to her problem. Not that she specifically wanted
to use a TRIGGER.

Oh well.

B.

#12

On Oct 23, 7:08 am, Brian Tkatch <N/A> wrote:

Quote:

On Mon, 22 Oct 2007 08:55:14 -0700, "fitzjarr... (AT) cox (DOT) net"

fitzjarr... (AT) cox (DOT) net> wrote:
On Oct 22, 9:19 am, Brian Tkatch <N/A> wrote:
On Sun, 21 Oct 2007 18:41:57 -0700, Krista <ywa... (AT) gmail (DOT) 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

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.- Hide quoted text -

- Show quoted text -

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

Ah, i see. I thought "I am practice on create trigger in Oracle."
meant that she was trying to learn to use TRIGGERs because she thought
ti was the solution to her problem. Not that she specifically wanted
to use a TRIGGER.

Oh well.

B.- Hide quoted text -

- Show quoted text -
what is "table is mutating, trigger/function may not see it.

B

#13

Krista wrote:

Quote:

On Oct 23, 7:08 am, Brian Tkatch <N/A> wrote:
On Mon, 22 Oct 2007 08:55:14 -0700, "fitzjarr... (AT) cox (DOT) net"

fitzjarr... (AT) cox (DOT) net> wrote:
On Oct 22, 9:19 am, Brian Tkatch <N/A> wrote:
On Sun, 21 Oct 2007 18:41:57 -0700, Krista <ywa... (AT) gmail (DOT) 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
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.- Hide quoted text -
- Show quoted text -
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
Ah, i see. I thought "I am practice on create trigger in Oracle."
meant that she was trying to learn to use TRIGGERs because she thought
ti was the solution to her problem. Not that she specifically wanted
to use a TRIGGER.

Oh well.

B.- Hide quoted text -

- Show quoted text -
what is "table is mutating, trigger/function may not see it.

B

You have violated one of the rules about a trigger referencing the
table on which it is located.

Go to http://tahiti.oracle.com and look up "Mutating Trigger."
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

How to create trigger after update on the simple slide

comp.databases.oracle.misc comp.databases.oracle.misc

Re: How to create trigger after update on the simple slide - 10-23-2007 , 09:08 AM

Re: How to create trigger after update on the simple slide - 10-23-2007 , 04:18 PM

Re: How to create trigger after update on the simple slide - 10-26-2007 , 10:51 AM