Home » SQL & PL/SQL » SQL & PL/SQL » Recording Update through trigger based on csv file upload
Recording Update through trigger based on csv file upload [message #667532] Thu, 04 January 2018 06:26 Go to next message
oracbeg
Messages: 16
Registered: July 2017
Junior Member
Hello Everyone. 

 

Request you to please suggest me some way on this. 

I have a csv file which i am uploading to a table using UTL_FILE. This activity runs on daily basis where file name remains same but data in there changes (insertion or update).  

I need to record that particular row with required columns which was changed in the csv file.



Created a procedure and  used below as condition which works for upload part.


   merge into test using dual on (Id = v_Id)
   when not matched then insert (Id, name, address) 
   values (v_Id, v_name, v_address)
   when matched then update set 

  name = v_name,  address = v_address;


 For recording that update i used a trigger on this table (test)


create or replace TRIGGER test_trg before update on test for EACH ROW 

begin

If Updating Then

insert into test_log 
(name, address
)
values
(:new.name,

:new.address

);




The trouble i am facing is that instead of recording that single record which is changed in table test using utl_file upload it records whole table values of required columns.

Trigger works fine if i manually edit or update the test table but in case of UTL_FILE upload procedure trigger records whole table data of required columns but i need only the row which i actually changed in csv file and in database table.

[Updated on: Thu, 04 January 2018 06:30]

Report message to a moderator

Re: Recording Update through trigger based on csv file upload [message #667533 is a reply to message #667532] Thu, 04 January 2018 06:40 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Would something like this help?
if updating and :new.name <> :old_name 
then
   insert into test_log 
     (name, address)
      values
     (:new.name, :new.address);
end if;
Re: Recording Update through trigger based on csv file upload [message #667535 is a reply to message #667533] Thu, 04 January 2018 09:03 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
The above will work only if column is NOT NULL, otherwise:

if updating and not (:new.name = :old.name or (:new.name is null and :old.name is null))

SY.

[Updated on: Thu, 04 January 2018 09:10]

Report message to a moderator

Re: Recording Update through trigger based on csv file upload [message #667594 is a reply to message #667535] Sun, 07 January 2018 22:33 Go to previous message
oracbeg
Messages: 16
Registered: July 2017
Junior Member
Thank you so much for your reply Smile . It worked and figured it out. As i mentioned that i am using utl_file to upload a csv file. A unusual issue i am facing is that it is uploading csv data in double quotes. I think it is is due to a column having data with line breaks like RAFFINERIE HEIDE 6.375% 17-01/12/2022. I am using something like this v_Securityname := REGEXP_SUBSTR(v_NewLine, '("[^"]*"|[^,]+)', 2, 3); Any way i could get over these line breaks and including that i need to skip headers while uploading csv file.

[Updated on: Mon, 08 January 2018 00:44]

Report message to a moderator

Previous Topic: SQL query
Next Topic: Change the order of records in some variables
Goto Forum:
  


Current Time: Thu Mar 28 18:57:09 CDT 2024