Update Triggers

[Mus986]

Hello,
I have never written a trigger. I need a trigger that updates all contacts associated with a selected employer to inactive when that employer is made inactive. So if the employer, Bank of America is made inactive, I would like all the associated contacts updated to inactive? Any help would be greatly appreciated.

 

[bborissov]

CREATE TRIGGER NameOfTheTrigger OF EmployerTable
FOR Update
AS
BEGIN
   IF UPDATED(ActiveInActiveField)
      BEGIN
        UPDATE ContactsTable 
               SET ActiveInActiveField = INSERTED.ActiveInActiveField
        FROM ContactsTable
        INNER JOIN Inserted 
              ON ContactsTable.EmploterId = Inserted.Id
      END
END

NOT TESTED!!!!!!!!!!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Mus986]

Thank you so much for your help Borislav - it works!!!

 

[gmmastros]

You may want to be a little careful (meaning.... more testing).

Originally, you said, when I make a contact inactive....

The query posted by Boris will also work the other way around too. Meaning.... if you make a contact active, it will make all of the employees active too, which may not be what you want.

I mention this only because it's easier to fix this now than it will be to fix your data later.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom