Trigger On Insert. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trigger On Insert.

Author

Topic

linokd
Starting Member

2 Posts

Posted - 2010-04-06 : 05:53:33

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-06 : 06:58:22

[code]CREATE TRIGGER YourTrigger
ON Table
FOR UPDATE
AS
BEGIN
INSERT INTO ArchivePrice (ID,OPrice,NPrice)
SELECT i.ID,d.Price,i.Price
FROM INSERTED i
JOIN DELETED d
ON i.ID=d.ID
END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources