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.AppStatusRowIDTable 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 fromApplicant.AppStatusRowID, Applicant.FirstName, Applicant.Lastname, Location.LocNumber, Location.LocationName, Company.CompanyCode, AppStatus.DisplayTextThe joins would be :Select * from Applicant AInner Join AppStatus ast on ast.RowID = a.AppStatusRowIDInner Join Location l on l.RowID = a.LocationRowIDInner Join Company c on c.RowID = l.CompanyRowIDThis 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 joinAny advice is apprecited. |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2009-10-21 : 05:40:36
|
| [code]SET QUOTED_IDENTIFIER, ANSI_NULLS ONGOCREATE TRIGGER tu_Applicant_AppStatusON dbo.ApplicantAFTER UPDATEASSET NOCOUNT ONINSERT 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.RowIDGO[/code] |
 |
|
|
|
|
|
|
|
SQL trigger joins