Solved

Checking the value before truncate/insert

Posted on 2013-07-01
5
464 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

617 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