MySQL Performance – Using Triggers or Transactions

First, this is exactly the question I had in mind when I asked Modelling constraints on subset aggregates? which is certainly the place to start. That question is more general though than this and so my answer here will have a bit more information regarding practical approaches.

You probably do not want to do this declaratively in PostgreSQL. The only possible declarative solutions either break 1NF or are extremely complicated and so this means doing it imperatively.

In LedgerSMB we expect to do this enforcement in two stages (both of which are strict).

1. All journal entries will come in via stored procedures. These stored procedures will accept a list of line items as an array and check that the sum is equal to 0. Our model in the db is that we have a single amount column with negative numbers being debits and positive numbers being credits (if I was starting over, I would have positive numbers as debits and negative numbers as credits because this is just a little more natural but the reasons here are obscure). Debits and credits are merged on storage and separated on retrieval by the presentation layer. This makes running totals a lot easier.

2. We will use a deferred constraint trigger which will check on commit based on the system fields on the table. This means that the lines entered in a given transaction must balance, but we can do this beyond the lines themselves.

This is an UNPIVOT problem.To get the sum of credit items and the sum of debit items you can use a SUM of a CASE statement. To show that, we need a table first:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE invoice_items
    (`invoice_item_id` int, `invoice_set_id` varchar(3), `credit_debit` varchar(6), `amount` int)
;

INSERT INTO invoice_items
    (`invoice_item_id`, `invoice_set_id`, `credit_debit`, `amount`)
VALUES
    (62, 'a22', 'debit', 15.00),
    (63, 'a22', 'debit', 8.00),
    (64, 'a22', 'credit', 23.00),
    (65, 'b23', 'debit', 44.00),
    (66, 'c55', 'debit', 15.00),
    (67, 'c55', 'debit', 2.00),
    (67, 'c55', 'credit', 8.00)
;

Now you can combine SUM and CASE like this: Query 1:

SELECT invoice_set_id,
       SUM(CASE WHEN credit_debit = 'debit' THEN amount END) AS debit_amount,
       SUM(CASE WHEN credit_debit = 'credit' THEN amount END) AS credit_amount
  FROM invoice_items
 GROUP BY invoice_set_id;

Results:

| INVOICE_SET_ID | DEBIT_AMOUNT | CREDIT_AMOUNT |
|----------------|--------------|---------------|
|            a22 |           23 |            23 |
|            b23 |           44 |        (null) |
|            c55 |           17 |             8 |

From here it is fairly simple to filter out the unwanted rows. We just need to wrap the two sums in COALESCE statements to turn NULL into 0:

Query 2:

SELECT invoice_set_id
  FROM(
    SELECT invoice_set_id,
           SUM(CASE WHEN credit_debit = 'debit' THEN amount END) AS debit_amount,
           SUM(CASE WHEN credit_debit = 'credit' THEN amount END) AS credit_amount
      FROM invoice_items
     GROUP BY invoice_set_id
  )X
WHERE COALESCE(debit_amount,0) > COALESCE(credit_amount,0);

Results:

| INVOICE_SET_ID |
|----------------|
|            b23 |
|            c55 |

If your actual problem is as simple as your example and you do not need the intermediate values for anything else, you can get the result also by just changing the sign of the amount based on the credit_debit value before calculating the sum. That would look like this:

Query 3:

SELECT invoice_set_id
  FROM invoice_items
 GROUP BY invoice_set_id
HAVING SUM(CASE WHEN credit_debit = 'debit' THEN amount ELSE -amount END)>0;

Results:

| INVOICE_SET_ID |
|----------------|
|            b23 |
|            c55 |