How do I stamp nothing?

Question

I get an LDAP feed in for my employee database - basically a big csv file. This updates once a night and I revise all of our internal apps based on this spreadsheet.

When a new person comes on the feed I have a trigger in my table that fires and spits out a "created" field so that I can run queries based on when the employee row entered our system.

My question is, is there a way to trigger when nothing is queried? I would like to date (time) stamp a user row based on that row no longer being on the feed. But also I don't want that overwritten every night since I would like my "left_date" field to be accurate.

Answer 1

Short answer would probably be no to the actual question. That being said it should be a fairly simple thing to fix.

1) Have your process bulk insert the CSV file to the same table every night after a TRUNCATE.

2) Do a LEFT JOIN to find with a null against your employee table to find the new ones and insert them.

3) Do a right join with null against your employee table to find the "users no longer on the feed" and update their "left_date" field.

If you need to run these updates manually (sounds like it from your question) you could still have the SQL just create the statements and so you could review and run them manually.