Learn SQL Database Programming

Creating and using a trigger with one statement

Let's create a copy of a table and an audit table:

1. We'll start by creating a copy of the allstarfull table:

USE lahmansbaseballdb;CREATE TABLE allstarfull_copySELECT * FROM allstarfull;

2. Then, we'll create an audit table for the allstarfull_copy table with the following query:

USE lahmansbaseballdb;  CREATE TABLE allstarfull_audit ( id INT AUTO_INCREMENT PRIMARY KEY, playerID varchar(9) NOT NULL, yearID smallint(6) NOT NULL, gameNum smallint(6) NOT NULL, gameID varchar(12) NULL, teamID varchar(3) NULL, lgID varchar(2) NULL, GP smallint(6) NULL, startingPos smallint(6) NULL, changedate DATETIME NOT NULL, actiontype VARCHAR(50) NOT NULL);

3. You can create a BEFORE UPDTE trigger on the allstarfull_copy ...