Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Checking the value before truncate/insert

Posted on 2013-07-01
5
Medium Priority
?
472 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 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 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