Results 1 to 8 of 8

Thread: Trigger

  1. #1
    Join Date
    Aug 2002
    Posts
    21

    Unanswered: Trigger

    Hello,
    I am new to Trigger's. Can some one help me. I need to update a column in a table

    Eg. Table name is X and the columns are [A, B, C]

    I need to update the column A. I want this column to have sequential numbering.

    Colunm A
    1
    2
    3
    4

    Right now this column is showing up as <Null>

    Thanks in advance.


    A

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    when do you reset the sequential number? If this never needs to be reset to 1 then just use an identity column, it will autonumber for you.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Aug 2002
    Posts
    21
    Paul,

    The data is and XML/XSD feed into the database. I will try what you have suggested. Is there another solution? Thanks.


    Anu

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    maybe I did not understand your problem. If you are given the data for columns B and C and need to provide the next value for column A you could create a local variable, store the max value for column A and then step through each inserted record updateing Column A with max value + 1, 2, 3 etc.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Aug 2002
    Posts
    21
    Its me again.

    I truncated the table and changed the design of the table where identity is Yes , Identity incremental 1

    When I did the load from XML/XSD the design changed back to what it had been. (ie no identity and no incremental)

    The feed loads the table with 2 fields eg B and C I need to some how populate the table A sequentially. 1,2 3, etc


    Sorry if this is confusing.

    Anu:

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Having a data load process that can modify a table on the fly is Dangerous! Does the B or C column uniquely identify a record?
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Aug 2002
    Posts
    21
    No they do not uniquely identify a record.

    Can I create a stored procedure? Or a Trigger?

    This is a test I am running I need this database information to show up on a web site that has been developed.

    It is not going to affect any production issues.

    Anu

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    considering your limited option how about:

    Code:
    create table x (A int, B char(10), C char(10))
    go
    if object_id('FixA') is not null drop trigger FixA
    go
    create trigger FixA on x
    for insert
    as
    set ansi_warnings off
    declare @counter int, @a int, @Error int
    declare munge cursor for select A from x where A is Null for update of A
    open munge 
    begin transaction
    select @Error = 0
    select @counter = isnull(max(A),0) + 1 from x with (holdlock)
    fetch next from munge into @a
    while (@@FETCH_STATUS = 0) begin
      update x set a = @counter where current of munge
      set @Error = @Error + @@error
      set @counter = @counter + 1
      fetch next from munge into @a
    end
    
    deallocate munge
    
    if (@Error = 0)
      commit transaction
    else
      rollback transaction
    go
    
    truncate table x
    go
    
    insert into x (B,C) values('A','B')
    select * from x
    insert into x (B,C) values('B','C')
    select * from x
    insert into x (B,C) values('C','D')
    select * from x
    insert into x (B,C) values('E','F')
    select * from x
    go
    I would still try to get the Identity attribute working as this would be your absolut best option.

    The above trigger has the potentioal of being slow and will pronbably hold a table lock or at least page lock while it runs. Without this I don't know of anyother way to make this work when you have multipule inserts from diffrent connections.

    I personaly would NOT use the above approach even if I had to re-design some part of an application, data load or do a schema re-design.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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