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

"Ana C. Dent" <anacedent_at_hotmail.com> schreef in bericht news:skUSi.10962$oC3.5760_at_newsfe08.phx...
> Krista <ywanip_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
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 Received on Mon Oct 22 2007 - 06:53:16 CDT