programming mysql

Biến bộ kích hoạt mysql từ chọn

Trình kích hoạt SQL là một đối tượng cơ sở dữ liệu mạnh mẽ khác mà chúng tôi có sẵn. Trong các bài viết trước, chúng tôi đã đề cập đến các hàm do người dùng định nghĩa, các thủ tục do người dùng định nghĩa và Chế độ xem SQL. Hôm nay chúng ta sẽ nói về các trình kích hoạt SQL và cách sử dụng chúng để đạt được hành vi mong muốn

Ngươi mâu

Trước khi chuyển sang chủ đề của bài viết này, chúng ta hãy xem nhanh mô hình mà chúng tôi đang sử dụng trong bài viết này cũng như trong suốt loạt bài này

Trong bài viết này, chúng tôi sẽ tập trung vào trình kích hoạt DML [ngôn ngữ thao tác dữ liệu] và chỉ ra cách chúng hoạt động khi chúng tôi thực hiện các thay đổi trong một bảng

Trình kích hoạt SQL là gì?

Trong SQL Server, trình kích hoạt là các đối tượng cơ sở dữ liệu, trên thực tế, là một loại thủ tục được lưu trữ đặc biệt, "phản ứng" với một số hành động nhất định mà chúng ta thực hiện trong cơ sở dữ liệu. Ý tưởng chính đằng sau trình kích hoạt là chúng luôn thực hiện một hành động trong trường hợp một số sự kiện xảy ra. Nếu chúng ta đang nói về trình kích hoạt DML, thì những thay đổi này sẽ là những thay đổi trong dữ liệu của chúng ta. Hãy xem xét một vài tình huống thú vị

Trường hợp bạn thực hiện thao tác chèn vào bảng gọi, bạn muốn cập nhật khách hàng liên quan đó có thêm 1 gọi [trường hợp đó ta nên có thuộc tính số nguyên trong bảng khách hàng]
Khi bạn hoàn thành một cuộc gọi [cập nhật cuộc gọi. giá trị thuộc tính end_time] bạn muốn tăng số lượng cuộc gọi được thực hiện bởi nhân viên đó trong ngày hôm đó [một lần nữa, chúng ta nên có thuộc tính như vậy trong bảng nhân viên]
Khi bạn cố gắng xóa một nhân viên, bạn muốn kiểm tra xem nhân viên đó có các cuộc gọi liên quan không. Nếu vậy, bạn sẽ ngăn việc xóa đó và đưa ra một ngoại lệ tùy chỉnh

Từ các ví dụ, bạn có thể nhận thấy rằng các trình kích hoạt DML là các hành động liên quan đến các lệnh SQL được xác định trong các trình kích hoạt này. Vì chúng tương tự như các thủ tục được lưu trữ, bạn có thể kiểm tra các giá trị bằng cách sử dụng câu lệnh IF, v.v. Điều này cung cấp rất nhiều tính linh hoạt

Lý do chính đáng để sử dụng trình kích hoạt SQL DML là trường hợp khi bạn muốn đảm bảo rằng một điều khiển nhất định sẽ được thực hiện trước hoặc sau câu lệnh đã xác định trên bảng đã xác định. Đây có thể là trường hợp khi mã của bạn ở khắp mọi nơi, e. g. cơ sở dữ liệu được sử dụng bởi các ứng dụng khác nhau, mã được viết trực tiếp trong các ứng dụng và bạn không có tài liệu đầy đủ về nó

Các loại Trình kích hoạt SQL

Trong SQL Server, chúng tôi có 3 nhóm trình kích hoạt

Trình kích hoạt DML [ngôn ngữ thao tác dữ liệu] – Chúng tôi đã đề cập đến chúng và chúng phản ứng với các lệnh DML. Đó là – CHÈN, CẬP NHẬT và XÓA
Trình kích hoạt DDL [ngôn ngữ định nghĩa dữ liệu] – Theo dự kiến, các trình kích hoạt loại này sẽ phản ứng với các lệnh DDL như – CREATE, ALTER và DROP
Kích hoạt đăng nhập – Cái tên nói lên tất cả. Loại này phản ứng với các sự kiện ĐĂNG NHẬP

Trong bài viết này, chúng tôi sẽ tập trung vào trình kích hoạt DML, vì chúng được sử dụng phổ biến nhất. Chúng tôi sẽ đề cập đến hai loại trình kích hoạt còn lại trong các bài viết tiếp theo của loạt bài này

Trình kích hoạt DML – Cú pháp

Cú pháp SQL đơn giản hóa để xác định trình kích hoạt như sau

TẠO KÍCH HOẠT [schema_name. ]tên_trigger

BẬT tên_bảng

{CHO . SAU . CÀI ĐẶT HỌC} {[INSERT] [,] [UPDATE] [,] [DELETE]}

BẰNG

{sql_statements}

Hầu hết các cú pháp nên tự giải thích. Ý tưởng chính là xác định

