Krista wrote:
> On Oct 23, 7:08 am, Brian Tkatch <N/A> wrote:
>> On Mon, 22 Oct 2007 08:55:14 -0700, "fitzjarr..._at_cox.net"
>>
>>
>>
>>
>>
>> <fitzjarr..._at_cox.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.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_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Fri Oct 26 2007 - 10:51:38 CDT
