Checking via pre-hook if a model should run (again)

Hi all,

I want to do the following: I have a model whitch creates a table “customers”. A pre- and a post-hook is defined in the model. Both hooks should update a column in a logging table.

The logging table has a simple, four-row structure:
tablename (varchar), run-id (number), start (timestamp), end (timestamp).

It is already filled with the following two datasets:
‘customers’,1000,null,null
‘customers’,2000,null,null

The pre-hook from my model should now fill the start-timestamp. The post-hook the end-timestamp after the completion of the model.

So far so easy. Now comes the tricky part.

I start the model the first time with run-id 1000. The model runs without errors and ends successfully. In the logging table, the start and end for the run-id 1000 are now <> null.
I start the model a second time, again with run-id 1000. The model should NOT RUN again, because the run with run-id 1000 is already completed. To recognize that, both timestamps are not null.

Now I start the model a third time, with run-id 2000. It runs into an error and the model is aborted. In this case, only the start-timestamp is filled, the end-timestamp is still null, because the post-hook wasn’t executed.
If I re-run this model with run-id 2000, it should run normally. Because the end-timestamp is null and the model never ran successfully for run-id 2000.

In the summary: I want to control if a model is executed by checking a logging table. If there is already a end-timestamp <> null for the combination tablename + run-id, the model should do nothing.

Is there a way to do that? Per model, per macro or something else?

Many thanks in advance.