Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2002
    Posts
    4

    Unanswered: Changing values in inserting record via triggers

    Hi

    Does anyone of you know how I ca create a trigger to do the following

    Table tbs contain 3 columns : total, num_col and alpha_col

    I'm doing an insert in table tbs:
    insert into tbs (total) values ('111aaa')

    Now I want the trigger to split '111aaa' in to parts: An numeric and a characterpart. The trigger should store the numeric part (111) in column num_col. And the characterpart should be stored in alpha_col.

    Can anyone help ?!?!?


    Torsten

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189
    Hi Torsten,

    this is a soultion for Oracle:

    BEGIN
    select trim(translate(:new.field1, '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLM NOPQRSTUVWXYZ',
    ' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWX YZ')) into FieldNum from dual;

    select trim(translate(:new.field1, '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLM NOPQRSTUVWXYZ',
    '1234567890 ')) into fieldChar from dual;

    UPDATE table set fieldnumeric = TO_NUMBER(FieldNum),
    fieldvarchar = fieldChar;
    END;

    Hope this helps ?

    Manfred Peter
    (Alligator Company)
    http://www.alligatorsql.com

  3. #3
    Join Date
    Jan 2002
    Posts
    4
    Originally posted by alligatorsql.com
    Hi Torsten,

    this is a soultion for Oracle:

    BEGIN
    select trim(translate(:new.field1, '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLM NOPQRSTUVWXYZ',
    ' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWX YZ')) into FieldNum from dual;

    select trim(translate(:new.field1, '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLM NOPQRSTUVWXYZ',
    '1234567890 ')) into fieldChar from dual;

    UPDATE table set fieldnumeric = TO_NUMBER(FieldNum),
    fieldvarchar = fieldChar;
    END;

    Hope this helps ?

    Manfred Peter
    (Alligator Company)
    http://www.alligatorsql.com
    Hi Manfred
    Thanks for your answer.
    What I need to do is on a SQL Server.
    I'll try and see, if I can modify your suggestion, so it fits into a SQLServer environment. But I can't find a SQLServer thing like Oracles :new.field1. Do you know if theres a way to reference columns in the current record in a SQLServer trigger ?

    Torsten

  4. #4
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189
    Hello Torsten,

    there must be a way to manipulate the current triggerfields; otherwise its would make less sence to program a trigger.
    Perhaps you try it in the MS Server forum. I haven´t got a documentation af that database, but it can´t be to difficult to figure this out ...

    Greetings
    Manfred Peter
    (Alligator Company)
    http://www.alligatorsql.com

  5. #5
    Join Date
    Jul 2002
    Posts
    1
    You're going to need to reference the inserted object in SQLServer. Like :new.field in Oracle, you can reference inserted.field by using a join between your base table (table1) and inserted.

    Select table1.field from inserted.fieldkey where table1.fieldkey=insert.fieldkey.

    Hope this helps.

    Chris

Posting Permissions

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