No I dont get any errors when I update on the table.
Here is the syntax of the trigger.
CREATE TRIGGER ContacatLoadTableInsert
ON Main5.dbo.Part
FOR UPDATE
AS
IF UPDATE(Main5.dbo.Part.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
