SQL TRIGGER

GIỚI THIỆU

Trigger là một đoạn mã được thực thi tự động để đáp ứng với một sự kiện cụ thể xảy ra trên một bảng trong cơ sở dữ liệu.

Một trigger luôn được liên kết với một bảng cụ thể. Nếu bảng bị xóa , tất cả các trigger được liên kết cũng tự động bị xóa.

Một trình kích hoạt được gọi trước hoặc sau sự kiện sau:

• INSERT – khi một hàng mới được chèn
• UPDATE – khi một hàng hiện có được cập nhật
• DELETE – khi một hàng bị xóa.

Khi bạn chạy một lệnh  INSERT, UPDATE hoặc DELETE, hệ quản trị cơ sở dữ liệu quan hệ (RDBMS) bắn cò tương ứng.

Trong một số RDMBS, kích hoạt cũng được viện dẫn trong kết quả của thực hiện một lệnh mà các INSERT, UPDATE hoặc DELETE. 
Ví dụ: MySQL có LOAD DATA INFILE , đọc các hàng từ tệp văn bản và chèn vào bảng với tốc độ rất cao, gọi BEFORE INSERT và AFTER INSERT kích hoạt.

Mặt khác, một câu lệnh có thể xóa các hàng trong bảng nhưng không gọi các trình kích hoạt liên quan. 
Ví dụ, câu lệnh TRUNCATE TABLE loại bỏ tất cả các hàng trong bảng nhưng không gọi các trình kích hoạt BEFORE DELETE và AFTER DELETE.

CÚ PHÁP TRIGGER

Để tạo trigger, bạn sử dụng câu lệnh sau:

CREATE TRIGGER trigger_name [BEFORE|AFTER] event
ON table_name trigger_type
BEGIN
  -- trigger_logic
END;

Hãy xem xét cú pháp chi tiết hơn:

• Đầu tiên, chỉ định tên của trình kích hoạt sau mệnh đề CREATE TRIGGER .
• Tiếp theo, sử dụng một trong hai BEFORE hoặc AFTER từ khóa để xác định khi nào nên kích hoạt nên xảy ra để đáp ứng với một sự kiện cụ thể ví dụ INSERT, UPDATE hoặc DELETE.
• Sau đó, chỉ định tên của bảng mà trigger liên kết tới.
• Sau đó, chỉ định loại trình kích hoạt bằng cách sử dụng FOR EACH ROW hoặc FOR EACH STATEMENT. Chúng ta sẽ thảo luận thêm về vấn đề này trong phần tiếp theo.
• Cuối cùng, đặt logic của trigger trong BEGIN ... END.

Bên cạnh việc sử dụng mã trong BEGIN END, bạn có thể thực hiện một quy trình được lưu trữ như sau:

CREATE TRIGGER trigger_name 
[BEFORE|AFTER] event
ON table_name trigger_type
EXECUTE stored_procedure_name;

ROW TRIGGER VÀ STATEMENT TRIGGER

Có hai loại trigger: Row trigger và Statement trigger.

Row trigger thực thi mỗi khi một hàng bị ảnh hưởng bởi một lệnh UPDATE . Nếu lệnh UPDATE ảnh hưởng đến 10 hàng, Row trigger sẽ thực thi 10 lần, mỗi lần trên mỗi hàng. Nếu lệnh UPDATE không ảnh hưởng đến bất kỳ hàng nào, thì Row trigger sẽ không được thực thi.

Khác với Row trigger, Statement trigger được gọi một lần bất kể có bao nhiêu hàng ảnh hưởng bởi lệnh UPDATE. Lưu ý rằng nếu lệnh UPDATE không ảnh hưởng đến bất kỳ hàng nào, trigger sẽ vẫn được thực thi.

Khi tạo trigger, bạn có thể chỉ định liệu trigger là Row hay Statement bằng cách sử dụng FOR EACH ROW hoặc FOR EACH STATEMENT tương ứng.

CÁCH DÙNG TRIGGER

Bạn thường sử dụng trình kích hoạt trong các trường hợp sau:

