Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    16

    Unanswered: reeeeeally easy for you guys!

    can someone post me an example trigger where.. inserting a new record in one table, creates a new record in another table with a new unique identifier incrementing by 1 from the last record.

    so that I can edit it to suit my own work like:

    eg. when I insert new person into people table, using a person_no as key.
    = creates new pay record in pay table ith a pay_id as key like '0004' 1 more than the last record if it was '0003', and then adds that new person_no to the record for reference.

    I know this is a really easy trigger, but it would be my first! Any example to edit would be great! thanks in advance

  2. #2
    Join Date
    Jan 2004
    Posts
    7

    Re: reeeeeally easy for you guys!

    create table tablename2(
    id int IDENTITY (1, 1),
    value varchar(100)
    )

    CREATE TRIGGER Trigger ON TableName
    FOR INSERT
    AS
    declare @value1 varchar(100)
    select @value1=columnName from inserted

    insert into tablename2 values (@value1)




    Originally posted by simple_simon
    can someone post me an example trigger where.. inserting a new record in one table, creates a new record in another table with a new unique identifier incrementing by 1 from the last record.

    so that I can edit it to suit my own work like:

    eg. when I insert new person into people table, using a person_no as key.
    = creates new pay record in pay table ith a pay_id as key like '0004' 1 more than the last record if it was '0003', and then adds that new person_no to the record for reference.

    I know this is a really easy trigger, but it would be my first! Any example to edit would be great! thanks in advance

  3. #3
    Join Date
    Dec 2003
    Posts
    16
    I think I follow this, but my equivalent of identity and value are in different tables. I don't think I can apply this,

    Which is the part of the code to increment new value by +1 of the last?

  4. #4
    Join Date
    May 2003
    Posts
    26
    Which is the part of the code to increment new value by +1 of the last?
    The column 'id' is created as an IDENTITY field, which means the value in this column is automatically entered. It is set to start with 1 and increment by 1 each time a new record is inserted. Notice that the INSERT statement inserts data into the 'value' column only, the correct incremented integer for the 'id' column is calculated and inserted automaticlly.

    This will work if you are creating new tables, if you are working with existing tables and data you would either have to ALTER the table or calculate the new value for 'id' yourself. Take a look at the following code. (I changed the name of your person_no column to people_id, standard naming conventions are a good habit to get into) The trigger will find the maximum current pay_id, increment it by 1, then insert it and the new people_id into the pay table


    use northwind
    go

    CREATE TABLE people
    (people_id int)
    go

    CREATE TABLE Pay
    (pay_id int,
    people_id int)
    go

    CREATE TRIGGER tr_insert_people ON people
    FOR INSERT
    AS
    declare @new_pay_id int
    declare @people_id int

    select @new_pay_id = ISNULL(max(pay_id),0) + 1 from pay
    select @people_id = people_id from inserted

    insert into pay
    (pay_id,people_id)
    values
    (@new_pay_id,@people_id)
    go


    INSERT INTO people
    VALUES (1)

    INSERT INTO people
    VALUES (2)

    INSER INTO people
    VALUES (5)

    SELECT * from people
    SELECT * from pay

    DROP TABLE people
    DROP TABLE pay

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    and what do you suppose will happen when you have this:

    Code:
    INSERT INTO people
        SELECT 1 
    UNION ALL  
        SELECT 2 
         UNION ALL 
        SELECT 3
    ...or any other set based operation?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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