Writing a complex trigger

Question

I am using SQL Server 2000. I am writing a trigger that is executed when a field Applicant.AppStatusRowID

Table Applicant is linked to table Location, table Company & table AppStatus.

My issue is creating the joins in my query.

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

The joins would be :

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

This is to be inserted into an Audit table (fields are ApplicantID, LastName, FirstName, Date, Time, Company, Location Number, Location Name, StatusDisposition, User)

My issue is the query for the inner join...

Answer 1

First lets introduce you to the inserted and deleted pseudotables which are available only in triggers. Inserted has new values and delted has old values or records being deleted.

You do not want to insert all records into your audit table only those in inserted.

So to insert into an audit table you might want something like inside the trigger code:

insert Myaudittable (<insert field list here>)
Select <insert field list here> from Inserted i
Inner Join AppStatus ast on ast.RowID = i.AppStatusRowID
Inner Join Location l on l.RowID = i.LocationRowID
Inner Join Company c on c.RowID = l.CompanyRowID

I would personally add columns for old and new values, a column for the type of change and what the date of the change and what user made the change, but you I'm sure have your own requirement to follow.

Suggest you read about triggers in Books online as they can be tricky to get right.

Here's one way to test and debug trigger that I often use. First I create temp tables names #delted and #inserted that have the sturcture of the table I'm going to put the trigger on. Then I write the code to use those instead of the deleted or inserted tables. That wa y I can look at things as I go and make sure everything is right before I change the code to a trigger. Example below with you code added in and modified slightly:

  Create table #inserted(Rowid int, lastname varchar(100), firstname varchar(100), appstatusRowid int)
  Insert #inserted
  select 1, 'Jones', 'Ed', 30
  union all
  select 2, 'Smith', 'Betty', 20

  Create table #deleted (Rowid int, lastname varchar(100), firstname varchar(100), appstatusRowid int)
  Insert #deleted
  select 1, 'Jones', 'Ed', 10
  union all
  select 2, 'Smith', 'Betty', 20

 --CREATE TRIGGER tri_UpdateAppDisp ON dbo.Test_App 
 --For Update 
 --AS 
 --If Update(appstatusrowid) 
 IF  exists (select i.appstatusRowid from #inserted i join #deleted d on i.rowid = d.rowid
   Where d.appstatusrowid <> i.appstatusrowid)
 BEGIN 
 --Insert AppDisp(AppID, LastName, FirstName, [DateTime],Company,Location,LocationName, StatusDisp,[Username]) 
 Select d.Rowid,d.LastName, d.FirstName, getDate(),C.CompanyCode,
 l.locnum,l.locname, ast.Displaytext, SUSER_SNAME()+' '+User 
 From #deleted d
 Join #inserted i on i.rowid = d.rowid
 --From deleted d 
 --Join inserted i on i.rowid = d.rowid
 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
 Where d.appstatusrowid <> i.appstatusrowid)
 end

Once you get the data for the select correct, then it is easy to uncomment out the trigger code and the insert line and change #deleted or #inserted to deleted or inserted.

You'll note I had two records in the temp tables, one of which met your condition and one of which did not. This allows you to test mulitple record updates as well as results that meet the condition and ones that don't. All triggers should be written to handle multiple records as they are not fired row-by-row but by batch.