1. SELECT * FROM `tsys_user`
    4. SELECT * FROM ums_customer
    7. # 查看字段
    8. desc ums_customer
    11. # 触发器
    13. SHOW TRIGGERS
    17. CREATE trigger trigger_name BEFORE|AFTER trigger_EVENT     
    18. ON TABLE_NAME FOR EACH ROW trigger_STMT 
    22. #参数trigger_name表示要创建的触发器名；
    24. #参数BEFORE和AFTER指定了触发器执行的时间，前者在触发器事件之前执行触发器语句，后者在触发器事件之后执行触发器语句；
    26. #参数trigger_EVENT表示触发事件，即触发器执行条件（触发事件），包含DELETE、INSERT和UPDATE语句；
    28. #参数TABLE_NAME表示触发事件的操作表名；
    30. #参数FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器；
    32. #参数trigger_STMT（sql语句）表示激活触发器后被执行的语句。执行语句中如果要引用更新记录中的字段，对于INSERT语句，只有NEW是合法的，表示当前已插入的记录；对于DELETE语句，只有OLD才合法，表示当前删除的记录；而UPDATE语句可以和NEW（更新后）以及OLD（更新前）同时使用。
    37. # 在创建触发器主体时，还可以使用OLD和NEW 来获取 SQL 执行INSERT，UPDATE和DELETE 操作前后的写入数据。
    41. # DELIMITER //：MySQL 默认分隔符是; 但在触发器中，我们使用 // 表示触发器的开始与结束   // 可以替换为 $ 。
    45. desc ums_customer;
    49. # 触发器 1
    51. DROP TRIGGER
    52. IF
    53.     EXISTS trigger_user_customer_insert;
    55. DELIMITER //
    56. CREATE TRIGGER trigger_user_customer_insert AFTER INSERT ON tsys_user FOR EACH ROW
    58. INSERT INTO ums_customer ( 
    59. #id,
    60. user_name, 
    61. `name`, sex, job, matrimony, birthday, forbidden 
    62. )
    63. VALUES
    64.     (
    65.         #new.user_id,
    66.         new.real_name,
    67.         new.real_name,
    68.         new.sex,
    69.         '企事业单位员工',
    70.         new.marriage_state,
    71.         new.birthday,
    72.         0 
    73.     )// 
    74.     DELIMITER;
    78. # 带定义字段  insert 触发器
    79. DROP TRIGGER
    80. IF
    81.     EXISTS trigger_user_customer_insert;
    83. DELIMITER //
    84. CREATE TRIGGER trigger_user_customer_insert AFTER INSERT ON tsys_user FOR EACH ROW
    85. BEGIN
    86.     DECLARE
    87.         user_id VARCHAR ( 50 );
    88.     DECLARE
    89.         user_name VARCHAR ( 50 );
    90.     DECLARE
    91.         `name` VARCHAR ( 50 );
    92.     DECLARE
    93.         sex INT;
    94.     DECLARE
    95.         matrimony INT;
    96.     DECLARE
    97.         birthday datetime;
    99.     SET user_id = new.user_id;
    101.     SET user_name = new.real_name;
    103.     SET `name` = new.real_name;
    105.     SET sex = new.sex;
    107.     SET matrimony = new.marriage_state;
    109.     SET birthday = new.birthday;
    111. INSERT INTO ums_customer ( user_name, `name`, sex, job, matrimony, birthday, forbidden, user_id )
    112. VALUES
    113.     ( user_name, `name`, sex, '企事业单位员工', matrimony, birthday, 0 , user_id );
    114.     END // 
    115.     DELIMITER;
    119. # 更新 (正式)
    120. DROP TRIGGER
    121. IF
    122.     EXISTS trigger_user_customer_update;
    124. DELIMITER //
    125. CREATE TRIGGER trigger_user_customer_update AFTER UPDATE ON tsys_user FOR EACH ROW
    126. BEGIN
    127.     DECLARE
    128.         user_name VARCHAR ( 50 );
    129.     DECLARE
    130.         `name` VARCHAR ( 50 );
    131.     DECLARE
    132.         sex INT;
    133.     DECLARE
    134.         matrimony INT;
    135.     DECLARE
    136.         birthday datetime;
    138.     SET user_name = new.real_name;
    140.     SET `name` = new.real_name;
    142.     SET sex = new.sex;
    144.     SET matrimony = new.marriage_state;
    146.     SET birthday = new.birthday;
    147.     UPDATE ums_customer 
    148.     SET user_name = user_name,
    149.     `name` = user_name,
    150.     sex = sex,
    151.     matrimony = matrimony,
    152.     birthday = birthday 
    153.     WHERE
    154.         user_id = new.user_id;
    156. END // 
    157. DELIMITER;
    163. # 更新
    164. DROP TRIGGER IF EXISTS trigger_user_customer_update;
    165. DELIMITER //
    166. CREATE TRIGGER trigger_user_customer_update AFTER UPDATE ON tsys_user FOR EACH ROW
    167. UPDATE ums_customer 
    168. SET user_name = NEW.real_name,
    169. `name` = NEW.real_name,
    170. sex = NEW.sex,
    171. matrimony = NEW.marriage_state,
    172. birthday = NEW.birthday 
    173. WHERE
    174.     id = new.user_id // 
    175.     DELIMITER;
    179. # 删除 （正式使用）
    180. DROP TRIGGER IF EXISTS trigger_user_customer_delete;
    181. DELIMITER //
    182. CREATE TRIGGER trigger_user_customer_delete AFTER DELETE ON tsys_user FOR EACH ROW
    183. DELETE FROM ums_customer 
    184. WHERE
    185.     user_id = old.user_id // 
    186.     DELIMITER;
    190. # 删除
    191. DROP TRIGGER IF EXISTS trigger_user_customer_delete;
    192. DELIMITER //
    193. CREATE TRIGGER trigger_user_customer_delete AFTER DELETE ON tsys_user FOR EACH ROW
    194. UPDATE ums_customer 
    195. SET forbidden = 1 
    196. WHERE
    197.     id = old.user_id // 
    198.     DELIMITER;

    image.png

    image.png

    参考：https://www.jb51.net/article/186756.htm