data clean up
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: data clean up

  1. #1
    Join Date
    Jan 2002
    Posts
    474

    data clean up

    I want to create a trigger so that if any time the records is greater than 5 character if it will only insert the first 5 characters

    CREATE OR REPLACE TRIGGER mytest
    BEFORE INSERT
    ON test
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    Begin
    if length(:new.MTR_CTR_NO) >5 then
    :new.MTR_CTR_NO := substr(:new.MTR_CTR_NO,0,5);
    end if;
    End;

    /


    any suggestions???

    Thanks

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003

    Re: data clean up

    Originally posted by ashley75
    CREATE OR REPLACE TRIGGER mytest
    BEFORE INSERT
    ON test
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    Begin
    :new.MTR_CTR_NO := substr(:new.MTR_CTR_NO,1,5);
    End;

    /
    You can do this. By the time you find out how long it is you could have already done the substr function.

  3. #3
    Join Date
    Jan 2002
    Posts
    474
    THIS DOESNOT WORK

    IT COMPLAIN ABOUT ORA-01401: INSERT VALUE TOO LARGE FOR COLUMN
    the data is something like 230400002040000

    any other function I can use or any other suggestions???
    Last edited by ashley75; 12-12-2002 at 03:37 PM.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Your trigger is never firing because you are inserting invalid data. In this case, you may want to look at INSTEAD OF triggers on a view, or creating another column that holds your 5 characters.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Jan 2002
    Posts
    474
    jEFF,

    could you please tell me why it's not firing ???? and why you know it's INVALID data ???

    I can modify the column to hold the number of characters as it insert but that will be my last option.

    Do you have any other advises???

    Thanks

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    You could put a raise_application_error as the first line in the trigger and reinsert the data. If the raise_application_error message comes back then the trigger fired. Otherwise it did not.

    You can either do an instead of trigger, like Jeff suggested, or you can just make the field bigger. You can also truncate the data before the insert. i.e. in your application.

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by ashley75
    jEFF,

    could you please tell me why it's not firing ???? and why you know it's INVALID data ???

    I can modify the column to hold the number of characters as it insert but that will be my last option.

    Do you have any other advises???

    Thanks
    It's not firing because you are inserting invalid data. I can tell it's invalid because you get an ORA-01401 error. The trigger will only fire once it has been determined that INSERT operation CAN happen.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Jan 2002
    Posts
    474
    Gandolf989 and Marist89,

    How could you put the a raise_application_error as the first line in the trigger and reinsert the data.

    I can't modify table and make the column bigger so I have to put in the trigger.

    Can you please help

  9. #9
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    I just meant this as a test. If you get an error with the trigger fired then it fired. If you don't get that then the trigger did not fire. Look into the instead if trigger.

    CREATE OR REPLACE TRIGGER mytest
    BEFORE INSERT
    ON test
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    Begin
    RAISE_APPLICATION_ERROR(-20001, 'The trigger fired');
    End;
    /

Posting Permissions

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



Click Here to Expand Forum to Full Width