1. Triggers:

In the bank deposit and withdrawal business, each time a user accesses a loan, the system needs to modify the balance of the corresponding account, and it also needs to automatically record this operation (generate transaction details), while the above operation is performed on two tables simultaneously (update the bank information table, finish the transaction information table)Into an insert operation).A trigger is a named database object associated with a table that is activated when a specific event occurs on the table.

Triggers are special stored procedures that automatically trigger execution when data in a table changes.That is, triggers are only for DML(insert, update, delete).

2. Grammar:

CREATE TRIGGER <trigger_name>   BEFORE|AFTER

  INSERT|UPDATE|DELETE  ON <table_name> FOR EACH ROW

  BEGIN

SQL statement;

 END

BEFORE means to execute the trigger before the trigger event occurs.

AFTER indicates that a trigger is executed after the trigger event occurs.

FOR EACH ROW indicates that each record affected by a data update (INSERT, UPDATE, or DELETE) operation executes a trigger.

mySql only supports row-level triggers, not statement-level triggers (such as CREATE TABLE statements).

OLD, NEW keywords

When inserting a new record into a table, NEW keywords can be used to represent the new record in the trigger program, and "NEW.Field Name" can be used to access a field value of the new record when it is needed.

When an old record is deleted from a table, OLD keywords can be used to represent the old record in the trigger program, and OLD.Field Name can be used when a field value of the old record needs to be accessed.

3. Cases:

-- ----------------------------
-- Table structure for `t_card`
-- ----------------------------
DROP TABLE IF EXISTS `t_card`;
CREATE TABLE `t_card` (
  `cardId` varchar(255) NOT NULL,
  `password` char(6) NOT NULL,
  `balance` int(11) NOT NULL,
  `open_date` date NOT NULL,
  PRIMARY KEY (`cardId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_card
-- ----------------------------
INSERT INTO t_card VALUES ('6221 1001', '111222', '900', '2019-08-08');
INSERT INTO t_card VALUES ('6221 1002', '111111', '500', '2019-08-08');
INSERT INTO t_card VALUES ('6221 2221', '565555', '200', '2019-08-08');

DROP TABLE IF EXISTS t_tran;
CREATE TABLE IF NOT EXISTS t_tran(
    id int auto_increment PRIMARY key,
    cardid varchar(19) not null,
    type varchar(10) not null,
    money int not null,
    t_time DATE not null,
    FOREIGN KEY(cardId) REFERENCES t_card(cardId)
);

DROP TRIGGER IF EXISTS t_update;
CREATE TRIGGER t_update AFTER UPDATE ON t_card
FOR EACH ROW
BEGIN
   DECLARE vtype VARCHAR(10);
   DECLARE m1 int;
   DECLARE m2 int;
    SELECT OLD.balance INTO m1 from t_card where cardid=OLD.cardid;
    SELECT NEW.balance INTO m2 from t_card where cardid=OLD.cardid;
    IF m2>m1 THEN SET vtype='deposit'; 
    ELSE SET vtype='Withdraw money';
    END IF;
   INSERT INTO t_tran(cardid,type,money,t_time)VALUES(OLD.cardId,vtype,ABS(m2-m1),NOW());
END

Test code:

UPDATE t_card set balance=balance-200 WHERE cardid='6221 1002';
SELECT * from t_card;
SELECT * from t_tran;