May 29, 2015 at 10:52 pm
Hi,
This post is related to http://www.sqlservercentral.com/Forums/Topic1690200-3412-1.aspx.
Here is some standalone, sample code:
-- Create Fact Table
DROP TABLE [dbo].[fact_Money]
GO
CREATE TABLE [dbo].[fact_Money](
[MoneyID] [int] IDENTITY(1,1) NOT NULL,
[DayID] [int] NOT NULL,
[Money] [decimal](5,2) NOT NULL,
CONSTRAINT [PK__fact_Money_MoneyID] PRIMARY KEY CLUSTERED
(
[MoneyID] 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
-- Create Dimension Table
DROP TABLE [dbo].[dim_Days]
GO
CREATE TABLE [dbo].[dim_Days](
[DayID] [int] IDENTITY(0,1) NOT NULL,
[Day] [varchar](15) NOT NULL,
CONSTRAINT [PK__dim_Days_DayID] PRIMARY KEY CLUSTERED
(
[DayID] 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
-- Add Integrity Constraint
ALTER TABLE [dbo].[fact_Money] WITH CHECK ADD CONSTRAINT [FK_fact_Money_dim_Days_DayID] FOREIGN KEY([DayID])
REFERENCES [dbo].[dim_Days] ([DayID])
GO
-- Load Tables
INSERT INTO [dbo].[dim_Days] (Day)
VALUES
('Sunday'),
('Monday'),
('Tuesday'),
('Wednesday'),
('Thursday'),
('Friday'),
('Saturday')
GO
INSERT INTO [dbo].[fact_Money] (DayID, Money)
VALUES
(0,0),
(0,$100),
(0,$200),
(1,$300),
(1,$400),
(2,$500),
(2,$600),
(2,$700),
(0,$800),
(1,$900)
GO
-- Create view
DROP VIEW [dbo].[vfact_Money]
GO
CREATE VIEW [dbo].[vfact_Money] AS
SELECT
m.MoneyID,
m.DayID,
d.Day,
m.Money
FROM [dbo].[fact_Money] m
INNER JOIN [dbo].[dim_Days] d
ON m.DayID=d.DayID
GO
Now, open the view in SSMS, edit top 200 rows. Edit as follows:
1) Edit row 1, MoneyID=1, change DayID from 0 to 1.
2) Refresh the view - day has changed from Sunday to Monday
3) View the data in fact_Money. So far, so good.
4) Edit row 8, MoneyID=8, change Day from Tuesday to Thursday
5) Refresh the view - ALL "Tuesdays" are not "Thursdays"
6) View the data in dim_Days. The dimension table has changed. In some scenarios this would be desired - in this scenario it's definitely NOT what I want.
Is there a way that I can edit the Day column in the view, but have that action cause the DayID column to be written to the fact table instead?
I'm wondering if this approach can be used: http://michaeljswart.com/2012/10/triggers-on-views-what-for/. If so, and you can provide a simple example, that would be great. Would I need a separate trigger for each column, or a single "instead of update" trigger for all the other dimension table columns?
If this approach does not work, or is not best practice, can you suggest a better approach? Perhaps my Excel FE would update a staging/temp/utility table under the covers, then calls a SP (or perhaps that staging table has a trigger?) to update the fact table? Then I refresh the view in Excel?
Thanks for any help you can provide.
Regards,
Scott
May 31, 2015 at 3:58 am
Ok, this code works. My "real application" will need to update 8 dim table FK's in the fact table. The dim tables aren't large (< 1K rows), so hopefully this will perform OK.
The other option is to lookup the FK in the FE VBA, then update the fact table with the FK instead of using triggers in SS.
Let me know what approach you think is better.
Thanks,
Scott
drop trigger t_vfact_money_update
go
create trigger t_vfact_Money_update
on vfact_Money
instead of update
as
begin
set nocount on
select * from inserted -- for debugging/testing
select * from deleted -- for debugging/testing
declare @moneyid int
select @moneyid = moneyid from inserted
update fact_money set dayid=(select a.dayid from dim_days a inner join inserted b on a.day=b.day) where moneyid=@moneyid;
end
go
-- Update the view (which updates the fact table via the trigger)
update vfact_money set day='Sunday' where moneyid=1
go
-- Check the results in the view
select * from vfact_money
go
May 31, 2015 at 6:28 am
That's not going to work at all.
declare @moneyid int
select @moneyid = moneyid from inserted
You're assuming there's only ever one row updated at a time. When more than one row is updated, the trigger will have incorrect results.
You need to rewrite it so that it can handle any number of rows in the inserted/deleted pseudotables (and that doesn't mean adding a cursor).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 31, 2015 at 7:41 am
Yep, I ran into this after posting.
Here is my current code against my "real application" tables:
drop trigger vFact_Cases_Instead_Update
go
create trigger vFact_Cases_Instead_Update
on vFact_Cases
instead of update
as
begin
set nocount on
--select * from inserted
--select * from deleted
update fact_Cases set MemberID = d.MemberID from dim_Members d inner join inserted i on (d.CovNo=i.CovNo and d.Suffix=i.Suffix) where fact_cases.CaseID=i.CaseID
update fact_Cases set AnalystID = d.UserID from dim_Users d inner join inserted i on (d.Fullname=i.Analyst) where fact_cases.CaseID=i.CaseID
update fact_Cases set StatusID = d.StatusID from dim_Status d inner join inserted i on (d.Status=i.Status) where fact_cases.CaseID=i.CaseID
update fact_Cases set TypeOfClaimID = d.TypeOfClaimID from dim_TypeOfClaim d inner join inserted i on (d.TypeOfClaim=i.TypeOfClaim) where fact_cases.CaseID=i.CaseID
update fact_Cases set InjuryTypeID = d.InjuryTypeID from dim_InjuryType d inner join inserted i on (d.InjuryType=i.InjuryType) where fact_cases.CaseID=i.CaseID
update fact_Cases set LawyerGroupID = d.LawyerGroupID from dim_LawyerGroup d inner join inserted i on (d.LawyerGroup=i.LawyerGroup) where fact_cases.CaseID=i.CaseID
update fact_Cases set LawyerBranchID = d.LawyerBranchID from dim_LawyerBranch d inner join inserted i on (d.LawyerBranch=i.LawyerBranch) where fact_cases.CaseID=i.CaseID
end
go
In most (all?) cases the updates will be committed a cell at a time from Excel to SS (I need to test a large cut-and-paste in Excel). So, this is going to execute 6 unneeded updates and 1 needed update. And if it was a non-lookup column then no updates would be However, with the current data volumes the performance is fine. If I add indexes to the lookup columns it might be a bit faster.
If there is a way to detect which column was actually modified and conditionally execute the update, and if that would result in a significant performance improvement, then please point me in the right direction.
May 31, 2015 at 8:07 am
Hmmm...I may need to rethink this...
My code works for the lookup columns...but fails miserably on the non-lookup columns. D'oh.
I either:
1) Need to add an additional update for all the other columns. It would be nice if I could do something like:
update fact_Cases set * = * from inserted i where fact_cases.CaseID=i.CaseID
but I'll have to list all the columns instead. Not necessarily so bad - the table structure would rarely change so the code wouldn't need much maintenance.
Or...
2) In my Excel FE use VBA to lookup the FK of the lookup value (similar to the update statements) then generate an update statement that updates the FK rather than the lookup value.
My strong preference is a SS rather than VBA solution.
If I don't use an instead of trigger, I also have to ensure the dim tables don't get improperly updated per my initial post.
I need to think about this further...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply