SQL Server Performance

Writing Trigger

Discussion in 'General Developer Questions' started by aruna_mathew, Sep 20, 2006.

  1. aruna_mathew New Member

    Hi All,

    User Table

    a. Id - Unique Identifier
    b. Active - bool
    c. TransactionNumber - Integer
    d. CreatedBy - varchar (20)
    e. CreatedOn - DateTime
    f. ModifiedBy - varchar(20)
    g. ModifiedOn - DateTime
    h. FirstName -Varchar(20)
    i. LastName - Varchar (20)
    j. UserId - Varchar (10)
    k. Date Of Birth - dateTime

    The TransactionNumber will always be incremented by one every time the row is modified or deleted.
    (It will be set to 1 when the row is initially created).
    Need to Write a trigger that validates that the transaction number = 1 if it is an insert and that the transactionNumber = old transaction number + 1 if it is an update.


    Please Help Me Out

    With Regards
    Aruna Mathew

  2. dineshasanka Moderator

    create triger InsertTRGNAME on User
    for insert
    as

    begin
    declare @TransactionNumber int
    select @TransactionNumber = TransactionNumber from inserted
    if @TransactionNumber = 1
    begin
    --your code
    end
    end

    create triger UpdateTRGNAME on User
    for update
    as

    begin
    declare @preTransactionNumber int
    declare @newTransactionNumber int

    select @preTransactionNumber = TransactionNumber from inserted
    select @preTransactionNumber = TransactionNumber from deleted

    if @preTransactionNumber + 1 = preTransactionNumber
    begin
    --your code
    end
    end

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

  3. aruna_mathew New Member


    Hi Dineshasanka,

    Thank you for giving the code, But what should I write in "--Your Code" content.
    Please provide me the answer.

    Thanking You

    Aruna Mathew

Share This Page