Solved

Trigger for denormalizing data

Posted on 2011-02-28
8
413 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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
 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

688 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