MySQL Learning notes--creation and deletion of triggers and precautions for use

-----/* Trigger */--when the trigger condition is met. Automatic execution. The statements in the trigger can guarantee consistency between certain operations--cascading changes that can refer to columns in other tables--pre-trigger can get pre-and new field values beforehand, validate some conditions and make some preparations, trigger */--after the table is saved. Guarantee the integrity of the transaction before it takes effect after the table has been modified */--row-level triggers are executed once for each row affected by the DML statement, such as when the UPDATE statement affects multiple rows, the trigger is activated for each row. -----Create--Create a trigger with only one execution statement   basic form as follows: create trigger  trigger name  before|after  Trigger Event  -- Trigger event can be insert,update,delete on  table name  for each row  EXECUTE statement    example:  create  trigger dept_trig1 before insert on department for each row  insert into trigger_time values (now ());  --pit, create trigger_time table  drop table  if exists tigger_time;--misspelled, missing an ' R '  create table trigger_time (  exec_ time time   )  desc trigger_time --Check trigger effect  select * from  department; insert into department values (1004, ' Sales department ', ' responsible for product sales ', ' 1th Building Sales Hall ')--error, Duplicate entry desc departmeNt --d_name is a unique constraint, cannot be named ' Sales Department '  insert into department values (1004, ' Sales 1 ', ' responsible for product sales ', ' Building No. 1th Sales Hall ')  select table_schema from information_schema.tables where table_name= ' Trigger_time '  --see trigger_time table belongs to what library  select * from trigger_time --view Trigger_ If the time table is updated by a trigger, ok   --creates a trigger with more than one execution statement   basic form as follows  create trigger  trigger name  before| after  Trigger Event  --trigger event can be insert,update,delete   on  table name  for each row  ---Line Trigger    begin    EXECUTE statement 1;     execution statement on the specified table 2;     .........;    end /*mysql default to '; ' The most complete execution of the statement end flag, here with delimiter && to translate to &&,&& to indicate the end of the whole sentence, in the IDE should be able to use the */  example:  delimiter &&  create trigger dept_trigzz after delete on  Department for each row begin insert into trigGer_time values (' 21:01:01 ');  insert into trigger_time values (' 22:01:01 ');  end  && delimiter; show triggers  --Check Trigger effects  delete from  department where d_id=1004; select * from trigger_time--Success insert,ok /* A table with the same trigger event at the same trigger time, can only create one trigger *//*, such as the Department table, trigger event INSERT, trigger event for after trigger can only have one, but can be defined trigger event is before the trigger */--- View Trigger---1.show triggersshow triggers --show triggers cannot query the specified trigger. Use only for fewer triggers---2. View the trigger information from the triggers table select * from information_schema.triggers where  Tigger_name= ' Trigger name '---trigger use--create Before insert and After insert two triggers, compare execution order--Create a before  Insert Trigger Create trigger before_insert before inserton department for each  rowinsert into trigger_test values (null, ' Before_insert ');--Creating a after insert trigger Create  trigger after_insert after inserton department&nbSp;for each rowinsert into trigger_test values (null, ' After_insert ');--fill pit, create trigger_ Test Table Create table trigger_test (id int auto_increment primary key,info  varchar (10));--Validation trigger insert into department values (1004, ' Sales 1 ', ' responsible for product sales ', ' 1th Building Sales Hall ');d elete  from department where id =  ' 1004 ' desc departmentdelete from department  where d_id =  ' 1004 ' insert into department values (1004, ' Sales 1 ', ' responsible for product sales ', ' Building No. 1th Sales Hall ');--info field error, length is not enough--modify the field length of the column in the table, Alter table trigger_test change column info  info varchar;  --change can change the table field name and data type at the same time, but only if the field length is modified to repeat the fields name Alter table trigger_test  modify column info varchar (+);--modify can only change the field data type--delete the inserted column that generated the error delete from  department where d_id =  ' 1004 '--reinsert insert into department values (1004, ' Sales 1 ', ' responsible for product sales ', ' 1th building Sales hall ';--Validate trigger executionResults select * from trigger_test --The first record is the ' Before_insert ' inserted after the Before_insert trigger is activated, and the second record is After_ Insert triggers are inserted after the ' After_insert ', ok/*fbi warning*//* triggers cannot contain things like: Start transaction,commit,rollback , and cannot contain a call statement *//* any step error during the execution of the trigger will prevent the program from continuing down, but for the normal table that generated the trigger event, the record that has been insert/delete/update cannot be rolled back, and the updated data will remain in the table */- --Delete trigger basic form:drop trigger  trigger name drop trigger  DB instance name. Trigger Name  --trigger is a global example of a DB instance:drop  trigger dept_trig1--whether the query still exists select * from information_schema.triggers where  Trigger_name= ' Dept_trig '  --query no results, ok/*fbi warning*//* when a trigger is no longer needed, be sure to delete the trigger, otherwise it will cause unexpected changes in data */

MySQL Learning notes--creation and deletion of triggers and precautions for use