• Sửa đổi bảng log. Một số bảng có dữ liệu nhạy cảm như email khách hàng, lương nhân viên, v.v. mà bạn muốn ghi lại tất cả các thay đổi. Trong trường hợp này, bạn có thể tạo trigger UPDATE để chèn các thay đổi vào một bảng riêng biệt.
• Thực thi tính toàn vẹn phức tạp của dữ liệu. Trong trường hợp này, bạn có thể xác định trigger để xác thực dữ liệu và định dạng lại dữ liệu nếu cần. 
  Ví dụ: bạn có thể chuyển đổi dữ liệu trước khi chèn hoặc cập nhật bằng cách sử dụng một BEFORE INSERT hoặc BEFORE UPDATE.

VÍ DỤ

Chúng tôi sẽ sử dụng bảng employees trong để trình diễn.

Giả sử chúng ta muốn ghi lại những thay đổi của các giá trị trong cột salary. Để thực hiện việc này, chúng tôi tạo một bảng riêng để lưu trữ các thay đổi và sử dụng trigger để chèn các thay đổi vào bảng này.

Câu lệnh sau tạo bảng salary_changes.

CREATE TABLE salary_changes (
    employee_id INT,
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    old_salary DECIMAL(8 , 2 ),
    new_salary DECIMAL(8 , 2 ),
    PRIMARY KEY (employee_id , changed_at)
);

Bảng salary_changes ghi lại employee id, old salary, new salary và thời điểm thay đổi. Lưu ý rằng cột change_at sử dụng thời gian hiện tại làm mặc định để ghi lại thời gian khi thay đổi xảy ra.

Trigger before_update_salary sau ghi lại các thay đổi lương vào bảng salary_changes.

CREATE TRIGGER before_update_salary
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
   IF NEW.salary <> OLD.salary THEN
	INSERT INTO salary_changes(employee_id,old_salary,new_salary)
        VALUES(NEW.employee_id,OLD.salary,NEW.salary);
    END IF;
END;

Trong phần thân của trigger, chúng tôi chèn các thay đổi nếu mức lương mới khác với mức lương cũ.

Lưu ý rằng trong nội dung của trigger, chúng tôi sử dụng từ khóa OLD và NEW để truy cập vào các cột trong các hàng bị ảnh hưởng bởi trigger.

Hãy kiểm tra trigger bằng cách tăng lương của nhân viên có id là 102 5%.

Đầu tiên, hãy kiểm tra mức lương hiện tại của nhân viên 102:

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    salary
FROM
    employees
WHERE
    employee_id = 110;

Thứ hai, tăng lương thêm 5% bằng cách đưa ra lệnh UPDATEt sau đây.

UPDATE employees 
SET 
    salary = salary * 1.05
WHERE
    employee_id = 110;

Thứ ba, kiểm tra bảng salary_changes để xem liệu trigger đã được gọi hay chưa.

SELECT 
    *
FROM
    salary_changes;

Như bạn thấy, bảng salary_changes có một mục mới. Nó có nghĩa là trigger đã được gọi một cách chính xác.

CHỈNH SỬA TRIGGER

Để thay đổi định nghĩa trigger, bạn sử dụng lệnh CREATE OR REPLACE TRIGGER.

Về cơ bản, CREATE OR REPLACE TRIGGER tạo ra một trigger mới nếu nó không tồn tại và thay đổi trigger nếu nó tồn tại.

Câu lệnh CREATE OR REPLACE TRIGGER tương tự như lệnh CREATE TRIGGER như sau:

CREATE OR REPLACE TRIGGER trigger_name 
[BEFORE|AFTER] event
ON table_name trigger_type
BEGIN
  -- trigger_logic
END;

XÓA TRIGGER

Để xóa trình kích hoạt, bạn sử dụng câu lệnh DROP TRIGGER như sau:

DROP TRIGGER [IF EXISTS] trigger_name;

Các IF EXISTS tùy chọn cho phép bạn xóa một trigger nếu có tồn tại. Nếu trigger không tồn tại, thì câu lệnh không có tác dụng gì. Tuy nhiên, nếu bạn không có IF EXISTS tùy chọn này, hệ thống cơ sở dữ liệu có thể xảy ra lỗi nếu bạn cố gắng thả một trigger không tồn tại.

Một lần nữa, nếu bạn xóa một bảng, tất cả các trigger được liên kết với bảng cũng bị xóa. Câu lệnh sau sẽ xóa trigger là before_update_salary:

DROP TRIGGER IF EXISTS before_update_salary;

Bây giờ bạn đã hiểu rõ về các trigger SQL và biết cách tạo trigger trong hệ thống cơ sở dữ liệu.

Xem bài viết Tiếng Anh tại đây.