how to update the colum in the same table after insert one row?

ssathish84 · #1 (**permalink**) 12-04-11, 01:39

hi,
i have a table called student(studentId,name,studentNo).
the studentNo is to be updated with studentId+'some text' after inserting the
row.
if i pass value like student(1,jack)
then the row should be updated like
studentId- 1
studentName- jack
studentNo- SNO1

thanks in advance.

Littlefoot · #2 (**permalink**) 12-04-11, 02:57

A database trigger can do that for you.

Code:

SQL> create table student (studentid number, stu_name varchar2(20), sno varchar2(20));

Table created.

SQL>
SQL> create or replace trigger trg_stu
  2    before insert on student
  3    for each row
  4  begin
  5    :new.sno := 'sno' || to_char(:new.studentid);
  6  end;
  7  /

Trigger created.

SQL> insert into student (studentid, stu_name) values (1, 'Jack');

1 row created.

SQL> insert into student (studentid, stu_name) values (230, 'Jill');

1 row created.

SQL> select * from student;

 STUDENTID STU_NAME             SNO
---------- -------------------- --------------------
         1 Jack                 sno1
       230 Jill                 sno230

SQL>

Mr.Vivek · #3 (**permalink**) 12-06-11, 01:14

create table student (studentid number, stu_name varchar2(20), sno varchar2(20));

If that table all ready have lots of records then
U have to update that column like

UPDATE student
SET SNO = 'Sno'||studentid;
Commit;

ssathish84 · #4 (**permalink**) 12-07-11, 09:05

thank you mr.vivek
it works fine.

i am using hibernate to persist the student (studentid, stu_name,sno) object.while i am saving the student object the sno column is updated through the trigger.after saving the student object i can't get the student object with sno value which is updated by the trigger.how can i get that!pls reply.

Littlefoot · #5 (**permalink**) 12-07-11, 15:22

Quote:

after saving the student object i can't get the student object with sno value which is updated by the trigger

You can't? Why not?

beilstwh · #6 (**permalink**) 12-07-11, 16:20

You tried

select sno from student;

flyboy · #7 (**permalink**) 12-07-11, 16:53

@Littlefoot: I would say the reason is "hibernate". I do not know that tool, but it seems to be "just another interface over database sql to disable most its features".

To the solution: Either issue INSERT statement with RETURNING clause (this is the place where the value of SNO would be returned) or use that formula (it is not that difficult, just a string concatenation) before issuing INSERT and fill SNO column directly with it.

Internet Services

Web Development

Digital Marketing

Consumer Tech