Unsure whether to use a Trigger or Procedure in SQL project

Question

This is my example relation that I am working with named 'Balance Sheet' with 4 attributes.

        Balance Sheet

| companyTicker | assets | liabilities | equity |

If someone adds a tuple to this relation where (assets != liabilities + equity) I would like either an error to be thrown saying that the data is invalid, or for the tuple to simply not be added. Should i use a procedure or something like a "before Update" trigger for this?

If you use a procedure, it won't help if someone updates the table directly instead of going through your procedure. A trigger will run for any update. — Barmar, Nov 08 '16 at 22:32
Why do you need an `assets` column if it's always `liabilities + equity`? — Barmar, Nov 08 '16 at 22:33
That's application-level validation, not database level. Why is `assets` even necessary? Are you sorting on that? Can't you compute it on-demand? — tadman, Nov 08 '16 at 22:40
True, I could probably just compute it on demand. I didnt think of that. but I will definitely need 'assets' later on because i will be creating tables that use that attribute for various calculations i.e. 'returnOnAssets' — Donaldino, Nov 08 '16 at 22:49

score 0 · Accepted Answer · edited May 23 '17 at 11:48

0

Checkout this answer SQL Differences between stored procedure and triggers by @Bohemian. Since the check is to be performed at the time of insertion or when updating, go with triggers.

edited May 23 '17 at 11:48

Community

1
1

answered Nov 08 '16 at 22:33

Kartikeya Jain

16
4

Unsure whether to use a Trigger or Procedure in SQL project

1 Answers1