Một tập hợp {sql_statements} sẽ được thực hiện khi trình kích hoạt được kích hoạt [được xác định bởi các tham số còn lại]
Chúng ta phải xác định thời điểm kích hoạt kích hoạt. Đó là phần {FOR. SAU ĐÓ. THAY THẾ CỦA} không. Nếu trình kích hoạt của chúng tôi được định nghĩa là CHO. SAU ĐÓ. THAY THẾ trình kích hoạt hơn các câu lệnh SQL trong trình kích hoạt sẽ chạy sau khi tất cả các hành động kích hoạt trình kích hoạt này được khởi chạy thành công. Trình kích hoạt INSTEAD OF sẽ thực hiện các điều khiển và thay thế hành động ban đầu bằng hành động trong trình kích hoạt, trong khi FOR. SAU KHI [chúng có nghĩa giống nhau] trình kích hoạt sẽ chạy các lệnh bổ sung sau khi hoàn thành câu lệnh ban đầu
Phần {[INSERT] [,] [UPDATE] [,] [DELETE]} biểu thị lệnh nào thực sự kích hoạt trình kích hoạt này. Chúng tôi phải chỉ định ít nhất một tùy chọn, nhưng chúng tôi có thể sử dụng nhiều tùy chọn nếu cần

Với suy nghĩ này, chúng ta có thể dễ dàng viết các trình kích hoạt sẽ

Kiểm tra [trước khi chèn] nếu tất cả các tham số của câu lệnh INSERT đều ổn, thêm một số nếu cần và thực hiện thao tác chèn
Sau khi chèn, hãy thực hiện các tác vụ bổ sung, chẳng hạn như cập nhật giá trị trong bảng khác
Trước khi xóa, hãy kiểm tra xem có bản ghi liên quan nào không
Cập nhật các giá trị nhất định [e. g. tệp nhật ký] sau khi xóa xong

Nếu bạn muốn bỏ kích hoạt, bạn sẽ sử dụng

DROP KÍCH HOẠT [schema_name. ]tên_kích hoạt;

Trình kích hoạt SQL INSERT – Ví dụ

Đầu tiên, chúng ta sẽ tạo một trình kích hoạt SQL đơn giản sẽ thực hiện kiểm tra trước câu lệnh INSERT

THẢI KÍCH HOẠT NẾU TỒN TẠI t_country_insert;

ĐI

TẠO KÍCH HOẠT t_country_insert BẬT quốc gia INSTEAD OF INSERT

NHƯ BẮT ĐẦU

KHAI BÁO @country_name CHAR[128];

KHAI BÁO @country_name_eng CHAR[128];

KHAI BÁO @mã quốc gia CHAR[8];

CHỌN @tên_quốc_gia = country_name, @country_name_eng = country_name_eng, @country_code = country_code FROM INSERTED;

NẾU @tên_quốc_gia LÀ NULL SET @country_name = @country_name_eng;

IF @country_name_eng IS NULL SET @country_name_eng = @country_name;

CHÈN VÀO quốc gia [country_name, country_name_eng, country_code] VALUES [@country_name, @country_name_eng, @country_code];

END;

Chúng ta có thể thấy trình kích hoạt của mình trong Object Explorer, khi chúng ta mở rộng dữ liệu cho bảng liên quan [quốc gia]

Tôi muốn nhấn mạnh một vài điều ở đây

Câu lệnh INSERT kích hoạt truy vấn này và thực sự được thay thế [INSTEAD OF INSERT] bằng câu lệnh trong trình kích hoạt này
Chúng tôi đã xác định một số biến cục bộ để lưu trữ các giá trị từ bản ghi chèn ban đầu [INSERTED]. Bản ghi này dành riêng cho trình kích hoạt và nó cho phép bạn truy cập vào bản ghi này và các giá trị của nó

Ghi chú. Bản ghi INSERTED có thể được sử dụng trong trình kích hoạt SQL chèn và cập nhật

Với các câu lệnh IF, chúng tôi đã kiểm tra các giá trị và SET giá trị nếu chúng không được đặt trước đó
Khi kết thúc truy vấn, chúng tôi đã thực hiện câu lệnh INSERT [câu lệnh thay thế câu lệnh ban đầu đã kích hoạt trình kích hoạt này]

Bây giờ hãy chạy lệnh INSERT INTO và xem điều gì xảy ra trong cơ sở dữ liệu. Chúng tôi sẽ chạy các câu lệnh sau

CHỌN * TỪ quốc gia;

CHÈN VÀO quốc gia [tên_quốc_gia, country_code] VALUES ['United Kingdom', 'UK'];

CHỌN * TỪ quốc gia;

Kết quả là trong hình dưới đây

Bạn có thể dễ dàng nhận thấy rằng hàng có id = 10 đã được chèn vào. Chúng tôi chưa chỉ định country_name, nhưng trình kích hoạt đã thực hiện công việc của nó và điền giá trị đó bằng country_name_eng

Ghi chú. Nếu trigger được định nghĩa trên một table nào đó thì đối với một hành động nào đó, nó sẽ luôn chạy khi hành động này được thực hiện

Kích hoạt SQL DELETE – Ví dụ

Bây giờ, hãy tạo một trình kích hoạt sẽ kích hoạt câu lệnh DELETE trên bảng quốc gia

