Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update Triggers

Status
Not open for further replies.

Mus986

Programmer
Mar 8, 2011
3
US
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.
 
Code:
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.
 
Thank you so much for your help Borislav - it works!!!
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top