April 4, 2010 at 11:56 pm
I have one table which contains product price details.
------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProdPriceDetails](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProdPriceListID] [int] NOT NULL CONSTRAINT [DF__ProdPrice__ProdP__0CBAE877] DEFAULT ((0)),
[IsUnitPrice] [bit] NULL CONSTRAINT [DF__ProdPrice__IsUni__0DAF0CB0] DEFAULT ((0)),
[Range] [nvarchar](120) NULL,
[Price] [money] NULL CONSTRAINT [DF__ProdPrice__Price__0EA330E9] DEFAULT ((0)),
[IsActualPrice] [bit] NULL CONSTRAINT [DF__ProdPrice__IsAct__0F975522] DEFAULT ((0)),
[MRSP] [int] NULL CONSTRAINT [DF__ProdPriceD__MRSP__108B795B] DEFAULT ((0)),
[CreatedBy] [int] NOT NULL CONSTRAINT [DF__ProdPrice__Creat__117F9D94] DEFAULT ((0)),
[CreatedDate] [datetime] NOT NULL,
[LastUpdatedBy] [int] NULL CONSTRAINT [DF__ProdPrice__LastU__1273C1CD] DEFAULT ((0)),
[LastUpdatedDate] [datetime] NULL,
CONSTRAINT [aaaaaProdPriceDetails2_PK] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProdPriceDetails] WITH NOCHECK ADD CONSTRAINT [FK_ProdPriceDetails_ProdPriceList] FOREIGN KEY([ProdPriceListID])
REFERENCES [dbo].[ProdPriceList] ([ProdPriceListID])
GO
ALTER TABLE [dbo].[ProdPriceDetails] NOCHECK CONSTRAINT [FK_ProdPriceDetails_ProdPriceList]
------------------------------------------------------
insert into ProdPriceDetails values(1,0,'a',10,0,0,0,getdate(),0,getdate())
insert into ProdPriceDetails values(2,0,'a',20,0,0,0,getdate(),0,getdate())
insert into ProdPriceDetails values(3,0,'a',30,0,0,0,getdate(),0,getdate())
insert into ProdPriceDetails values(4,0,'a',40,0,0,0,getdate(),0,getdate())
I wish to fire a trigger on this table on insert statement if the price value of row 1 changes from 10 to any other value.
Here instead of updating values i am deleting all the values and inserting them again.
(I am able to get this functionality working if i am updating records but failing if i use delete+insert operation.)
I want to store both old and new price value in new table(Archive_ProdPriceChange)
/****** Object: Table [dbo].[Archive_ProdPriceChange] Script Date: 04/05/2010 11:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Archive_ProdPriceChange](
[ID] [int] NULL,
[ProdPriceListID] [int] NULL,
[ProdID] [int] NULL,
[CountryID] [int] NULL,
[CurrencyID] [int] NULL,
[Price] [money] NULL,
[OldPrice] [money] NULL,
[CreatedBy] [int] NULL,
[CreatedDate] [datetime] NULL,
[LastUpdatedBy] [int] NULL,
[LastUpdatedDate] [datetime] NULL
) ON [PRIMARY]
Here is sql statement that i have tried so far.
--------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER ArchiveProdPriceChangeInsert
ON ProdPriceDetails
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for trigger here
--DECLARE VARIABLES Having one to one Mapping with ProdSubAppLangDependent Table
DECLARE @ID int
DECLARE @ProdPriceListID int
DECLARE @ProdID int
DECLARE @CountryID int
DECLARE @CurrencyID int
DECLARE @Price money
DECLARE @OldPrice money
DECLARE @createdby int
DECLARE @CreatedDate datetime
DECLARE @LastUpdatedBy int
DECLARE @LastUpdatedDate datetime
DECLARE @NewPrice money
END
SELECT @ID = i.ID, @ProdPriceListID = i.ProdPriceListID, @Price = i.Price,
@createdby = i.CreatedBy, @CreatedDate = i.CreatedDate, @LastUpdatedBy = i.LastUpdatedBy,
@LastUpdatedDate = i.LastUpdatedDate , @OldPrice=d.Price, @NewPrice=i.Price
FROM Inserted i
INNER JOIN deleted d ON i.ID = d.ID
--Get relevant details from the ProdPriceList table
SELECT @ProdID = ProdID, @CountryID = CountryID, @CurrencyID=CurrencyID
FROM ProdPriceList
WHERE ProdPriceListID = @ProdPriceListID
--Insert the same into the Archive table
if (@OldPrice <> @NewPrice)
Begin
INSERT INTO Archive_ProdPriceChange
(ID,ProdPriceListID,ProdID,CountryID,CurrencyID,Price,OldPrice,CreatedBy,CreatedDate,LastUpdatedBy,LastUpdatedDate)
VALUES(@ID,@ProdPriceListID,@ProdID,@CountryID,@CurrencyID,@Price,@OldPrice,@CreatedBy,@CreatedDate,@LastUpdatedBy,@LastUpdatedDate)
End
GO
--------------------------------------------------------------
Kindly Provide suggestion for this.
Thanks,
Nilesh
April 5, 2010 at 12:10 am
Inside an INSERT trigger, you will not have any data in the deleted table. Only the inserted will have data.
Why are you preferring delete + insert method over the update method? Any particular reason?
If you want to maintain the old and new value history, you will have to store the deleted values in a seperate table and then compare the inserted values with the data in that table for further processing.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 5, 2010 at 12:24 am
Delete + Insert is as per the business logic so that can not be changed.
Any alternative than storing values in separate table before delete?
April 5, 2010 at 12:34 am
In Case Of Insert You do not have old price of product.when you update a record then u can get old and new price
April 5, 2010 at 12:45 am
nilesh k (4/5/2010)
Delete + Insert is as per the business logic so that can not be changed.Any alternative than storing values in separate table before delete?
One more idea would be to maintain a IsDeleted bit in the table which can be set when the data is suppopsed to be deleted. You should not physically delete the data. You can physically delete it when the corresponding insert happens.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 5, 2010 at 12:54 am
There's no unique key in table so that i can associate 'IsDelete' with new inserted record.
So cant use separate table even.
April 5, 2010 at 1:22 am
Then i would suggest you to create a unique column and then proceed. A system generated IDENTITY column should be fine unless it breaks your existing code or procedures.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 5, 2010 at 2:16 am
well identity columns value is going to differ in delete and insert.so how can i make it worth?
April 5, 2010 at 6:17 am
nilesh k (4/5/2010)
well identity columns value is going to differ in delete and insert.so how can i make it worth?
that is not true; I can understand how it might seem confusing.
the INSERTED and DELETED tables are the virtual tables that have the before and after values of each row affected in the statement which made the trigger event occur.
a row of data can have one(and only one) column populated with an identity() value; that value is inserted before the trigger commences it's work, so if you if you had an empty table, for example, and inserted one row of data, the value of that identity() column in the trigger would be 1.
later if you update or delete that row, that value is still 1; the value does not change...
so you can JOIN the INSERTED and DELETED tableson that column...they will always have the same value for the same rows, which makes it super easy to compare other columns in the table to their new and old values to see if a change occurred or not.
Lowell
April 5, 2010 at 9:19 pm
nilesh k (4/5/2010)
Delete + Insert is as per the business logic so that can not be changed.
This is such a terrible idea that I have to ask. What is the actual requirement for this? Don't say it's the requirement "per the business logic". I want to know why it's a requirement of the business logic because it currently makes no logical sense for it to be done this way and it makes no sense for a developer to not ask "why".
--Jeff Moden
Change is inevitable... Change for the better is not.
April 5, 2010 at 11:10 pm
Jeff Moden (4/5/2010)
nilesh k (4/5/2010)
Delete + Insert is as per the business logic so that can not be changed.This is such a terrible idea that I have to ask. What is the actual requirement for this? Don't say it's the requirement "per the business logic". I want to know why it's a requirement of the business logic because it currently makes no logical sense for it to be done this way and it makes no sense for a developer to not ask "why".
And with the absence of a unique key, the work around though possible would be quite tedious. So changing the business logic seems a bit less tedious in this case.
Lets see whats the reason for the business logic.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 6, 2010 at 1:09 am
Can you give bit more clarification using sql statement for trigger?
April 6, 2010 at 1:14 am
Kingston Dhasian (4/5/2010)
Jeff Moden (4/5/2010)
nilesh k (4/5/2010)
Delete + Insert is as per the business logic so that can not be changed.This is such a terrible idea that I have to ask. What is the actual requirement for this? Don't say it's the requirement "per the business logic". I want to know why it's a requirement of the business logic because it currently makes no logical sense for it to be done this way and it makes no sense for a developer to not ask "why".
And with the absence of a unique key, the work around though possible would be quite tedious. So changing the business logic seems a bit less tedious in this case.
Lets see whats the reason for the business logic.
I completely agree about this thumb rule "Insert=Insert & Update=Update".
Well the logic is already built by one developer and the project is on production server.
This is one requirement which came up when we were done with rest of the things
April 6, 2010 at 1:15 am
Lowell (4/5/2010)
nilesh k (4/5/2010)
well identity columns value is going to differ in delete and insert.so how can i make it worth?that is not true; I can understand how it might seem confusing.
the INSERTED and DELETED tables are the virtual tables that have the before and after values of each row affected in the statement which made the trigger event occur.
a row of data can have one(and only one) column populated with an identity() value; that value is inserted before the trigger commences it's work, so if you if you had an empty table, for example, and inserted one row of data, the value of that identity() column in the trigger would be 1.
later if you update or delete that row, that value is still 1; the value does not change...
so you can JOIN the INSERTED and DELETED tableson that column...they will always have the same value for the same rows, which makes it super easy to compare other columns in the table to their new and old values to see if a change occurred or not.
Can you give bit more clarification using sql statement for trigger?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply