Need help sql 2008 trigger

Trigger.xlsxHello im not really familiar with triggers in sql 2008

Here is what ive been ask if i could do it

i need to create a trigger on insert and update

When the field "NOM_FRANCAIS" from table "ARBRES" change i need to update 4 others fields ("NOM_LATIN" + "TYPE" + " HAUTEUR_MAT" + "LARGEUR_MAT"

I have 85 differents case (look at my excel file)

Can someone please help me with this triggers

Thanks alot !!
Jean-François GuénetNetwork AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you are not familiar with triggers, you should first get familiar with them before trying to use them..

technical reference (which includes some examples on the bottom of the page):
http://msdn.microsoft.com/en-us/library/ms189799.aspx

after that, you would need to attach the excel file or better explain what the "issues" are...
0
Jean-François GuénetNetwork AdministratorAuthor Commented:
Ok thanks ill check that

The excel file is attached...i see it in my question at the top

Trigger.xlsx
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I would not use that with "cases", but fill that excel data into a table, and use that in the trigger code for lookup and the update.
again: you should be able to use the basic trigger samples from the tech link above, combine with this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html

in short (I don't have a sql box now availble to test any code):
UPDATE T
  set t."NOM_LATIN" =  isnull(l."NOM_LATIN", t."NOM_LATIN")
  , t."TYPE" =  isnull(l."TYPE", t."TYPE")
  , t."HAUTEUR_MAT" =  isnull(l."HAUTEUR_MAT", t."HAUTEUR_MAT")
  , t."LARGEUR_MAT" =  isnull(l."LARGEUR_MAT", t."LARGEUR_MAT")
FROM INSERTED i
JOIN ARBRES t
   ON t.key_field = i.keyfield
LEFT JOIN excel_data_table l
   ON l.nom_francais = t.nom_francais

Open in new window


just fill in you primary key field of the table ARBRES, and put that inside of the normal trigger basic code, and you should be done
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jean-François GuénetNetwork AdministratorAuthor Commented:
Hi thanks for your help

Here is what i did

I imported my excel file in sql management to create a new table called "ARBRES_SPEC" (see my attached file for the design of the 2 tables "ARBRES" and "ARBRES_SPECS"

Now here is the code

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER dbo.UPDATE_TRIGGER_NOM_FRANCAIS
   ON  dbo.ARBRES
   AFTER INSERT,UPDATE
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for trigger here
      IF (UPDATE(NOM_FRANCAIS))
      BEGIN
            UPDATE T
                  SET t."NOM_LATIN" =  isnull(l."NOM_LATIN", t."NOM_LATIN")
                        , t."TYPE" =  isnull(l."TYPE", t."TYPE")
                        , t."HAUTEUR_MAT" =  isnull(l."HAUTEUR_MAT", t."HAUTEUR_MAT")
                        , t."LARGEUR_MAT" =  isnull(l."LARGEUR_MAT", t."LARGEUR_MAT")
                  FROM INSERTED i
                  JOIN ARBRES t
                  ON t.JMAP_ID = i.keyfield
                  LEFT JOIN ARBRES_SPECS l
                  ON l.nom_francais = t.nom_francais
      END
END
GO


For this line

ON t.keyfield = i.keyfield

I need to put like this ?

ON t.JMAP_ID = i.NOM_FRANCAIS

Thanks alot you really helping me :)
ARBRES-DESIGN.png
ARBRES-SPEC-DESIGN.png
0
Jean-François GuénetNetwork AdministratorAuthor Commented:
Ha i think i understand

IVE put

ON t.JMAP_ID = i.JMAP_ID
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Yes that looks like it should work.
0
Jean-François GuénetNetwork AdministratorAuthor Commented:
Thanks it was a pleasure working with you.

You gave me really good explannation

Thanks alot !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.