Results 1 to 6 of 6

Thread: Trigger

  1. #1
    Join Date
    Jun 2008
    Location
    India
    Posts
    96

    Unanswered: Trigger

    All,

    I have one scenario. I have to convert lower case letter to UPPER case automatically. so i have written a trigger to do that. But it is not working.

    create table t1
    (
    eno char(2)
    )

    drop trigger i_t1

    create trigger i_t1
    after insert
    on t1
    update t1
    set eno := upper(new.eno)
    from new
    where t1.eno = new.eno

    insert into t1 values ('aa')


    where i am doing mistake?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Where did you find that trigger syntax? The correct syntax is:
    Code:
    create trigger i_t1
    before insert
    on t1
    for each row
    begin
    :new.eno := upper(:new.eno);
    end;
    Note that:
    1) It must be a BEFORE trigger to change values
    2) It must be a FOR EACH ROW trigger to change values
    3) You don't use an UPDATE statement to change the values

  3. #3
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    Thanks andrew!

    Actually i am new to ORACLE. SO i referred lot of douments and mixed all of them. Thats why i wrote the wrong one.

    can you refer me some good book names which will teach the basic concepts?

  4. #4
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    I am a pure sybase,sql developer. So far i didn't work in ORACLE.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by karthi_syb
    can you refer me some good book names which will teach the basic concepts?
    Everything is here: http://www.oracle.com/pls/db111/portal.all_books

    For triggers in details look here: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/create_trigger.htm

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by karthi_syb
    can you refer me some good book names which will teach the basic concepts?
    I'm afraid I can't, as my beginner days are long behind me! Hopefully someone else here can though.

Posting Permissions

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