Join Bill Weinman for an in-depth discussion in this video Updating a table with a trigger, part of SQL Essential Training.
Triggers are operations that are automatically performed when a specified database event occurs. A common use for triggers is to force a table to be updated whenever a row is inserted or updated in another table. How triggers are implemented and used, varies significantly from system to system. The lessons in this chapter are intended as an overview to give you a sense of how triggers work, for this lesson we're going to be using the test.db database. And I'm going to copy and paste some statements from the Chap09 Exercise Files.
We're going to start by creating a couple of tables and inserting some rows into the tables. So, I'll copy that and paste it into the SQL box here in SID, and press Go. And you see we created two tables. A widgetCustomer table and a widgetSale table. They have automatically updating id columns. And in the customer table there's a name column and a last_order_id which you see is NULL for now. And the sale table has a place for the customer_id which will link it to the customer table and quantity, and price.
And there is also an item_id for linking to an item table that we're not really using. So, we've inserted three rows with Bob, Sally and Fred. You notice the id column is automatically generated. And so, I'm going to go ahead and leave this SELECT in here for now. And, we're going to create a trigger. So, I'II copy and paste that into the SQL box here and press Go. So, the trigger has been created and here's how this works. The CREATE TRIGGER statement creates a trigger and it's name goes right after the CREATE TRIGGER.
The body of the trigger, the statements that are going to be executed in that trigger are between this BEGIN and END. Which creates a block for the statements in the trigger. And, there's this AFTER INSERT ON widgetSale. AFTER means that the trigger happens after the event, and the INSERT ON widgetSale is the event, and so this trigger will happen every time there's an insert on the widget sale table. The statement updates the last_order_id column in the customer table, which you can see is NULL here so far, and it assigns it a value NEW.id.
We just inserted a row in the widgetSale when this trigger happens. And so there's a new row, and that new row has a NEW.id, which is automatically generated, so there's no other way to get it. So, this NEW is really an alias and it's part of the trigger syntax. When a trigger happens because a row has been inserted, that row is available with this NEW alias. So, this is the id from the NEW row and it's going to be set in this last_order_id. So, this last_order_id will get populated with that NEW.id.
And the WHERE clause, says that the customer_id = customer_id in the NEW row. So now, let's go ahead and insert some rows. We'll copy and paste from our Exercise File here. So, we're inserting three new rows in the widgetSale table. And then, we're going to take a look at the widgetSale and the widgetCustomer tables. Press Go, so notice our widgetCustomer table now has 3,2 and 1 in the last_order_id. And the sale table has these three orders.
So this last_order_id has been populated by our trigger. All we did was insert these rows into the widgetSale table and the widgetCustomer table got automatically updated. So for this chapter, I'm going to leave these tables in place. We're going to go ahead and use them in the rest of the chapter. You see that a trigger can be an excellent way to enforce business rules that require a table to be updated whenever another table is updated. How triggers are implemented and used. Varies significantly from system to system. The lessons in this chapter are intended as an overview to give you a sense of how they work.
And you'll need to refer to your system documentation for details on its specific implementation.
- Understanding SQL terminology and syntax
- Creating new tables and records
- Inserting and updating data
- Writing basic SQL queries
- Sorting and filtering
- Accessing related tables with JOIN
- Working with strings
- Finding the numeric type of a value
- Using aggregate functions and transactions
- Updating a table with triggers
- Creating views
Skill Level Beginner
Q: For Mac OS X: When I try to start the Apache Web Server from the XAMPP control panel, it doesn't start, and when I open "localhost" in my web browser, I see a white screen that says "It Works!" instead of the XAMPP page.
sudo apachectl stop
Q: I'm on a Mac, and I get an error in SID that says "attempt to write a read only database." How can I fix this?
A: This usually means that the database folder does not have sufficient permissions for writing by the web user. This can happen if you create the SQL folder new, rather than copying it from the Exercise Files. Here's how to fix this:
- Open a Finder window and Navigate to /Applications/XAMPP/htdocs/SQL
- Control-click on the SQL folder and select "Get Info" from the context menu.
- Under "Sharing and Permissions" (you may need to open the disclosure triangle), in the "everyone" row, select "Read & Write."Then you can close the Info window.
- Now repeat the process for the three *.db files inside the folder.