Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2011
    Posts
    2

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

    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.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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>

  3. #3
    Join Date
    Dec 2011
    Posts
    25

    Arrow Update Columns

    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;

  4. #4
    Join Date
    Dec 2011
    Posts
    2

    how to retrieve the value generated by the trigger?

    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.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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?

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You tried

    select sno from student;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    @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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •