How to sincronize two table (Stor procedure,Triggers)

[sereleg]

I really need some ideas to acomplish this task..
I need to Insert the data obtained by this QUERYA to a table called EmploymentExp which have the same fields as the query with an ADDITIONAL ID field which is incremented by one everytime a new record is inserted.

QUERYA:
SELECT Candidates.InternalID,Employment.CandidateID, Employment.IDEmpl,empSector.Sector,empSector.SubSector,Employment.experience
FROM Employment INNER JOIN empSector
ON Employment.SectorID = empSector.ID
INNER JOIN candidates
ON candidates.CandidateID = Employment.CandidateID
where Candidates.InternalID is not NULL or Candidates.InternalID<>''



After inserting that data to EmploymentExp I need to use
EmploymentExp.INTERNALID=Candidates.INTERNALID
to update data on "EmploymentExp" table in case any update happens on "candidates" table. Also if an new InternalID that doesn't exist on "EmploymentExp" is created on candidates table this should be detected and inserted to EmploymentExp with its related data.


I will appreciate any help
Thanks
Raul

 

[mrdenny]

You'll want to use a trigger to handle this that fires on insert and update or two triggers, one for insert and one for update.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[sereleg]

Dear Danny Thanks for your response I would like you help me to fire a trigger for insert base on the conditions I described below. For the update case I need to have clear other situations that I have not taking into account yet.

QUERY A:
SELECT Employment.IDEmpl,Employment.CandidateID,empSector.Sector,
empSector.SubSector,Employment.experience,
Candidates.InternalID as PCMAILID FROM Employment INNER JOIN empSector ON Employment.SectorID = empSector.ID
INNER JOIN candidates ON candidates.CandidateID = Employment.CandidateID WHERE Candidates.InternalID is not NULL or Candidates.InternalID<>''

The result of this Query should be inserted in

"EmploymentExp" but the program should detect if PCMAILID exist on "EmploymentExp" if PCMAILID doesn't exist the system should insert INFORMATION FROM QUERYA.

 

[Catadmin]

You can't use a trigger from a query. Triggers only work from existing tables.

What you want to do is add some code to your existing code. At the front of it, use a "Insert Into EmploymentExp (Col1, Col2, Col3, ...) " as the first part, then your query, then add a where statement like "Where PCMAILID not Exists in (Select PCMAILID from EmploymentExp)" one the end of the query.

You'll have to play with it to get the syntax exactly correct, but that's all you should need to do.



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[sereleg]

Thank you for your response
The query I provided is just a sample to visualize the fields I need to automate. The reality is that fields should be inserted in "EmploymentExp" automaticly every time a new record is inserted on "Employment" table.
Employment table is related to Candidates table by the field CandidateID and Candidates table has a field named PCMAILID that relates to "EmploymentExp" table.
1.-Data should be inserted in "EmploymentExp" only if a new pCMAILID is inserted on candidates table. Another condition is that "Employment" should have at least one record related with this PCMAILID to be inserted into "EmploymentExp" the process should be automatic..

Sorry If I have difficulties to explain..

sereleg