t1 col1 col2 nishi 5000 lucky 6700 akash 7000 i want that a

t1
col1 col2
nishi 5000
lucky 6700
akash 7000
i want that a query that when i insert 7000 it will show me data already present and data will not insert. if data is not present it will insert.

Question Posted / nishi.swain@gmail.com

2 Answers
2724 Views
MasterCard, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

t1 col1 col2 nishi 5000 lucky 6700 akash 7000 i want that a query that when i i..

Answer / uday

The best thing would be to create a trigger which checks and not allows duplicate data .

Create or replace trigger trig1
before insert or update on t1
for each row
declare
a number (10);
begin
select count(*) into a from t1 where col2 = :new.col2;
if a >= 1 then
raise_application_error(-20123,'data already present and data will not insert');
elsif a = 0 then
dbms_output.put_line('inserted');
end if;
end;

Is This Answer Correct ?

1 Yes

0 No

t1 col1 col2 nishi 5000 lucky 6700 akash 7000 i want that a query that when i i..

Answer / ravi gali

hi u can use DUP_VAL_ON_INDEX exception

SQL> create table test(id number primary key);

Table created.

SQL> set serverout on

SQL>

begin

insert into test values(1);

exception

when DUP_VAL_ON_INDEX then

raise_application_error('duplicate value entered on the row ');

when others then

dbms_output.put_line('if u entered wrong data');

end;

Is This Answer Correct ?

1 Yes

2 No

Post New Answer