Home » SQL & PL/SQL » SQL & PL/SQL » Help in writing a trigger
Help in writing a trigger [message #240429] Thu, 24 May 2007 17:02 Go to next message
sreehari
Messages: 101
Registered: May 2006
Senior Member
HI

create or replace trigger contract_site_upd
after insert or update on contract_site for each row
begin
update contract_site set status_code = 'ACTIV'
where status_code is null;
end contract_site_upd;

This is not the correct way of writing the trigger

i think i should make use of :OLD and :NEW values

Please help in correcting this trigger

My intention is when ever there is a null inserted or updated it shoud be updated with 'ACTIV'

Thanks
Re: Help in writing a trigger [message #240430 is a reply to message #240429] Thu, 24 May 2007 17:08 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
why bother with a trigger when you could just use a DEFAULT VALUE?
Re: Help in writing a trigger [message #240431 is a reply to message #240430] Thu, 24 May 2007 17:12 Go to previous messageGo to next message
sreehari
Messages: 101
Registered: May 2006
Senior Member
Application demands trigger. please help me with the one

Thanks
Re: Help in writing a trigger [message #240432 is a reply to message #240429] Thu, 24 May 2007 17:16 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Application demands trigger
MY application does what I code it to do.
Is it your homework instructor wants trigger as the assignment?
Re: Help in writing a trigger [message #240433 is a reply to message #240432] Thu, 24 May 2007 17:19 Go to previous messageGo to next message
sreehari
Messages: 101
Registered: May 2006
Senior Member
i am junior guy with the database in a company..supposed to write the trigger for this task..but failing..but this is some what critical..

Thanks
Re: Help in writing a trigger [message #240434 is a reply to message #240429] Thu, 24 May 2007 17:25 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
1) GOOGLE is your firend, but only if you use it.
2) http://asktom.oracle.com has many fine coding examples.
Re: Help in writing a trigger [message #240456 is a reply to message #240429] Thu, 24 May 2007 21:22 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
First, DEFAULT is applied only for INSERT when the column is not stated. However using trigger may confuse you as simple
UPDATE contract_site SET status_code = NULL;
will update status_code in all rows to 'ACTIV' (you are aware of it, I hope).

Your demand is achievable using NEW variable. however think: you do not want to update all NULL rows in CONTRACT_SITE table, you just want to correct the STATUS_CODE value in the currently inserted/updated row, which can be done in the BEFORE INSERT OR UPDATE trigger (NOT AFTER!) like this:
if :new.status_code is null then
  :new.status_code := 'ACTIV';
end if;
Re: Help in writing a trigger [message #240471 is a reply to message #240429] Fri, 25 May 2007 00:22 Go to previous message
apps.suresh
Messages: 15
Registered: October 2006
Junior Member
create or replace trigger contract_site_upd before insert or update on contract_site for each row

begin
if :new.status_code is null then
:new.status_code := 'ACTIV';
end if;
end;


hope this works for you...

thanks
Previous Topic: HELP STATEMENT WITH IN (merged)
Next Topic: Bitmap index
Goto Forum:
  


Current Time: Sat Dec 10 22:38:55 CST 2016

Total time taken to generate the page: 0.11780 seconds