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!!
Best Answer
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 givenCaseID
andEvidenceNum
should have a NULLEvidence_Returned_Timestamp
.In that case, you'd need a trigger on your Outbound Ship Table with something like the following in it:
(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
andEvidenceNum
pair(s) that currently have NULL inEvidence_Returned_Timestamp
, so if you can have more than one open row for a specificCaseID
andEvidenceNum
, 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 theirEvidence_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.