This posting is an update (with trigger code) from my earlier posting yesterday
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)
The trigger query is:
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
Any ideas?