SQL Server Performance

Trigger On Insert

Discussion in 'SQL Server 2005 General Developer Questions' started by linok29, Apr 6, 2010.

  1. linok29 New Member

    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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[TriggerTest]([ID] [nvarchar](50) NOT NULL,[Price] [money] NOT NULL) ON [PRIMARY]---------------------------------Table 2:---------------------------------SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 INSERTAS BEGIN SET NOCOUNT ON;DECLARE @ID intDECLARE @Price moneyDECLARE @OldPrice moneyDECLARE @NewPrice moneyENDSELECT @ID = i.ID, @Price = i.Price,@OldPrice=d.Price, @NewPrice=i.PriceFROM Inserted i INNER JOIN Deleted d ON i.ID = d.ID PRINT @OldPricePRINT @NewPriceIF (@OldPrice <> @NewPrice)BeginINSERT INTO ArchivePriceVALUES(@ID,@OldPrice,@NewPrice)EndGO--------------------------------------------I want this trigger to fire on following stored Procedure.-------------------------------------------CREATE PROCEDURE InsertTriggerTest @Price moneyASBEGINDelete TriggerTest where ID=1Insert into TriggerTest Values(1,@Price)ENDGO-------------------------------------------------Eg. Suppose i have following values in TriggerTestID+++++Price 1+++++10And if somebody wish to changes this price to 20 in TriggerTest table LikeID+++++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. Adriaan New Member

    How is the DELETE + INSERT being handled? Sounds as if it may be handled in a stored procedure. Then let that stored procedure log the full information wherever you want it to be logged, and don't worry about triggers - not worth the trouble if they insist on doing DELETE+INSERT instead of a single UPDATE.

Share This Page