Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2010
    Posts
    4

    Unanswered: Trigger On Insert

    I have one table(ie. TriggerTest) which stores price information and i want log a changed price and current price in another table(ie. ArchivePrice) when the user performs change.I know this can be achieved using update Trigger, but problem here is we are performing Delete + Insert operation instead of Update Query.

    ---------------------------------
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[TriggerTest](
    [ID] [nvarchar](50) NOT NULL,
    [Price] [money] NOT NULL
    ) ON [PRIMARY]
    ---------------------------------

    Table 2:
    ---------------------------------
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[ArchivePrice](
    [ID] [nvarchar](50) NOT NULL,
    [OPrice] [money] NOT NULL,
    [NPrice] [money] NOT NULL
    ) ON [PRIMARY]
    ---------------------------------

    Can anyone here help me find out a way for this?
    I am posting trigger statement that i have tried so far.


    ---------------------------------
    CREATE TRIGGER PriceChangeInsert
    ON TriggerTest
    AFTER INSERT
    AS
    BEGIN

    SET NOCOUNT ON;

    DECLARE @ID int
    DECLARE @Price money
    DECLARE @OldPrice money
    DECLARE @NewPrice money

    END

    SELECT @ID = i.ID, @Price = i.Price,
    @OldPrice=d.Price, @NewPrice=i.Price
    FROM
    Inserted i
    INNER JOIN Deleted d ON i.ID = d.ID

    PRINT @OldPrice
    PRINT @NewPrice

    IF (@OldPrice <> @NewPrice)
    Begin
    INSERT INTO ArchivePrice
    VALUES(@ID,@OldPrice,@NewPrice)
    End

    GO
    --------------------------------------------

    I want this trigger to fire on following stored Procedure.
    -------------------------------------------
    CREATE PROCEDURE InsertTriggerTest
    @Price money
    AS
    BEGIN

    Delete TriggerTest where ID=1

    Insert into TriggerTest Values(1,@Price)



    END
    GO
    -------------------------------------------------

    Eg. Suppose i have following values in TriggerTest

    ID+++++Price
    1+++++10

    And if somebody wish to changes this price to 20 in TriggerTest table Like

    ID+++++Price
    1+++++20

    Then ArchivePricetable should have following entry.

    ID+++++OPrice++++NPrice
    1+++++10++++++++20.

    __________________________________________________ _____________


    Kindly provide some steps to achieve this.

    Regards,
    Nilesh

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The obvious thing of course would be to update the row rather than delete then insert.

    If, for whatever reason, you insist on deleting then inserting you will need to have a delete trigger for the before value and an insert trigger for the new value.

    Also note your trigger is buggy since it assumes there will be only ever one row inserted at a time. Triggers should be written to handle set based operations.

  3. #3
    Join Date
    Apr 2010
    Posts
    4
    Quote Originally Posted by pootle flump View Post
    The obvious thing of course would be to update the row rather than delete then insert.

    If, for whatever reason, you insist on deleting then inserting you will need to have a delete trigger for the before value and an insert trigger for the new value.

    Also note your trigger is buggy since it assumes there will be only ever one row inserted at a time. Triggers should be written to handle set based operations.

    I completely agreed upon your first statement , but this is the way how it is and i need to find a way for this.

    Any hints?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes - you need two triggers, not one, since you are using two operations, not one. So you need to create a delete trigger first to log all deletions. Once you have created and tested that you need to create an insert trigger to log all inserts.

  5. #5
    Join Date
    Apr 2010
    Posts
    4
    Quote Originally Posted by pootle flump View Post
    Yes - you need two triggers, not one, since you are using two operations, not one. So you need to create a delete trigger first to log all deletions. Once you have created and tested that you need to create an insert trigger to log all inserts.
    My Problem is that i can not associate deleted and newly inserted entries as there's no unique key column in this table and even identity column's value is also going to be differed.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Then what you have posted does not match your problem. In your post you know what the value of ID is and you use that for your delete and your insert.

    Your table needs all keys enforced. Why are there no primary keys\ unique constraints?

  7. #7
    Join Date
    Apr 2010
    Posts
    4
    Quote Originally Posted by pootle flump View Post
    Then what you have posted does not match your problem. In your post you know what the value of ID is and you use that for your delete and your insert.

    Your table needs all keys enforced. Why are there no primary keys\ unique constraints?
    I have just assumed Value of ID as 1.

    my table does have primary key constraint and that is for identity column. Once the row is deleted next time ID column is going to have different value.
    That's why can not associate logs before delete and after Insert.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by knilesh View Post
    I have just assumed Value of ID as 1.
    You have - but you also INSERT 1 in to the table in your example which is not a true reflection of your problem.

    An identity is a surrogate key - there should also be a natural key enforced.

    You have several options in descending order of correctness.
    1) Enforce and use the natural key to identify rows
    2) Insert the same identity value in to your table after deleting instead of auto incrementing it
    3) Perform some post processing in your procedure after you've logged the two rows in order to associate them
    4) Nasty - but you might be able to do something in a single transaction by making use of the SPID and an insert date in your logging table

Posting Permissions

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