Trigger for denormalizing data

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!
eviglottiAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
LowfatspreadConnect With a Mentor Commented:
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
 
Paul JacksonSoftware EngineerCommented:
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
 
eviglottiAuthor Commented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Paul JacksonSoftware EngineerCommented:
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
 
eviglottiAuthor Commented:
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
 
eviglottiAuthor Commented:
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
 
LowfatspreadCommented:

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
 
eviglottiAuthor Commented:
Perfect, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.