Can I create a SQL (Oracle) Trigger that increments a daily count based off another table's updates?

Question

We have a table that people has application. As users go through the applications the table is updated. Once the form is submitted there is a flag that is changed from 1 to 2. We want to run a daily count of submissions and keep track of it. So we would have a separate table with a column for the date and a column for the total submission for that day. I want that daily count to be incremented each time a row is updated from 1 to 2. I was thinking that using a trigger where its something like this:

CREATE TRIGGER daily_submission_count
AFTER UPDATE on [form_info_table]
FOR EACH ROW
WHEN (form_info_table.status == 2)
BEGIN
     UPDATE [daily_count_table].daily_count SET daily_count = daily_count + 1;

Of course with this approach, I would have to initialize the row with another rule that runs at 12:00am that sets the date and the count to 0. I am having trouble finding the right way to write the SQL or if there is a better approach to this.

Answer 1

You could do that but it is a bad idea. Think about that ONE person that updates that FORM_INFO_TABLE and steps away from their desk before ending the transaction (or in more realistic terms, the service stalls or crashes). Now you have an open lock on DAILY_COUNT_TABLE.

Every single other attempt to increment that count is now dead in the water. Your system comes to a halt. Even in the best case situation, where everything is running smoothly, you have effectively reduced your system to a single transaction at a time, because whoever is incrementing the daily count blocks everyone else, even if it is just for a moment.

The first thing I would look at is: How expensive is it to derive the daily count with NO additional database structures, ie, is it really that expensive to run:

SELECT COUNT(*)
FROM   FORM_INFO_TABLE
WHERE  STATUS = 2
AND    <some appropriate date range>

because if that is fast enough (for the amount of times you need to run it), then you're done.

If that alone is too slow, then the next option might be an index on only the records with status of 2, eg

CREATE INDEX MY_INDEX ON FORM_INFO_TABLE ( CASE WHEN STATUS = 2 THEN DATE_COLUMN END )

because then the only entries in that index are rows that contain a status of 2. An appropriate query to scan just that index is your next option - no extra code needed but a small transaction overhead for STATUS=2 rows to get that daily count efficiently.

If you really have some complex structures for which both of the above are not possible, then you could look at a queueing mechanisn, where the trigger is along the lines of

CREATE TRIGGER daily_submission_count
AFTER UPDATE on [form_info_table]
FOR EACH ROW
WHEN (form_info_table.status == 2)
BEGIN
  insert into pending_info values (:new.primary_key_col);
  --- (or the same with DBMS_AQ if you prefer)
END

and then a background task that comes along from time to time to collate those rows and summarise the data appropriately.

But it would have to be a pretty special set of circumstances to warrant that complexity.