For the sake of simplicity, triggers are the way to go for implementing any kind of tracking of database changes. However, you need to be aware of what happens under the hood when you use triggers.
According to MySQL Stored Procedure Programming, page 256 under the head "Trigger Overhead" says the following:
It is important to remember that, by necessity, triggers add overhead
to the DML statement to which they apply. the actual amount of overhead
will depend upon the nature of the trigger, but --- as all MySQL
triggers execute FOR EACH ROW --- the overhead can rapidly accumulate
for statements that process large numbers of rows. You should
therefore avoid placing any expensive SQL statements or procedural
code in triggers.
An expanded explanation of trigger overhead is given on pages 529-531. The conclulding point from that section states the following:
The lesson here is this: since the trigger code will execute once
for every row affected by a DML statement, the trigger can easily
become the most significant factor in DML performance. Code inside the
trigger body needs to be as lightweight as possible and -- in
particular -- any SQL statements in the trigger should be supported by
indexes whenever possible.
Not mentioned in the book is another factor when using triggers: When it comes to audit logging, please be aware of what you log data into. I say this because should you choose to log to a MyISAM table, each INSERT into a MyISAM table produces a full table lock during the INSERT. This can become a serious bottleneck in a high-traffic, high-transaction environment. Additionally, if the trigger is against an InnoDB table and you log changes in MyISAM from within the trigger, this will secretly disabled ACID compliance (i.e., reduce block transactions to autocommit behavior), which is cannot be rolled back.
When using triggers on InnoDB tables and logging changes
- The table you log to is also InnoDB
- You have autocommit turned off
- You setup START TRANSACTION...COMMIT/ROLLBACK blocks thoroughly
In this way, audit logs can benefit from COMMIT/ROLLBACK as would main tables.
Concerning using stored procedures, you would have to painstakingly call the stored procedure at every point of DML against the table being tracked. One could easily miss logging changes in the face of tens of thousands of lines of application code. Placing such code in a trigger eliminates finding all those DML statements.
CAVEAT
Depending on how complex the trigger is, it can still be a bottleneck. If you want to reduce bottlenecks in audit logging, there is something you can do. However, it will require a little infrastructure change.
Using commodity hardware, create two more DB Servers
This will server to reduce write I/O on the main database (MD) due to audit logging. Here is how you can accomplish it:
Step 01) Turn on binary logging in the main database.
Step 02) Using an inexpensive server, setup MySQL (same version as MD) with binary logging enabled. This will be DM. Setup replication from MD to DM.
Step 03) Using a second inexpensive server, setup MySQL (same version as MD) with binary logging disabled. Setup each audit table to use --replicate-do-table. This will be AU. Setup replication from DM to AU.
Step 04) mysqldump the table structures from MD and load it into DM and AU.
Step 05) Convert all audit tables in MD to use the BLACKHOLE storage engine
Step 06) Convert all tables in DM and AU to use the BLACKHOLE storage engine
Step 07) Convert all audit tables in AU to use the MyISAM storage engine
When done
- DM will replicate from MD and record stuff in its binary log only
- With --replicate-do-table filter on all audit tables, AU will replicate from DM
What this does is store audit info on a separate DB server and also reduce any write I/O degradation that MD would normally have.
That sort of validation is not a candidate for a column or table level Constraint. You have two options depending on how you're interacting with this database:
Validation is done via the 'front end' interface, it won't let you make 'bad' picks. You can then validate them again within a SQL query to double check if so desired.
Validation only occurs within a SQL query, allowing the 'front end' to pass along potentially 'bad' data.
If you choose to validate in the SQL query you can either build explicit checks using typical IF THEN iterative logic or build it into your INSERT queries. To do the latter your tables need to contain the data in a relationship that mimics you validation scheme, which luckily it looks like your tables do. Something like the following should do what you're looking for (@ denotes variables, I'm assuming your passing those in or setting them ahead of time):
INSERT INTO Tests (student_id, quarter_id, class_id, test_date, test_grade)
SELECT student_id
,quarter_id
,class_id
,test_date
,test_grade
FROM (SELECT @student_id AS student_id
,@quarter_id AS quarter_id
,@class_id AS class_id
,@test_date AS test_date
,@test_grade AS test_grade ) AS InsertSet
INNER JOIN quarters Q
ON InsertSet.quarter_id = Q.quarter_ID
AND InsertSet.student_ID = Q.student_ID
INNER JOIN classes_to_courseloads CTC
ON Q.courseload_ID = CTC.coarseload_ID
AND InsertSet.class_id = CTC.class_id
By turning the variable set into an inline table we can join against the other tables to insure the data is valid. We INNER JOIN to the Quarters table to insure the quarter_ID and student_ID match up, thus verifying he is enrolled for the quarter. We then INNER JOIN onto Classes_to_Courseloads to verify that the class_ID the test grade is for matches up with his courseLoad_ID the student_ID was signed up for in the Quarters table. If any of the JOINs in that query fail you'll get no results and thus won't INSERT anything into the Tests table.
As for throwing back a message or error you can run a SELECT query against Tests using the variables to try and return the test_ID, if you get a result the INSERT was a success, if you don't it failed.
Best Answer
Best practice...
A database is a place to store data. It is the 'source of truth'.
The application is the place for * interpreting the data * "Business logic" * Formatting and pretty-printing of output * Cleansing the data as it is stored into the database.
It is often wise (especially in more complex systems) to have a "database layer" in which the "business" info is transformed to/from "database" info. On the database side, there is "INT" and "FLOAT"; on the business side, there are other terms for numeric things. The business may say "store this"; the layer may turn that into one
INSERT
, or split the columns across two tables, or normalization plus inserts, orINSERT .. ON DUPLICATE KEY UPDATE ..
, orINSERT IGNORE
, or anINSERT
plus augment a summary table, or ...Yes, some of those things could be done with
TRIGGERs
andFOREIGN KEY
"cascading constraints", etc. But I would rather put all the logic in a single place. I find triggers clumsy to code. I find FKs limted in capability.