mysql 트리거는 몇 레벨인가요?

MySQL 트리거는 행 수준입니다. SQL 표준에 따르면 트리거는 두 가지 유형으로 나눌 수 있습니다. 1. 수정된 데이터 행마다 한 번씩 활성화되는 행 수준 트리거입니다. 명령문이 100행의 데이터를 삽입하면 트리거가 100번 호출됩니다. 명령문 수준 트리거 트리거는 각 명령문에 대해 한 번씩 활성화됩니다. 100행의 데이터를 삽입하는 명령문은 트리거를 한 번만 호출합니다. MySQL은 준비된 명령문 수준 트리거가 아닌 행 수준 트리거만 지원합니다.

이 튜토리얼의 운영 환경: windows7 시스템, mysql8 버전, Dell G3 컴퓨터.

트리거 개요

MySQL 트리거는 지정된 테이블과 연결되어 테이블의 데이터가 변경(추가, 업데이트, 삭제)될 때 자동으로 실행되는 저장 프로시저입니다. 데이터 행을 수정하는 이러한 작업을 트리거 이벤트라고 합니다. 예를 들어 데이터를 삽입하는 INSERT 또는 LOAD DATA와 같은 문은 삽입 트리거를 활성화할 수 있습니다.

SQL 표준에 따르면 트리거는 행 수준 트리거와 명령문 수준 트리거로 나눌 수 있습니다.

• 행 수준 트리거는 수정된 각 데이터 행에 대해 한 번씩 활성화됩니다. 문이 100행의 데이터를 삽입하면 트리거가 100번 호출됩니다.

• 문 수준 트리거는 한 번 활성화됩니다. 각 문에 대해 100행의 데이터를 삽입하는 문은 트리거를 한 번만 호출합니다.

• MySQL은 준비된 명령문 수준 트리거가 아닌 행 수준 트리거만 지원합니다.

다양한 이벤트는 다양한 유형의 트리거를 활성화할 수 있습니다. INSERT 이벤트 트리거는 INSERT, LOAD DATA, REPLACE 문 등을 포함한 데이터 작업을 삽입하는 데 사용됩니다. UPDATE 이벤트 트리거는 UPDATE 문과 같은 업데이트 작업에 사용됩니다. DELETE 및 REPLACE 문 등, DROP TABLE 및 TRUNCATE TABLE 문은 삭제 트리거를 활성화하지 않습니다.

또한 MySQL 트리거는 트리거 이벤트 전후에 실행될 수 있으며, 이를 각각 BEFORE 트리거 및 AFTER 트리거라고 합니다. 이 두 가지 트리거 타이밍은 BEFORE INSERT 트리거 또는 AFTER UPDATE 트리거와 같은 다양한 트리거 이벤트와 결합될 수 있습니다.

MySQL 트리거의 장점은 다음과 같습니다.

• 감사 기능을 구현하기 위해 테이블의 데이터에 대한 사용자 수정 작업을 기록하고 감사합니다. -비즈니스 시간 데이터 작업

• 직원을 추가하거나 삭제할 때 부서의 인원 수를 자동으로 업데이트하는 등 특정 비즈니스 로직을 구현합니다.

• 테이블의 데이터를 실시간으로 동기식으로 복사합니다.

• 트리거는 강력하지만 몇 가지 단점도 있습니다.

트리거는 데이터베이스 구조의 복잡성을 증가시키고 트리거는 애플리케이션에 보이지 않으며 디버깅하기 어렵습니다.

• 트리거; 더 많은 데이터베이스 서버 리소스를 점유해야 하며 데이터베이스에서 제공하는 null이 아닌 고유, 검사 제약 조건 등을 사용해야 합니다.

• 트리거는 매개변수를 수신할 수 없으며 현재 트리거 개체를 기반으로만 작동할 수 있습니다.

• 특별한 시나리오에 트리거를 사용하면 어느 정도 편리함을 얻을 수 있지만 데이터베이스 성능 저하 및 유지 관리 어려움을 피하기 위해 트리거에 너무 많이 의존하지 마십시오. 다음으로 트리거의 관리 작업을 소개합니다.

MySQL에서 지원하는 세 가지 트리거

실제 사용에서 MySQL은 INSERT 트리거, UPDATE 트리거, DELETE 트리거라는 세 가지 트리거를 지원합니다.

1) INSERT 트리거

INSERT 문 실행 전후에 응답하는 트리거입니다. INSERT 트리거를 사용할 때 다음 사항에 주의해야 합니다.

INSERT 트리거 코드에서 NEW(대소문자 구분 없음)라는 가상 테이블을 참조하여 삽입된 행에 액세스할 수 있습니다.

• BEFORE INSERT 트리거에서는 NEW의 값도 업데이트할 수 있으며, 이를 통해 삽입된 값을 변경할 수 있습니다(해당 작업 권한이 있는 경우).

