"Go" MySQL Getting Started learning Xi.: basic operation of triggers

Reprint Address: http://www.2cto.com/database/201212/176781.html

A trigger is a MySQL statement (or a set of statements between the begin and end statements) that is automatically executed by MySQL in response to any of the following statements: www.2cto.com

            delete;            insert;      &NB Sp     update;            The use of triggers requires MYSQL5 or later version support. First, trigger basic operations 1, create triggers         CREATE triggers, you need to give 4 information:        the only trigger name; Although MySQL5 allows the names of triggers on different tables to be the same, it is generally better not to do so. )  www.2cto.com          triggers associated table;        Trigger response events;      & nbsp When triggers execute;        syntax structure:        CREATE trigger Trigger_name (before| After) (Delete|update|insert) on table_name        for each row        begin        SQL statement to trigger;        end;        Example:[sql] mysql> Delimi ter |  mysql> CREATE trigger T_trig before insert on t_goods for each row      begin    & nbsp     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 trigger         DROP TRIGGER [ Schema_name.] Trigger_name          example:        mysql> DROP trigger t_trig;3, view trigger         SHOW TRIGGERS [from DBNAME [like ']];        Example: [SQL]   www.2cto.com & Nbsp;mysql> show triggers;  +---------+--------+---------+------------------------------------------------------------------------------------------ | Trigger | Event  | Table   | Statement  +---------+--------+---------+----------------------------------------------------------------- ------------------------- | T_trig  | INSERT | T_goods | Begin      Set new.add_date = Current_date ();  end | Before | NULL    |          | [Email protected] | Latin1               | Latin1_swedish_ci    | Latin1_swedish_ci  |  +---------+--------+---------+--------------------------------------------------------------------------- --------------- mysql> show triggers from test;  +---------+--------+---------+--------------------------------------------------------------------------- --------------- | Trigger | Event  | Table   | Statement  +---------+--------+---------+------------------------------------------------------------------------------------------ | T_trig  | INSERT | T_goods | Begin      Set new.add_date = Current_date ();  end | Before | NULL    |          | [Email protected] | Latin1               | Latin1_swedish_ci    | Latin1_swedish_ci  |  +---------+--------+---------+--------------------------------------------------------------------------- --------------- mysql> show triggers from test like ' t% ';  +---------+--------+---------+--------------------------------------------------------------------------- --------------- | Trigger | Event  | Table   | Statement  +---------+--------+---------+----------------------------------------------------------------- ------------------------- | T_trig  | INSERT | T_goods | Begin      Set new.add_date = Current_date ();  end | BefoRE | NULL    |          | [Email protected] | Latin1               | Latin1_swedish_ci    | Latin1_swedish_ci  |  +---------+--------+---------+--------------------------------------------------------------------------- ---------------   Second, note 1, only the table supports 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 multiple tables; 4, for INSERT, The new row is represented by new, and the value of each column in the row is represented by the new column name;        for delete, the deleted row is represented by old;

        for update, the pre-update lines are represented by old, and the updated lines are represented with new