June 16, 2010 at 6:12 pm
Does anyone have sample queries to use with "mirror table" auditing?
Using examples from Steve Jones' "Auditing Your SQL Server - Part 2": http://www.sqlservercentral.com/articles/auditingyoursqlserverpart2/933/
Any queries such as
1) Change history for a particular column?
2) Which column(s) changed in a given update?
Here is a cursor I came up with to see the change history for the OrderDetails.Discount column.
How can this be done without a cursor? Only show log records where Discount changed?
/* Create sample [OrderDetails] table */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--drop table [OrderDetails]
CREATE TABLE [dbo].[OrderDetails](
[OrderID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL CONSTRAINT [DF_OrderDetails_UnitPrice] DEFAULT (0),
[Quantity] [smallint] NOT NULL CONSTRAINT [DF_OrderDetails_Quantity] DEFAULT (1),
[Discount] [real] NOT NULL CONSTRAINT [DF_OrderDetails_Discount] DEFAULT (0),
CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED
(
[OrderID] ASC,
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
/* Create log table */
--drop table OrderDetails_Log
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OrderDetails_Log](
[ID] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[Quantity] [smallint] NOT NULL,
[Discount] [real] NOT NULL,
[modifiedby] [varchar](80),
[modified] [datetime] NOT NULL DEFAULT (getdate()),
[action] [char](1)
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/* trigger to track INSERTS to OrderDetails */
--drop trigger tri_OrderDetails_Insert
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Trigger tri_OrderDetails_Insert on OrderDetails
for insert
as
Set Nocount On
insert OrderDetails_Log
select
*
, suser_sname()
, getdate()
, 'I'
from inserted i
return
/* trigger to track UPDATES to OrderDetails */
--drop trigger tri_OrderDetails_Insert
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Trigger tri_OrderDetails_Update on OrderDetails
for Update
as
Set Nocount On
insert OrderDetails_Log
select
*
, suser_sname()
, getdate()
, 'U'
from inserted i
return
--truncate table OrderDetails
/* Add some data to [Order Details] */
INSERT INTO [dbo].[OrderDetails]
([OrderID] ,[ProductID] ,[UnitPrice] ,[Quantity] ,[Discount])
Select '10248','11','14.00','12','0' Union All
Select '10248','42','9.80','10','0' Union All
Select '10248','72','34.80','5','0' Union All
Select '10249','14','18.60','9','0' Union All
Select '10249','51','42.40','40','0' Union All
Select '10250','41','7.70','10','0' Union All
Select '10250','51','42.40','35','0.15' Union All
Select '10250','65','16.80','15','0.15' Union All
Select '10251','22','16.80','6','0.05' Union All
Select '10251','57','15.60','15','0.05' Union All
Select '10251','65','16.80','20','0' Union All
Select '10252','20','64.80','40','0.05' Union All
Select '10252','60','27.20','40','0' Union All
Select '10253','31','10.00','20','0' Union All
Select '10253','39','14.40','42','0' Union All
Select '10253','49','16.00','40','0' Union All
Select '10254','24','3.60','15','0.15' Union All
Select '10254','55','19.20','21','0.15' Union All
Select '10254','74','8.00','21','0';
/* review data */
select * from OrderDetails
/* make some random updates */
update OrderDetails
set Discount = 0.25
where Discount = 0
update OrderDetails
set Discount = (Discount * 2)
update OrderDetails
set UnitPrice = (UnitPrice * .9)
update OrderDetails
Set Discount = (Discount * .75)
where OrderID < 10251
update OrderDetails
set UnitPrice = (UnitPrice * 1.1)
update OrderDetails
Set UnitPrice = (UnitPrice * 1.1)
where OrderID = 10248
update OrderDetails
Set UnitPrice = (UnitPrice * 1.1)
where OrderID = 10249
select * from OrderDetails
select * from OrderDetails_Log order by OrderID, ProductID, modified
--Stored Procedure to show history of changes to OrderDetails.Discount
IF EXISTS (
SELECT *
FROM sysobjects
WHERE name = N'OrderDetailsDiscountChange'
AND type = 'P'
)
DROP PROCEDURE OrderDetailsDiscountChange
GO
Create Procedure OrderDetailsDiscountChange
@OrderID int, @ProductID int
as
Set NoCount On
declare @DiscountChg table (
[ID] [int] NOT NULL,
[OrderID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[Discount] [real] NOT NULL,
[modifiedby] [varchar](80),
[modified] [datetime] NOT NULL DEFAULT (getdate()),
[action] [char](1))
declare @prev_Discount real
Insert Into @DiscountChg ([ID],[OrderID],[ProductID],[Discount],
[modifiedby],[modified],[action])
select [ID],[OrderID],[ProductID],[Discount],
[modifiedby],[modified],[action]
from OrderDetails_Log
where OrderID = @OrderID
AND ProductID = @ProductID
order by OrderID, ProductID, modified
--Set @prev_Discount = original inserted Discount
Select top 1 @prev_Discount = Discount
from @DiscountChg
Where action = 'I'
order by OrderID, ProductID, modified
declare delCursor Cursor for
Select [Discount], [ID]
from @DiscountChg
Where action = 'U'
order by OrderID, ProductID, modified
declare @discount real, @ID int
Open delCursor
Fetch Next from delCursor into @discount, @ID
WHILE (@@fetch_status <> -1)
Begin
--if Discount is equal to previous Discount then delete from temp table, otherwise it was updated
If @prev_Discount = @discount
Begin
delete from @DiscountChg
where ID = @ID
End
Set @prev_Discount = @discount
Fetch Next from delCursor into @discount, @ID
End
Close delCursor
Deallocate delCursor
select * from @DiscountChg
Set NoCount Off
Go
--run procedure
Exec OrderDetailsDiscountChange 10248, 72
June 22, 2010 at 11:32 am
Anyone, Anyone 🙂 Is it not possible to replace the cursor in the stored procedure with a set based solution? The cursor works and fulfills my business requirement - I am simply looking to expand my SQL skills and get away from cursors and while loops if possible.
In the example code, there is 1 insert and 7 updates to the OrderDetails table. The sproc shows the original insert plus only the 3 changes to the Discount (not the other 4 updates that did not affect Discount).
I am stuck with SQL 2000 in this case but would love to see a SQL 2005/2008 solution as well if this can't be done in SQL 2000.
Thanks for any ideas.
Cheers
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply