Trigger for denormalizing data

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?

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.

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

Select allOpen in new window

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.

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

Select allOpen in new window

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

Select allOpen in new window

Perfect, thanks!