0

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!!

6
  • Does your Outbound Ship table has CaseId and EvidenceNum column? Jun 8 '17 at 15:45
  • Yes,..... It does. But "Ship ID" is primary key, but that won't be used. Just had to throw a PK in there to keep it unique. CaseID is FK in that table
    – Alee
    Jun 8 '17 at 15:50
  • Can't you have another trigger based on CaseID and EvidenceNum? If you add schema of all 3 tables might help folks answer your question. On your 2nd question you have to use a case statement to see True and False. Jun 8 '17 at 15:54
  • Yes, I don't know how to do that. I am very new with SQL
    – Alee
    Jun 8 '17 at 17:17
  • 1
    And, as always, it helps to make sure that you tag the question with your DBMS, "sql" simply refers to the SQL language in general. If you're using MS SQL Server, then add the "sql-server" tag, along with the tag for your version.
    – RDFozz
    Jun 8 '17 at 19:47
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.