Solved

Trigger for denormalizing data

Posted on 2011-02-28
8
373 Views
Last Modified: 2012-08-13
Hello,

I am not totally new to SQL, but I am new to triggers and I know nothing about stored procedures. Let's assume I have a customer database with simple things like customer name, address, city, state and zip. I also have a foreign key to a table about how they heard about us. That table has a primary key and name (let's call it how_heard_key and how_heard_name).

so I have the how_heard_key in my customer database and I want to denormalize the how_heard_name in there as well.

Let's just assume that my front end for capturing data will only have the how_heard_key as part of the insert or update. How do I make a trigger so that anytime a user does any insert or update of the record that it denormalizes the how_heard_name into the customer table?

I think I want to do this behind the scenes with a trigger and not with any stored procedure, at least in this odd case.

Thanks!
0
Comment
Question by:eviglotti
  • 4
  • 2
  • 2
8 Comments
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35003038
It sounds like this how_heard_name is fixed data that the users select from a list rather than something they type in ?
If so the how_heard table is a fixed data or what is sometimes called a lookup table, which will be prepopulated with some fixed data.

"anytime a user does any insert or update of the record that it denormalizes the how_heard_name into the customer table? "
But I thought this isn't being stored in the customer table?



0
 

Author Comment

by:eviglotti
ID: 35003077
Hear me out, I know that what I'm doing is dumb database design. Trust me in that I have an odd situation where I actually need to do this. Yeah, the second table is a lookup table, hence the primary and foreign key.

So how do I do it so that any insert or update deals with the denormalization for me? Can this be done with a trigger.

Don't worry so much about the reason "why" I am doing this. I know it seems dumb :)

Thanks.
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35003249
ok think I understand what you want, so you will actually have a column for how_heard_name in customer table and you want a trigger to get this value from the how_heard table based on the how_heard_id that is used in an insert or update

CREATE TRIGGER updHowHeard
AFTER UPDATE,INSERT AS

Declate @HowHeardName varchar(50)
Select @HowHeardName  = (Select How_Heard_Name From tblHowHeard Where How_Heard_ID = Inserted.How_Heard_Id)
   Update Customer
     Set Customer.how_heard_name = @HowHeardName
   WHERE Customer.ID = Inserted.Id
GO

Open in new window

0
 

Author Comment

by:eviglotti
ID: 35003614
As odd as this may seem to do, this really sounds like exactly what I wanted. Let me try it out and let you know, but this seems like it. Nice and simple, I just didn't know how to do it!

Thanks.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:eviglotti
ID: 35004064
Hello,

My actual implementation is actually with tables called MD_TEST and STATE_TEST. In the MD_TEST table, I have a foreign key to the state_id which is the primary key in the STATE_TEST table. So I am denormalizing the STATE_TEST.state_name into MD_TEST.denorm_state_name. And obviously the md_test_key is the primary key of the MD_TEST table and state_id is the primary key of the STATE_TEST table.

I run the code below and get "Incorrect syntax near 'AFTER". What am I doing incorrectly? I put this below the

SET_ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

stuff that SQL Server 2005 puts by default in a new trigger.



Thanks.
 [
embed=snippet 7999805]
CREATE TRIGGER denormStateName
AFTER UPDATE,INSERT AS

Declare @StateName varchar(50)
Select @StateName  = (Select state_name From STATE_TEST Where state_id = Inserted.state_id)
   Update MD_TEST
     Set MD_TEST.denorm_state_name = @StateName
   WHERE MD_TEST.md_test_key = Inserted.md_test_key
GO

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35004592

ok

1)   you need to code triggers to cater for multiple or single row  actions

2)  always reference the Inserted/Deleted Tables which contain the after/before images of the rows being affected by the
     trigger action (limits the scope of your processing)

3) code triggers very efficiently ( you can easily adversley affect your database performance by coding an inefficient
     trigger)

specifics...

pk   -   the primary key (columns) of the MD_TEST Table (these columns also exist on the Inserted/deleted tables)

so
Update Md_test
   for the state_name
 "where " The from clause...
      the row exists on the Inserted Table (ie its been changed or Inserted)
  Join it to the Md_test Table (This is an after trigger so the row is already updated/exists with the "changed" values
   from the source statements)
  join to the State_test table on the state_id

Where "actual statement"  the value in the State_id doesn't match the value that previously existed on the
   row on the md_test table (from the deleted table copy) as we don't need to update in that case
  and we will avoid firing any other triggers that may exist for the table , and reduce overall processing (logging etc)

  and finally ... test that we do need to actually update in this case... ie the denorm_state_name column doesb't already
    contain the correct value... (several ways that could occur , which i'm not going into at this time)


(i think Jacko72 was unfortunately giving you more of an oracle style of trigger syntax rather than sql server...)




CREATE TRIGGER denormStateName
 on md_test
AFTER UPDATE,INSERT 
AS

   Update MD_TEST
     Set denorm_state_name = s.StateName
    from inserted as I
   inner join Md_test as M
      on I.pk=M.pk
   inner join state_test as s
     on s.state_id=I.state_id
   WHERE not exists (select pk from deleted as d 
                        where d.pk = i.pk
                        and i.state_id=d.state_id)
    and (M.denorm_state_name is null
         or M.denorm_state_name <> s.State_name)         

GO

Open in new window

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 125 total points
ID: 35004622
sorry i mean substitute in the primary key column of the md_test table where i have specified pk....

there is nothing magic about pk... SQL Server doesn't / wont subsititue it for you....

and i could work out what you'd called it if you have given it in the question discourse.
0
 

Author Closing Comment

by:eviglotti
ID: 35004760
Perfect, thanks!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now