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.