Jump to content
oracbeg

Recording Update through trigger based on csv file upload

Recommended Posts

Hello Everyone. 

 

Request you to please suggest  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.

 

I 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 after 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.

Share this post


Link to post
Share on other sites

Hello Mate,

This is because the trigger is activated for all the rows and not for the particular rows which are updated. 

In your trigger, kindly change the IF UPDATING clause to ,

if updating and nvl(:new.ID,1) <> nvl(:old.ID,0)

This will log only if there is a difference in your ID from your previous one,

Please let us know how it goes after this change.

 

Cheers,

Boobal Ganesan

 

Share this post


Link to post
Share on other sites
Home » SQL & PL/SQL » SQL & PL/SQL » Recording Update through trigger based on csv file upload
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator 
E-mail to friend  | Unsubscribe from topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
- Recording Update through trigger based on csv file upload [message #667532] Thu, 04 January 2018 06:26 Go to next message
39c38311c08c8486dd03002ae1044dec?s=64&d=mm&r=g%20alt= oracbeg 
Messages: 15
Registered: July 2017
Junior Member
 
Hello Everyone. 

 

Request you to please suggest options for 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

msg_about.gif Send a private message to this user   msg_reply.gif  msg_quote.gif
- 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
72104.gif Littlefoot 
Messages: 21310
Registered: June 2005 
Location: Croatia, Europe
Senior Member
Account Moderator
add to buddy list
ignore all messages by this user
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;
msg_about.gif Send a private message to this user   msg_reply.gif  msg_quote.gif
- 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: 2697
Registered: January 2010 
Location: Connecticut, USA
Senior Member
add to buddy list
ignore all messages by this user
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

msg_about.gif Send a private message to this user   msg_reply.gif  msg_quote.gif
- 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
39c38311c08c8486dd03002ae1044dec?s=64&d=mm&r=g%20alt= oracbeg 
Messages: 15
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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×