Solved

Checking the value before truncate/insert

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Remove Hyphens in Oracle SQL 5 44
Oracle SQL Char delimited error 5 30
Left Justify field in Oracle 6 69
SSN Format in Oracle 2 59
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
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 video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now