Database ease of updating

17-Apr-2015 13:21

In SQL terms, a trigger is "procedural code that is automatically executed in response to certain events on a particular table in a database" (Wikipedia).More simply, it is a stub of code that is automatically activated when a particular database event, such as an INSERT or DELETE, occurs.There are two important clauses in this trigger: Also worth noting is the NEW keyword, prefixed to the field name in the source table.This keyword indicates that the trigger should take account of the new value of the field (that is, the value that has just been inserted into the field).(Table A) Now, I'd like to set up an audit trail to track changes made to this table.This trail should reflect every change made to the table, and indicate both the user who initiated the change and the time at which the change was made.To store this information, I need to create a new table (table name: audit) as shown below.(Listing C) If you've followed along so far, this should be easy to read.

You can use the statement to safely create tables – if they already exist, the statement will be ignored. There are lots of tutorials available, such as this one, this one, or this Google search resultset.) You might notice that we closed the database when done.Triggers are new in My SQL 5.x, and have been gradually improving as new versions of the 5.x code tree have emerged.In this document, I'll provide you with a brief introduction to defining and using a trigger, viewing trigger status, and deleting a trigger once you're done with it.My SQL also supports a corresponding OLD prefix, which can be used to refer to the previous value of the field.

You can check if your trigger is active via a call to the special SHOW TRIGGERS command, as shown in Listing B. row *************************** Trigger: t1 Event: INSERT Table: data Statement: UPDATE chars SET count = count CHAR_LENGTH( Timing: AFTER Created: NULL sql_mode: 1 row in set (0.01 sec) As you can see, the INSERT into the data table activated our trigger, which counted the number of characters in the inserted records and stored that count in the chars table.

Hint: one way to do it is by using a BEFORE DELETE ON clause.