• AUTO_INCREMENT 열의 경우 NEW에는 INSERT가 실행되기 전에 값 0이 포함되고, INSERT가 실행된 후에 자동으로 생성된 새로운 값이 포함됩니다.

• 2) UPDATE 트리거

UPDATE 문이 실행되기 전후에 응답하는 트리거입니다. UPDATE 트리거를 사용할 때 다음 사항에 유의하세요.

UPDATE 트리거 코드 내에서 NEW(대소문자 구분 없음)라는 가상 테이블을 참조하여 업데이트된 값에 액세스할 수 있습니다.

• UPDATE 트리거 코드 내에서는 OLD(대소문자 구분 없음)라는 가상 테이블을 참조하여 UPDATE 문이 실행되기 전 값에 접근할 수 있습니다.

• BEFORE UPDATE 트리거에서는 NEW의 값도 업데이트될 수 있으며, 이를 통해 UPDATE 문에 사용되는 값을 변경할 수 있습니다(해당 작업 권한이 있는 경우).

• OLD의 모든 값은 읽기 전용이며 업데이트할 수 없습니다.

참고: 트리거가 테이블 자체의 업데이트 작업을 트리거하도록 설계된 경우 BEFORE 유형 트리거만 사용할 수 있으며 AFTER 유형 트리거는 허용되지 않습니다.

3) DELETE 트리거

DELETE 문이 실행되기 전후에 응답하는 트리거입니다.

DELETE 트리거를 사용할 때 다음 사항에 주의해야 합니다.

• DELETE 트리거 코드 내에서 OLD(대소문자 구분 없음)라는 가상 테이블을 참조하여 삭제된 행에 액세스할 수 있습니다.

• OLD의 모든 값은 읽기 전용이며 업데이트할 수 없습니다.

일반적으로 트리거를 사용하는 동안 MySQL은 다음과 같은 방식으로 오류를 처리합니다.

트랜잭션 테이블의 경우 트리거가 실패하고 결과적으로 전체 문이 실패하면 문에 의해 수행된 모든 변경 사항이 비트랜잭션 테이블에 대해 롤백됩니다. 문이 실패하더라도 이러한 롤백을 수행할 수 없습니다. 오류가 발생하기 전에 변경한 사항은 여전히 ​​유효합니다.

BEFORE 트리거가 실패하면 MySQL은 해당 행에서 작업을 수행하지 않습니다.

BEFORE 또는 AFTER 트리거 프로그램 실행 중에 오류가 발생하면 트리거 프로그램을 호출하는 전체 명령문이 실패하게 됩니다.

MySQL은 BEFORE 트리거와 행 작업이 모두 성공적으로 실행된 경우에만 AFTER 트리거를 실행합니다.

트리거 생성

MySQL은 CREATE TRIGGRT 문을 사용하여 트리거를 생성합니다.

CREATE TRIGGER trigger_name
    { BEFORE | AFTER } { INSERT | UPDATE | DELETE }
    ON table_name FOR EACH ROW
    trigger_body;

그 중 Trigger_name은 트리거 이름을 지정하는 데 사용됩니다. 트리거의 트리거 시간은 트리거 이벤트의 유형을 정의하는 데 사용됩니다. table_name은 임시 테이블이나 뷰가 될 수 없는 테이블의 이름입니다. 행 수준 트리거는 트리거에 의해 실행되는 특정 문입니다.

예를 들어 직원의 급여는 중요한 정보이므로 급여 수정 내역을 기록해야 합니다. 먼저 감사 테이블을 만듭니다.

CREATE TABLE emp_salary_audit (
    audit_id    INTEGER NOT NULL AUTO_INCREMENT
    emp_id      INTEGER NOT NULL,
    old_salary  NUMERIC(8,2) NULL,
    new_salary  NUMERIC(8,2) NULL,
    change_date TIMESTAMP NOT NULL,
    change_by   VARCHAR(50) NOT NULL,
    CONSTRAINT pk_emp_salary_audit PRIMARY KEY (audit_id)
);

그중 audit_id는 자동 증가 기본 키이고, emp_id는 직원 번호입니다. old_salary와 new_salary는 각각 수정 전과 수정 후의 월급을 저장하는 데 사용됩니다. ;change_by는 운영 사용자의 실행 수정을 기록합니다.

그런 다음 tri_audit_salary 트리거를 생성하여 직원 월급 수정 기록을 기록합니다.

DELIMITER $$
CREATE TRIGGER tri_audit_salary
  AFTER UPDATE ON employee
  FOR EACH ROW
BEGIN
  -- 当月薪改变时，记录审计数据
  IF (NEW.salary <> OLD.salary) THEN
   INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
   VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
  END IF;
END$$
DELIMITER ;

