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.

All Forums  Old Forums  CLOSED - General SQL Server  update trigger issue

Author  Topic  abyie Starting Member 42 Posts Posted - 2006-12-21 : 10:27:34 Hi, I am trying to create an update trigger which will be triggered when a field named LSEComplete on the table named Part on database X get updated would import various fields associated with LSEComplete from more than one table on database X to a table called Contactloadtest on Database Y. Here is my trigger syntax.The trigger execution was successful, but when I tried to test the trigger by updating LSEComplete on one of the records, it didnt import the record.Please help me out. Trigger Syntax SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO ALTER TRIGGER ContacatLoadTableInsert ON Part FOR UPDATE AS IF UPDATE(LSE_Complete_Date) BEGIN INSERT INTO SALESFORCEBK.dbo.Contact_Loadtest (AccountId, Birthdate, Borough__c, CEO_ID__c, CreatedById, FirstName, Gender__c, Gov_t_Issued_Photo_ID_Number__c, HomePhone, LastModifiedById, LastName, MailingCity, MailingPostalCode, MailingState, MailingStreet, Marital_Status__c, MobilePhone, Number_of_Children__c, OtherCity, OtherPostalCode, OtherState, OtherStreet, RecordTypeId, Salutation, Social_Security_Number__c, TABE_Math__c, TABE_Verbal__c, Job_Developer__c, Job_Coach__c, Retention_Specialist__c, Last_Grade_Complete__c, OwnerId, Conviction_1__c, Conviction_2__c, Conviction_3__c, Sentence_Date_1__c, Release_Date_1__c ) select '0015000000F39jCAAR', A.BirthDate, A.BOROUGH, A.CEO_ID, A.Id, A.First_Name, A.GENDER, A.Driver_License_Num, A.Telephone, A.Id, A.Last_Name, C.city, A.Zip, A.State, A.Address, A.MARITAL_STATUS, A.cell_phone_num, A.Number_of_Children, B.alt_city, A.alt_zip, A.alt_state, A.alt_address, '012500000000iRFAAY', A.Salutation, A.SSN, A.math_score, A.reading_score, A.Id, E.Job_Coach, D.Retention_Specialist, A.grade_in_school, A.Id, A.conviction, A.conviction2, A.conviction3, A.sentence_date, A.release_date from (Select i.Part_ID, Convert(varchar(10),i.Birthdate,101) as BirthDate, CASE WHEN LEFT(i.Zip, 3) = '100'THEN 'Manhattan' WHEN LEFT(i.Zip, 3) = '103'THEN 'Staten Island' WHEN LEFT(i.Zip, 3) = '104'THEN 'Bronx' WHEN LEFT(i.Zip, 3) = '112'THEN 'Brooklyn' WHEN (LEFT(i.Zip, 3) = '110' or LEFT(i.Zip, 3) = '111' or LEFT(i.Zip, 3) = '113' or LEFT(i.Zip, 3) = '114' or LEFT(i.Zip, 3) = '116') THEN 'Queens' WHEN LEFT(Zip, 3) is null THEN 'NOT AVAILABLE' ELSE 'Outside of NYC' END AS BOROUGH , i.CEO_ID, U.Id, i.First_Name, CASE WHEN i.Sex = 'M' THEN 'MALE' WHEN i.Sex = 'F' THEN 'FEMALE' ELSE 'NONE' END AS GENDER, pe.Driver_License_Num, i.Telephone, i.Last_Name, i.Zip, i.State, i.Address, CASE WHEN i.Marital_Status = 'S' THEN 'SINGLE' WHEN i.Marital_Status = 'M' THEN 'MARRIED' WHEN i.Marital_Status = 'N' THEN 'NEVER MARRIED' WHEN i.Marital_Status = 'D' THEN 'DIVORCED' ELSE 'NONE' END AS MARITAL_STATUS , i.cell_phone_num, Count(pc1.part_id)AS Number_of_Children, i.alt_zip, i.alt_state, i.alt_address, i.Salutation, i.SSN, ja.math_score, ja.reading_score, ja.grade_in_school, r.conviction, r.conviction2, r.conviction3, Convert(varchar(10),r.sentence_date,101) as sentence_date, Convert(varchar(10),r.release_date,101) as release_date from inserted i left join Part_Employment pe on i.Part_ID = pe.Part_ID left join Part_Children pc1 on i.Part_ID = pc1.part_id left join JTPA_Appl ja on i.Part_ID = ja.part_id left join Users U1 on i.User_ID = U1.User_ID join NtoSUserLookup U on U.User_ID = U1.User_ID left join Release_Paper r on r.part_id = i.Part_ID GROUP BY i.Part_ID,i.CEO_ID,i.Birthdate,i.Zip, pe.Driver_License_Num, i.Telephone, i.First_Name, i.Last_Name, i.Address, i.State, i.Marital_Status, i.cell_phone_num, i.Sex, i.alt_address, i.alt_state, i.alt_zip, U.Id, U1.Job_Title, i.SSN, ja.math_score, ja.reading_score, ja.grade_in_school, i.Salutation, i.Term_Date, r.conviction, r.conviction2, r.conviction3, r.sentence_date, r.release_date)A LEFT JOIN (select i.Part_ID, L.Val as alt_city from inserted i left join List_Vals L on i.alt_city = L.Val_ID where L.List_ID = 4 GROUP BY i.Part_ID,L.Val )B on A.Part_ID = B.Part_ID LEFT JOIN (select i.Part_ID, L.Val as city from inserted i left join List_Vals L on i.City = L.Val_ID where L.List_ID = 4 GROUP BY i.Part_ID,L.Val)C on A.Part_ID = C.Part_ID LEFT JOIN (Select i.Part_ID,U.Id as Retention_Specialist from inserted i left join Users U1 ON i.Retention_Specialist = U1.User_ID LEFT JOIN NtoSUserLookup U on U.User_ID = U1.User_ID )D on A.Part_ID = D.Part_ID LEFT JOIN (Select i.Part_ID,U.Id as Job_Coach from inserted i left join Users U1 ON i.job_coach_id= U1.User_ID left join NtoSUserLookup U on U.User_ID = U1.User_ID)E on A.Part_ID = E.Part_ID Order by A.Part_ID END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO