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.
- 2 Answers
- 2703 Views
- MasterCard, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
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 |
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 |
How to retrieve values from data fields in record variables?
How do we switch from init.ora file to asp file?
Explain a data segment?
what is the difference between UNION AND UNIONALL
how to get the second max val for every group in a table
1.display list of all users from ur data base.what is the query?
How to pass a cursor variable to a procedure?
How to execute a stored program unit?
when a grant option is encountered for a table EMP to a peer sitting beside you who has already having the table of that name (EMP), then what is the result?
Please explain oracle left join with an example?
Explain do view contain data?
How do we get field detail of a table?
Oracle General 
