Solved

Checking the value before truncate/insert

Posted on 2013-07-01
5
456 Views
Last Modified: 2013-07-02
I have an application (apex)which truncates the load the data into following table from spreadsheet.

When it truncates and load, I need to ensure that the value of a column doesnt exist in another table

Lets say the spread has 2 columns below:

A      B
xyz    1123
lmn    1234


the app should truncate and insert above records into TAB_A.

I have another table TAB_B with following columns:

A     D
xyz  3543
pqr  2345

But if any of the value of COL A in the spreadsheet exists in TAB_B, it should abort inserting any records and throw the value to the application which already has this value( in this case A)

Please let me know if this can be done using a trigger?

Thanks,
0
Comment
Question by:gs79
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39292318
you can do this with a row level before insert trigger right.

Just do a query on the table b to see if the value in the spreadsheet is found there and if yes, then raise an error with raise_application_error(..)

example urls are given below :

http://stackoverflow.com/questions/6415340/raise-application-error-issue
http://www.tek-tips.com/viewthread.cfm?qid=142819
0
 

Author Comment

by:gs79
ID: 39295472
Thanks..I wrote the below trigger and it says subquery not allowed here. How can I check if the value exists in tab b

CREATE OR REPLACE TRIGGER  "CHECK_ID_VAL"
BEFORE INSERT OR UPDATE
ON tab_a
FOR EACH ROW
BEGIN
if :NEW.id in (select id from tab_b)
    THEN
            RAISE_APPLICATION_ERROR(-20101, 'ID exists in table Please remove from the file and re-load');
END IF;
END CHECK_ID_VAL;

please let me know how to fix this..

Thanks,
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 39295479
try something like the below :

CREATE OR REPLACE TRIGGER  "CHECK_ID_VAL"
BEFORE INSERT OR UPDATE
ON tab_a
FOR EACH ROW
declare
temp_var number:=0;
BEGIN

select count(1) into temp_var from tab_b where id = :new.id;

if temp_var >= 1    THEN
            RAISE_APPLICATION_ERROR(-20101, 'ID exists in table Please remove from the file and re-load');
END IF;
END CHECK_ID_VAL;
/
0
 

Author Comment

by:gs79
ID: 39295490
works like charm!

Thanks
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39295536
Good and thanks.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question