Checking the value before truncate/insert

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,
gs79Asked:
Who is Participating?
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
gs79Author Commented:
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
 
gs79Author Commented:
works like charm!

Thanks
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Good and thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.