OurSQL Episode 120: Trigger Happy

This week we discuss triggers in MySQL. Ear Candy is SQL in the Shell, and At the Movies is Project Triage and Recovery, by Keith Casey of Twilio, at the 2012 CodeConnexx conference.

FOSDEM 2013 is taking place in Brussels, Belgium Sat Feb 2 - Sun Feb 3rd. Call for papers for the MySQL & Friends room closes December 21st.

Northeast LinuxFest is happening at Harvard University, Cambridge MA Saturday March 16th and Sunday March 17th, 2013. Open Database Camp will be held there. Sponsors are needed, make sure to specify you are helping out because of Open Database Camp. Registration is free, register today!

Percona Live: MySQL Conference and Expo 2013 is being held at the Santa Clara Convention Center Monday April 22nd - Thursday April 25th. The tutorial schedule is up. Register today, early bird pricing goes until Friday, December 28th.

Training
MySQL Marinate, a FREE basic MySQL training course led by Sheeri.

SkySQL Trainings

Oracle's MySQL Trainings

Percona Trainings

Triggers
Episode 56, part 1 of Events
Episode 57, part 2 of Events
Episode 118, part 1 of Stored Routines
Episode 119, part 2 of Stored Routines (also where we talked about cursors)

TRIGGER privilege

Simple example trigger:

CREATE TRIGGER staff_update_date
BEFORE INSERT ON staff
FOR EACH ROW SET NEW.last_update = NOW();

More complex example:

DELIMITER |
CREATE TRIGGER logNewStaff
BEFORE INSERT ON staff
FOR EACH ROW BEGIN
INSERT INTO staff_creation_log (username, when_created)
VALUES (NEW.username, NOW());
SET NEW.last_update = NOW();
END
|
DELIMITER ;

mysqldump --triggers for backup

Using Triggers
Trigger Syntax
Limitations on triggers

SHOW TRIGGERS
INFORMATION_SCHEMA.TRIGGERS table

Ear Candy
In this week's ear candy we talk about SQL in the Shell. There's a similar article about Implementing SQL with UNIX Utilities.
grep - like SELECT (or WHERE)
cut - like SELECT
sort - like ORDER BY or SELECT DISTINCT
awk - more complex operations

Cygwin
Awk book

At the Movies
In At the Movies this week, we present Project Triage and Recovery by Keith Casey of Twilio at last month's CodeConnexx conference in Indianapolis.

Where you can see us
MySQL Marinate starts January 1st, 2013. It is a virtual self-study course learning MySQL which both Gerry and Sheeri will be helping lead.

Gerry will be at the January Seattle MySQL meetup group. We will talk about MySQL Marinate for those who will be following it. The main topic for January will be the first 'MySQL show and tell' session where Lisa Phillips will be talking about MySQL @ Twitter. If you would like to present how your organization uses MySQL at the Seattle MySQL meetup group, please contact me using the feedback information.

Sheeri will be at the January Boston MySQL meetup group on Monday, January 14th from 7-9 pm. The topic is NuoDB, an emergent database that is 100% SQL, 100% ACID, and scales out elastically on decentralized resources in the cloud, on-premises, or both.

Sheeri will be speaking at the miniconfs at Linux Conf Australia, about "Getting Started With a Podcast", "mysql security" and "The Art of Cat Herding: How to Manage Geeks." Linux Conf Australia will be held Monday January 28th through Saturday February 2nd, 2013 in Canberra.

Sheeri will be speaking at Rocky Mountain Oracle User Group Training Days in denver, colorodo from Monday Feb 11 - Thursday February 14th on "Backing up MySQL" and "Are You Getting the Most Out of Your Indexes?"

Sheeri will be speaking at Confoo in Montreal, Canada on Monday February 25 - Friday March 1, 2013, giving talks in English about "Are You Getting the Most Out of Your Indexes?" and "Different (MySQL) Forks for Different Folks".

Gerry will present a tutorial at Percona Live:MySQL Conference and Expo on Using TokuDB: A Guided Walk Through TokuDB Implementation. Many of the concepts apply to custom MySQL / MariaDB installations as well. Percona Live: MySQL Conference and Expo 2013 will be held April 22-25, 2013 in Santa Clara, California.

Feedback
Facebook group
Google+ page
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter: @oursqlcast
or Tweet about @oursqlcast