그중 DELIMITER는 SQL 문의 끝 문자를 수정하는 데 사용됩니다. 이는 저장 프로시저를 도입할 때 이미 알고 있는 AFTER 수정을 의미합니다. data 그런 다음 트리거를 실행합니다. UPDATE는 업데이트 작업에 대해서만 데이터 변경 사항이 기록됨을 의미합니다. 트리거 본문의 NEW 및 OLD는 수정된 레코드와 사전 수정된 레코드를 포함하는 MySQL 트리거의 특수 변수입니다. DELETE 트리거에 대한 새로운 변수는 없습니다. CURRENT_TIMESTAMP 및 USER()는 현재 시간과 로그인한 사용자를 반환하는 MySQL 시스템 함수입니다.

트리거를 생성한 후 트리거의 효과를 확인하기 위해 몇 가지 데이터 수정 작업을 수행합니다.

UPDATE employee
SET email = 'sunqian@shuguo.net'
WHERE emp_name = '孙乾';

UPDATE employee
SET salary = salary * 1.1
WHERE emp_name = '孙乾';

SELECT *
FROM salary_audit;
audit_id|emp_id|old_salary|new_salary|change_date        |change_by|
--------|------|----------|----------|-------------------|---------|
       1|    25|      4700|      5170|2019-10-18 10:16:36|TONY     |

첫 번째 UPDATE 문은 "Sun Qian"의 이메일 주소만 수정했으므로 tri_audit_salary는 두 번째 항목이 트리거되지 않습니다. UPDATE 문은 월급을 수정하고 tri_audit_salary를 트리거합니다. 따라서 감사 테이블 Salary_audit에는 월급 변경 전후의 상황을 기록한 데이터가 포함되어 있습니다.

직원 추가와 삭제 작업을 동시에 감사하려면 INSERT 트리거와 DELETE 트리거를 생성하면 됩니다.

또한 MySQL은 동일한 트리거 시간 및 동일한 이벤트에 대해 여러 트리거 정의 및 실행 순서 지정을 지원합니다.

CREATE TRIGGER trigger_name
    { BEFORE | AFTER } { INSERT | UPDATE | DELETE }
    ON table_name FOR EACH ROW
    { FOLLOWS | PRECEDES } other_trigger
    trigger_body;

그 중 FOLLOWS는 트리거가 other_trigger 후에 실행된다는 의미입니다. other_trigger 이전; 옵션이 지정되지 않은 경우 기본값은 트리거가 생성된 순서대로 실행되는 것입니다.

트리거 보기

SHOW TRIGGERS 문을 사용하여 데이터베이스의 트리거 목록을 봅니다.

SHOW TRIGGERS
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

그 중 db_name은 지정된 데이터베이스의 트리거를 보는 데 사용되며 기본값은 현재 데이터베이스입니다. ; LIKE는 저장소 일치에 사용됩니다. 프로세스 이름, WHERE는 더 많은 필터 조건을 지정할 수 있습니다. 예를 들어, 다음 문은 현재 데이터베이스의 트리거를 반환합니다.

mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: tri_audit_salary
               Event: UPDATE
               Table: employee
           Statement: BEGIN
  -- 当月薪改变时，记录审计数据
  IF (NEW.salary <> OLD.salary) THEN
   INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
   VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
  END IF;
END
              Timing: AFTER
             Created: 2020-10-06 21:50:02.47
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

또한 MySQL 시스템 테이블 INFORMATION_SCHEMA.TRIGGERS에는 더 자세한 트리거 정보가 포함되어 있습니다.

트리거를 생성하기 위해 DDL 문을 얻으려면 SHOW CREATE TRIGGER 문을 사용하면 됩니다. 예:

mysql> SHOW CREATE TRIGGER tri_audit_salary\G
*************************** 1. row ***************************
               Trigger: tri_audit_salary
              sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `tri_audit_salary` AFTER UPDATE ON `employee` FOR EACH ROW BEGIN
  -- 当月薪改变时，记录审计数据
  IF (NEW.salary <> OLD.salary) THEN
   INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
   VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
  END IF;
END
  character_set_client: utf8mb4
  collation_connection: utf8mb4_0900_ai_ci
    Database Collation: utf8mb4_0900_ai_ci
               Created: 2020-10-06 21:50:02.47
1 row in set (0.00 sec)

트리거 삭제

MySQL은 트리거를 수정하는 명령문을 제공하지 않습니다. DROP TRIGGER 문을 통해서만 트리거를 삭제하고 다시 생성할 수 있습니다. 예를 들어, tri_audit_salary 트리거가 존재하지 않는 경우 오류를 방지하려면

DROP TRIGGER IF EXISTS tri_audit_salary;

IF EXISTS 문을 사용하여 tri_audit_salary 트리거를 삭제할 수 있습니다.

【관련 추천: mysql 비디오 튜토리얼】

위 내용은 mysql 트리거는 몇 레벨인가요?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!