trigger - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

trigger

Author

Topic

abyie
Starting Member

42 Posts

Posted - 2006-12-21 : 14:31:59

Hi,
I am trying to create an update trigger on the field named LSEComplete on table Part located on database X.

As soon the field is updated,I need to import some fields from more than one table on database X to a table called Contactloadtest on Database Y.
I would really appreciate your help.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-21 : 15:07:13

Please post what you have so far so that we can help you fix your code. You need to at least show that you tried.

Tara Kizer

abyie
Starting Member

42 Posts

Posted - 2006-12-21 : 15:25:33

********Create Trigger statement***********
CREATE TRIGGER ContacatLoadTableInsert
ON Part
FOR UPDATE
AS
******The trigger fires only when this field is updated *********
IF UPDATE(LSE_Complete_Date)

BEGIN

****Insertion statement containing all the fields that have to be imported to another table in different database*******

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 15:43:23

Duplicate post!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76707

Peter Larsson
Helsingborg, Sweden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-21 : 16:04:02

So are you getting an error with your trigger or it's not working as desired? We really need you to provide more details plus simplify your trigger code for us down to the essentials.

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources