Home » SQL & PL/SQL » SQL & PL/SQL » Trigger question
Trigger question [message #200625] Tue, 31 October 2006 09:23 Go to next message
pw1975
Messages: 24
Registered: June 2006
Junior Member
Hi folks,

i am new to trigger programming so i kindly ask for your help.

i have a table with 5 fields, which is truncated frequently and also refilled (but only the first 4 fields. The fifth field has to be filled after each single insert statement via a lookup table.
The statement should lookup something like:

DECLARE tmp_val VARCHAR2(30);
SELECT value INTO tmp_val FROM LOOKUP_TAB WHERE id = :NEW.id
UPDATE CORE_TAB SET 5thfield = tmp_val WHERE ??

i am not sure how to write the UPDATE clause...

Any help is appreciated.
Thanks a lot,
pw
Re: Trigger question [message #200645 is a reply to message #200625] Tue, 31 October 2006 12:27 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Kindly ask this question in the Newbie forum.
Re: Trigger question [message #200664 is a reply to message #200625] Tue, 31 October 2006 17:13 Go to previous message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
If you are trying to do this update within a row trigger, then don't use an update statement; use:

CREATE OR REPLACE TRIGGER core_tab_bir
  BEFORE INSERT ON core_tab
  FOR EACH ROW
DECLARE 
  tmp_val lookup_tab.value%TYPE;
BEGIN
  SELECT value 
  INTO   tmp_val 
  FROM   LOOKUP_TAB 
  WHERE  id = :NEW.id;

  :NEW.5thfield := tmp_val;
END core_tab_bir;
/

Previous Topic: inserting carriage return in an email
Next Topic: How To Create OS folder froom PL/SQL
Goto Forum:
  


Current Time: Mon Dec 05 09:11:15 CST 2016

Total time taken to generate the page: 0.12825 seconds