Insert Timestamp on value change from other table

Question

I have an EvidenceTimeStamp table that is triggered with data when the case table has data inserted into it.

EvidenceTimeStamp:

• CaseID
• EvidenceNum
• EvidenceType
• Evidence_Initiated_Timestamp
• Evidence_Returned_Timestamp

I created a trigger to fill the first four columns of data whenever a new case is inserted in the case table and it creates a timestamp on Evidence_Initiated_Timestamp when this happens.

However, I need to create a closing timestamp when the process is complete. I wanted to do this by my Outbound Ship Table- everytime the Attribute (bit value) Outbound_Item_Recevied is changed from false to true (or 0 to 1) I want it to initate a timestamp on the Evidence_Returned_Timestamp column in my EvidenceTimeStamp table that matches the corresponding CaseID and EvidenceNum.

Note: the EvidenceTimeStamp table does not have a primary key or foreign key technically... the CaseID is primary key in the Case table and EvidenceNum is an attribute in the Evidence Table. This is a "trigger" table so it really doesn't have its own specific data, it's just filling data from other tables.

Any idea of how to do this?

Also, other quick question, how do I get bit values to show "True" or "False" instead of 1 or 0? Thank you!!

Answer 1

Logically (based on the names of your tables), a particular item of evidence cannot be checked out to more than one person at once. We'll assume, then, that no more than one EvidenceTimeStamp table record for a given CaseID and EvidenceNum should have a NULL Evidence_Returned_Timestamp.

In that case, you'd need a trigger on your Outbound Ship Table with something like the following in it:

UPDATE ets
   SET ets.Evidence_Returned_Timestamp = GETDATE()
  FROM EvidenceTimeStamp ets
         INNER JOIN inserted I ON (    ets.CaseID = i.CaseID
                                   AND ets.EvidenceNum = i.EvidenceNum
                                  )
 WHERE i.Outbound_Item_Recevied = 1
   AND ets.Evidence_Returned_Timestamp IS NULL
;

(SQL Server syntax; modify as needed for other platforms)

This will work whether you're modifying/inserting one record or many in the Outbound Ship Table. However, this will update all rows for the given CaseID and EvidenceNum pair(s) that currently have NULL in Evidence_Returned_Timestamp, so if you can have more than one open row for a specific CaseID and EvidenceNum, you'll need some other way to find the right row(s) to update.

(To clarify: If you've got open entries for case 123, evidence numbers 2 and 7, and case 456, evidence numbers 3 and 7, and you updated an Outbound Ship item for case 123, evidence num 7, then the other three open entries are fine. However, if you had two EvidenceTimeStamp rows with case 123, evidence number 7, then both of them would have their Evidence_Returned_Timestamp set.)

NOTE: Every table should have a column (or group of columns) that is guaranteed to uniquely identify every row in the table and it (or they) really should be the primary key. You may not want to set that up as a clustered index, but you enforce uniqueness by making that the primary key.