plsql - How can i fix my trigger to automatically determine if first donation and update single column -

i've hit wall trying create trigger table. have donation table has column (first_donation) checked 'y' or 'n' depending whether donor's first donation or not. want automate process trigger automatically adds 'y' or 'n' if new donation added. if donor submits new donation or first donation column (first_donation) adjusted reflect y or n. first attempt @ triggers , did not seem daunting @ first , language seemed easier last book on java script. guidance appreciated.

   create or replace trigger first_donation_tg       after        insert of donation_id  --id of donation pledge       on tt_donate       each row       begin       if inserting       update tt_donation       set first_donation := 'y';       else        set first_donation := 'n';    end if;    end first_donation_tg; 

below you'll find working example should loking for. don't explain details of basic trigger syntax can read fine manual: pl/sql triggers.

before insert trigger allows modify inserted values before row inserted table (note use of :new pseudorecord gives access row-to-be-inserted). here peek table if donor has made donations or not , adjust flag value. note @ point table doesn't have row we're going insert.

please note example table constraints guard data has valid values.

example table

create table donation (  donator_id number not null ,amount number not null check(amount > 0) ,first_ varchar2(1) not null check(first_ in ('y', 'n')) ,date_ date default sysdate not null );  -- ensure searching donators effective create index donation_idx1 on donation(donator_id); 

trigger

create or replace trigger donation_trg1 before insert on donation each row declare   v_first_exists number; begin   -- check if donator has made donation   select count(*) v_first_exists   donation   donator_id = :new.donator_id   -- we're interested if donation exists therefore   -- don't need count beyond 1   , rownum = 1;    -- adjust value before database inserts row   :new.first_ :=     case       when v_first_exists = 0 'y'       else 'n'     end; end; / 

example run

insert donation(donator_id, amount) values(1, 10); insert donation(donator_id, amount) values(1, 20); insert donation(donator_id, amount) values(1, 30); insert donation(donator_id, amount) values(2, 10); insert donation(donator_id, amount) values(2, 20); insert donation(donator_id, amount) values(2, 30);  select * donation;  donator_id     amount first_ date_ ---------- ---------- ------ -------------------          1         10 y      2015-07-22 09:20.10          1         20 n      2015-07-22 09:20.10          1         30 n      2015-07-22 09:20.10          2         10 y      2015-07-22 09:20.10          2         20 n      2015-07-22 09:20.10          2         30 n      2015-07-22 09:20.10   6 rows selected 

hope helps !