THẢI KÍCH HOẠT NẾU TỒN TẠI t_country_delete;

ĐI

TẠO KÍCH HOẠT t_country_delete BẬT quốc gia INSTEAD OF DELETE

NHƯ BẮT ĐẦU

KHAI BÁO @id INT;

KHAI BÁO @đếm INT;

CHỌN @id = id FROM DELETED;

CHỌN @đếm = COUNT[*] FROM city WHERE country_id = @id;

NẾU @đếm = 0

XÓA TỪ quốc gia NƠI id = @id;

KHÔNG

THROW 51000, 'không thể xóa - quốc gia, 1;

END;

Đối với trình kích hoạt này, cần nhấn mạnh những điều sau

Một lần nữa, chúng tôi thực hiện hành động trước [thay vì] thực thi thực tế [THAY LẠI XÓA]
Chúng tôi đã sử dụng hồ sơ DELETED. Bản ghi này có thể được sử dụng trong các trình kích hoạt liên quan đến câu lệnh DELETE

Ghi chú. Bản ghi đã XÓA có thể được sử dụng để xóa và cập nhật trình kích hoạt SQL

Chúng tôi đã sử dụng câu lệnh IF để xác định xem nên xóa hay không nên xóa hàng. Nếu cần, chúng tôi đã thực hiện câu lệnh DELETE và nếu không, chúng tôi sẽ ném và ngoại lệ

Chạy câu lệnh dưới đây không có lỗi vì quốc gia có id = 6 không có bản ghi liên quan

XÓA TỪ quốc gia NƠI id = 6;

Nếu chúng tôi chạy câu lệnh này, chúng tôi sẽ thấy thông báo lỗi tùy chỉnh, như trong hình bên dưới

XÓA TỪ quốc gia NƠI id = 1;

Một thông báo như vậy không chỉ mang tính mô tả mà còn cho phép chúng tôi xử lý lỗi này một cách tốt đẹp và hiển thị một thông báo có ý nghĩa hơn cho người dùng cuối

Trình kích hoạt CẬP NHẬT SQL

Tôi sẽ để lại cái này cho bạn, như một thông lệ. Vì vậy, hãy cố gắng ghi lại trình kích hoạt CẬP NHẬT. Điều quan trọng bạn nên biết là trong trình kích hoạt cập nhật, bạn có thể sử dụng cả hai bản ghi – INSERTED [sau khi cập nhật] và DELETED [trước khi cập nhật]. Trong hầu hết các trường hợp, bạn sẽ cần sử dụng cả hai

Khi nào nên sử dụng Trình kích hoạt SQL?

Trình kích hoạt chia sẻ nhiều điểm chung với các thủ tục được lưu trữ. Tuy nhiên, so với các thủ tục được lưu trữ, chúng bị giới hạn về những gì bạn có thể làm. Do đó, tôi muốn có một quy trình được lưu trữ để chèn/cập nhật/xóa và thực hiện tất cả các kiểm tra và hành động bổ sung ở đó

Tuy nhiên, đó không phải lúc nào cũng là lựa chọn. Nếu bạn kế thừa một hệ thống hoặc đơn giản là bạn không muốn đưa tất cả logic vào các thủ tục được lưu trữ, thì trình kích hoạt có thể là giải pháp cho nhiều vấn đề mà bạn có thể gặp phải

Làm cách nào để gán giá trị cho biến trong trình kích hoạt SQL?

Vậy làm cách nào để gán Giá trị cho biến được khai báo chính? . SELECT CustomerID INTO CustomerId FROM Seller WHERE Id = NEW. GIỚI HẠN ID người bán lại 1 . Nhưng sau khi kích hoạt xong, biến này sẽ bị hủy và giá trị của nó sẽ bị mất.

Chúng ta có thể sử dụng trigger với câu lệnh SELECT không?

Sự kiện kích hoạt bắt đầu hành động kích hoạt có thể là một câu lệnh CHÈN, XÓA, CẬP NHẬT hoặc CHỌN . Câu lệnh MERGE cũng có thể là sự kiện kích hoạt cho trình kích hoạt CẬP NHẬT, XÓA hoặc CHÈN.

Làm cách nào để truy cập giá trị mới trong trình kích hoạt MySQL?

Đầu tiên, chỉ định tên của trình kích hoạt mà bạn muốn tạo trong mệnh đề CREATE TRIGGER. Thứ hai, sử dụng mệnh đề SAU CẬP NHẬT để chỉ định thời gian gọi trình kích hoạt. Thứ ba, chỉ định tên của bảng mà trình kích hoạt thuộc về sau từ khóa BẬT

Làm cách nào để khai báo và đặt biến trong MySQL?

Sau đây, chúng ta sẽ tìm hiểu về biến do người dùng định nghĩa. Chúng ta có thể đặt một số giá trị cho biến với sự trợ giúp của lệnh SET. SET @yourVariableName=value; Lưu ý − Trong câu lệnh SELECT, “yourVariableName” chứa giá trị NULL và sau khi sử dụng lệnh SET, nó chứa giá trị mà .