Best Syntax Within Trigger To Change Value

[skuhlman]

What is the best syntax to use within a trigger for this problem?

Table: MYCURRENTTABLE (one record per MYID)
Fields: MYCT_KEY, MYID, MYCODE, .....

Table: MYHISTORICALTABLE (multiple records per MYID)
Fields: MYHT_KEY, MYID, MYCODE, .....

When a record gets inserted into the MYHISTORICALTABLE, the MYCODE field needs to be populated with the value currently sitting in the MYCODE field of the MYCURRENTTABLE. I've already got a trigger on the MYHISTORICALTABLE which is performing other tasks. I just need to add logic to the trigger to handle this new need.

The code needs to work both a single record insert and a multiple record insert. MYID will always already exist within the MYCURRENTTABLE. MYHT_KEY is an identity column. MYCODE may or may not be included in the insert statement. If MYCODE IS included, it should keep that value. If MYCODE is NULL or is NOT included in the insert statement, the value should be retrieved from the MYCURRENTTABLE.

Thanks

 

[SQLSister]

Definetly you want to use an update statement that joins to the inserted psuedotable so that you can update the changed records as a group.

YOu can also use the updated clause in an if statment to determin if the column was changed and base your process onthat. Or you can check the values between the delted and inserted psuedotables to find the records where the column was not updated, then just add any additional where clauses you need to get the right data.


Questions about posting. See faq183-874

 

[skuhlman]

so, assuming that I've already determined that it's an insert, would this be the proper syntax to use:

UPDATE MYHISTORICALTABLE
  SET MYCODE = MYCURRENTTABLE.MYCODE
  FROM MYHISTORICALTABLE JOIN inserted
        ON MYHISTORICALTABLE.MYHT_KEY = inserted.MYHT_KEY
      JOIN MYCURRENTTABLE
        ON MYCURRENTTABLE.MYID = MYHISTORICALTABLE.MYID
  WHERE ISNULL(inserted.MYCODE,'')=''

 

Thanks, great post. I just encounted identical issue and this resolution worked like a charm!

 

[ESquared]

That last message looks like it was posted by a ghost! No name is appearing.

-------------------------------------
A sacrifice is harder when no one knows you've made it.