Results 1 to 14 of 14

Thread: create trigger

  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: create trigger

    Hi,
    I've this table TAB1:

    COD_ID............NAME........DESCRIP

    I'd like to create a trigger that when I insert a new record into table TAB1 I get the value of column COD_ID = NAME_0000000001......NAME_0000000002.............N AME_0000000003............NAME_00000000XX......... .......(NAME + 10byte autonumber)


    for example:

    INSERT INTO TAB1 (NAME, DESCRIP)
    VALUES ('AAA', 'THIS IS EXAMPLE');

    select *
    from tab1;

    COD_ID....................NAME............DESCRIP
    AAA_0000000001.........AAA...........THIS IS EXAMPLE


    INSERT INTO TAB1 (NAME, DESCRIP)
    VALUES ('BBB', 'THIS IS EXAMPLE2');


    select *
    from tab1;

    COD_ID....................NAME............DESCRIP
    AAA_0000000001.........AAA...........THIS IS EXAMPLE
    BBB_0000000002.........BBB...........THIS IS EXAMPLE2



    INSERT INTO TAB1 (NAME, DESCRIP)
    VALUES ('XXX', 'THIS IS EXAMPLE3');


    select *
    from tab1;

    COD_ID....................NAME............DESCRIP
    AAA_0000000001........AAA...........THIS IS EXAMPLE
    BBB_0000000002.........BBB...........THIS IS EXAMPLE2
    XXX_0000000003........XXX...........THIS IS EXAMPLE3


    INSERT INTO TAB1 (NAME, DESCRIP)
    VALUES ('CCC', 'THIS IS EXAMPLE4');


    select *
    from tab1;

    COD_ID....................NAME............DESCRIP
    AAA_0000000001........AAA...........THIS IS EXAMPLE
    BBB_0000000002.........BBB...........THIS IS EXAMPLE2
    XXX_0000000003........XXX...........THIS IS EXAMPLE3
    CCC_0000000004........CCC...........THIS IS EXAMPLE4

    .................................................. ..........................
    .................................................. ..........................
    .................................................. ..........................
    .................................................. ..........................
    .................................................. ..........................

    How can I write this trigger to get my output??

    Thanks in advance!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have you considered either of the following
    Code:
    CREATE TABLE tab1 (
       id int identity(1,1)
     , name As char(3)
     , descrip As varchar(50)
    )
    CREATE VIEW view1
    AS
    SELECT name + '_' + Right('000000000000' + Convert(varchar, id), 12) As [lookie]
         , name
         , descrip
    FROM   tab1
    OR
    Code:
    CREATE TABLE tab1 (
       id int identity(1,1)
     , name As char(3)
     , descrip As varchar(50)
     , name + '_' + Right('000000000000' + Convert(varchar, id), 12) As [lookie]
    )
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2002
    Posts
    227
    George,
    thanks but I need a trigger.

    How can I write your code into a trigger?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Who says you need a trigger?
    Is this a homework assignment?
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2002
    Posts
    227
    cod_id is primary key and is varchar2(32)

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by raf
    cod_id is primary key and is varchar2(32)
    What is your database server?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jul 2002
    Posts
    227
    sql server 2000

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There is no such datatype as varchar2 in SQL Server 2000. Which one of your posts is wrong?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jul 2002
    Posts
    227
    sorry,
    cod_id is varchar(32)

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ok - it just so happens to be a valid datatype in Oracle so important to check.

    In George's code: change the id definition to the correct name and definition. Replace this :
    Convert(varchar, id), 12)

    with the name of your column.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    CREATE TRIGGER tr_tab1_I
       ON  tab1
       AFTER INSERT
    AS 
    BEGIN
    	SET NOCOUNT ON;
    
    	update tab1
    	set tab1.lookie = tab1.name + '_' + Right('000000000000' + Convert(varchar, tab1.id), 12)
    	from	tab1
    		inner join inserted on tab1.id = inserted.id
    	where tab1.lookie is null
    END
    GO
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Eww blindman, why?!
    George
    Home | Blog

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Because under your solutions the value is dynamic, not static.
    It is very likely that he does not want the derived value permanently tied to the surrogate key. With the trigger, he can modify the source column values without affecting the derived column value.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Fair point.

    But in my eyes, based off the information provided, this is a calculated value and should performed at run time (i.e. not persisted).

    *Shrug* up to the OP to decide I suppose.
    George
    Home | Blog

Posting Permissions

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