title: Writing a complex trigger
body: <p>This posting is an update (with trigger code) from my earlier posting yesterday</p>
  
  <p>I am using SQL Server 2000. I am writing a trigger that is executed when a field Applicant.AppStatusRowID</p>
  
  <p>Table Applicant is linked to table Location, table Company &amp; table AppStatus.</p>
  
  <p>My issue is creating the joins in my query.</p>
  
  <p>When Applicant.AppStatusRowID is updated, I want to get the values from Applicant.AppStatusRowID, Applicant.FirstName, Applicant.Lastname, Location.LocNumber, Location.LocationName, Company.CompanyCode, AppStatus.DisplayText</p>
  
  <p>The joins would be :</p>
  
  <pre><code>Select * from Applicant A 
  Inner Join AppStatus ast on ast.RowID = a.AppStatusRowID 
  Inner Join Location l on l.RowID = a.LocationRowID 
  Inner Join Company c on c.RowID = l.CompanyRowID 
  </code></pre>
  
  <p>This is to be inserted into an Audit table (fields are ApplicantID, LastName, FirstName, Date, Time, Company, Location Number, Location Name, StatusDisposition, User)</p>
  
  <p>The trigger query is:</p>
  
  <pre><code>SET QUOTED_IDENTIFIER OFF 
  GO
  SET ANSI_NULLS ON 
  GO
  
  CREATE TRIGGER tri_UpdateAppDispo ON dbo.Test_App
  For Update 
  AS
  
  declare @Approwid int
          Declare @triggername sysname
          Set @rowCnt = @@rowcount
          Set @triggername = object_name(@@procid)  
  
          If @rowCnt = 0
                      RETURN
  
          If Update(appstatusrowid)
          BEGIN
  
          -----------------------------------------------------------------------------
                      -- insert a record to the AppDispo table, if AppstatusRowid
                      -- is being Updated
                      -----------------------------------------------------------------------------
                      Insert AppDispo(AppID, LastName, FirstName, [DateTime],Company,Location,LocationName,
                      StatusDispo,[Username])
  
                                  Select d.Rowid,d.LastName, d.FirstName, getDate(),C.CompanyCode,l.locnum,l.locname, ast.Displaytext,
                               SUSER_SNAME()+' '+User  
                                  From     deleted d with(nolock) 
                              Inner join Test_App a with (nolock) on a.RowID = d.rowid
                              inner join location l with (nolock) on l.rowid = d.Locationrowid
                                              inner join appstatus ast with (nolock) on ast.rowid = d.appstatusrowid 
                                              inner join company c with (nolock) on c.rowid = l.CompanyRowid
                                              --Inner Join Deleted d ON a.RowID = d.RowID
                                              --Where (a.rowid = @Approwid)
          END
  GO
  </code></pre>
  
  <p>Any ideas?</p>