MYSQL getting started 11: Basic trigger operations

MYSQL getting started 11: links to basic triggers: MYSQL Getting Started: basic operations http://www.bkjia.com/database/201212/173868.htmlMYSQL Entry 2: Search Using Regular Expressions http://www.bkjia.com/database/201212/173869.htmlMYSQL Entry 3: Full Text Search http://www.bkjia.com/database/201212/173873.htmlMYSQL Entry 4: MYSQL Data Types http://www.bkjia.com/database/201212/175536.htmlMYSQL Entry 5: MYSQL Character Set http://www.bkjia.com/database/201212/175541.htmlMYSQL Getting started 6: MYSQL Operators http://www.bkjia.com/database/201212/175862.htmlMYSQL Entry 7: MYSQL common functions http://www.bkjia.com/database/201212/175864.htmlMYSQL Step 8: basic database and table operations http://www.bkjia.com/database/201212/175867.htmlMYSQL 9: simple indexing operations http://www.bkjia.com/database/201212/176772.htmlMYSQL 10: basic view operations http://www.bkjia.com/database/201212/176775.html A trigger is a MySQL statement automatically executed by MySQL in response to any of the following statements (or a group of statements between the in and END statements): www.2cto.com DELETE; INSERT; UPDATE; Use the trigger, mySQL 5 or later versions are required. I. Trigger basic operation 1. Four pieces of information are required when a trigger is created: Unique trigger name (although MySQL5 allows the same trigger name for different tables, but it is generally better not to do this .) Table associated with the www.2cto.com trigger; event returned by the trigger; when the trigger is executed; syntax structure: create trigger trigger_name (BEFORE | AFTER) (delete | update | insert) on table_name for each row BEGIN the SQL statement to be triggered; END; example: [SQL] mysql> delimiter | mysql> create trigger t_trig before insert on t_goods for each row-> begin-> set NEW. add_date = current_date ();-> end;-> | mysql> delimiter; mysql> insert into t_goods (id, goods_name, quantity)-> values (1, 'apple ', 50);-> | mysql> select * from t_goods; -> | + ------ + ------------ + ---------- + ------------ + | id | goods_name | quantity | add_date | + ------ + ------------ + ---------- + | 1 | apple | 50 | 2012-12-12 | + ------ + ------------ + ---------- + ------------ + 2. Delete the trigger drop trigger [schema_name.] trigger_name; example: mysql> drop trigger t_trig; 3. view the trigger show triggers [from dbname [like '']; example: [SQL] www.2cto.com mysql> show triggers; + --------- + -------- + --------- + triggers | Trigger | Event | Table | Statement + --------- + -------- + --------- + Statement | t_trig | INSERT | t_goods | begin set NEW. add_date = current_date (); end | BEFORE | NULL | root @ localhost | latin1 | latin1_swedish_ci | latin1_swedish_ci | + --------- + -------- + --------- + prepare mysql> show triggers from test; + --------- + -------- + --------- + triggers | Trigger | Event | Table | Statement + --------- + -------- + --------- + Statement | t_trig | INSERT | t_goods | begin set NEW. add_date = current_date (); end | BEFORE | NULL | root @ localhost | latin1 | latin1_swedish_ci | latin1_swedish_ci | + --------- + -------- + --------- + export mysql> show triggers from test like 't% '; + --------- + -------- + --------- + triggers | Trigger | Event | Table | Statement + --------- + -------- + --------- + Statement | t_trig | INSERT | t_goods | begin set NEW. add_date = current_date (); end | BEFORE | NULL | root @ localhost | latin1 | latin1_swedish_ci | latin1_swedish_ci | + --------- + -------- + --------- + Appendix 2. Note 1: Only tables support triggers, views and temporary tables are not supported; 2. Each table supports up to 6 triggers; 3. A single trigger cannot be associated with multiple events or tables; 4. For INSERT, the NEW row is represented by NEW. The value of each column in the row is represented by NEW. for DELETE, the deleted rows are represented by OLD. For UPDATE, the row before UPDATE is represented by OLD, and the updated row is represented by NEW.