Solved

Trigger for denormalizing data

Posted on 2011-02-28
8
404 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

790 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