Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CSharpNewbie
Starting Member
39 Posts |
 Posted - 2009-10-20 : 16:44:53
|
Hi,
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
Any advice is apprecited. |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2009-10-21 : 05:40:36
|
[code]
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE TRIGGER tu_Applicant_AppStatus
ON dbo.Applicant
AFTER UPDATE
AS
SET NOCOUNT ON
INSERT INTO AuditTable (<colList>)
SELECT
-- For New Info
I.ApplicantID, I.LastName...
-- -- For Old Info
-- D.ApplicantID, D.LastName...
FROM inserted I
JOIN deleted D
ON I.ApplicantID = D.ApplicantID
AND I.AppStatusRowID <> D.AppStatusRowID
JOIN Location L
-- For New info
ON I.LocationRowID = L.RowID
-- -- For Old info
-- ON D.LocationRowID = L.RowID
JOIN Company C
-- For New info
ON I.CompanyRowID = C.RowID
-- -- For Old info
-- ON D.CompanyRowID = C.RowID
GO
[/code] |
 |
|
|
|
|
|
SQL trigger joins