SQL Server – Stored procedure và Trigger

Trong bài viết lần này chúng ta sẽ cùng nhau tìm hiểu Stored procedure và trigger trong SQL Server là cái gì và được sử dụng như thế nào và cùng xem thử chúng có đáng sợ, nguy hiểm và cao siêu như những gì mọi người thường nghĩ hay không

Stored procedure là gì?

Stored procedure (thủ tục lưu trữ) là tập hợp một hoặc nhiều câu lệnh T-SQL thành một nhóm đơn vị xử lý logic và được lưu trữ trên Database Server. Khi một câu lệnh gọi chạy stored procedure lần đầu tiên thì SQL Server sẽ chạy nó và lưu trữ vào bộ nhớ đệm, gọi là plan cache, những lần tiếp theo SQL Server sẽ sử dụng lại plan cache nên sẽ cho tốc độ xử lý tối ưu.

SQL Server cung cấp các thủ tục lưu trữ sẵn trong hệ thống giúp thực hiện 1 số công việc thường xuyên, nó được gọi là thủ tục hệ thống (system stored procedure) còn những thủ tục do người dùng tự viết gọi là user stored procedure

Thủ tục lưu trữ trong sql server cũng tương tự như khái niệm về thủ tục trong các ngôn ngữ lập trình khác bởi vì nó chấp nhận biến đầu vào và trả lại kết quả khi thực hiện , chứa những câu lệnh dùng trong lập trình có thể thao tác với cơ sở dữ liệu và có thể gọi đến các thủ tục khác, trả lại giá trị trạng thái khi thủ tục được gọi để xác định việc thực hiện thủ tục thành công hay thất bại.

Thủ tục lưu trữ có thể trả về dữ liệu theo 1 trong 4 cách:

• Trả về qua đối số đầu ra, tức là trả về dữ liệu kiểu số nguyên hoặc kiểu kí tự hay biến con trỏ
• Trả về kết quả sử dụng từ khóa return
• Trả về 1 tập kết quả với mỗi lệnh Select chứa trong thủ tục lưu trữ hoặc trong 1 thủ tục lưu trữ khác được gọi bởi thủ tục lưu trữ hiện hành
• Trả về kết quả thông qua 1 con trỏ toàn cục mà có thể được tham chiếu từ bên ngoài thủ tục lưu trữ

Vì sao nên dùng Stored procedure

• Giảm dung lượng chuyển đổi qua lại giữa client và server bởi vì ta chỉ cần truyền lời gọi thực thi stored procedure qua mạng đến server mà ko phải truyền chi tiết từng câu lệnh truy vấn
•  Bảo mật hơn , chỉ cần truyền lời gọi stored procedure qua mạng nên người khác ko thể biết được tên bảng, tên các đối tượng nhúng trong các câu lệnh vấn tin tức là sẽ ko thể lần theo đó mà đánh cắp thông tin.
• Có thể tái sử dụng code, tức là tái sử dụng stored procedure đã được viết cho ứng dụng này nếu phù hợp thì có thể sử dụng cho ứng dụng khác
• Cải thiện hiệu năng, stored procedure sau khi thực thi lần đầu tiên sẽ được lưu trong vùng kết của bộ nhớ ở những lần sử dụng sau đó thì sql server sẽ ko cần biên dịch lại stored procedure này nữa điều đó sẽ giúp giảm thời gian trả lời vấn tin

Nhược điểm của Stored procedure

• Thiếu tính khả chuyển (Lack of Portability) : Stored procedure khó chuyển từ một hệ quản trị cơ sở dữ liệu sang một hệ quản trị cơ sở dữ liệu khác. Yêu cầu lập trình và kiểm thử lại đáng kể
• Tải DB Server : Sử dụng stored procedure nhiều có thể gây quá tải database server (SQL Server phải lưu trữ quá nhiều kế hoạch thực thi)

Ví dụ Stored procedure 

CREATE PROCEDURE uspFindProductByModel (

@model_year SMALLINT,

@product_count INT OUTPUT

) AS

BEGIN

SELECT product_name, list_price FROM production.products

  WHERE model_year = @model_year;

SELECT @product_count = @@ROWCOUNT;

END;

DECLARE @count INT;

EXEC uspFindProductByModel

@model_year = 2018

@product_count = @count;

SELECT @count AS 'Number of products found';

Trigger là cái gì?

Trigger là một loại stored procedure đặc biệt (không có tham số) được thực thi (execute) một cách tự động ở phía server khi có một sự kiện thay đổi dữ liệu (data modification) như Insert, Delete, hay Update . Trigger được lưu trữ và quản lý trong Server DB, được dùng trong trường hợp ta muốn kiểm tra các ràng buộc toàn vẹn trong DB.

Trigger còn được gọi là bẫy lỗi, 1 bẫy lỗi được gắn liền với  1 bảng và được thực thi khi xuất hiện 1 sự kiện đặc biệt trong bảng như là insert, update hay delete.

Các bẫy lỗi sẽ được kích hoạt tự động khi 1 hành động xảy ra, các bẫy lỗi được sử dụng phổ biến để ép các thao tác tuân theo 1 quy tắc nhất định, chúng giám sát sự thay đổi để chắc chắn rằng sự thay đổi đó là phù hợp với các quy tắc nghiệp vụ vì vậy các bẫy lỗi thường được sử dụng để đảm bảo tính toàn vẹn dữ liệu.

Trong SQL, bạn sẽ gặp phải 3 loại Trigger như sau:

• Trigger cho DML (ngôn ngữ thao tác dữ liệu): bao gồm 3 kiểu là INSERT, UPDATE và DELETE.
• Trigger cho DDL (ngôn ngữ định nghĩa dữ liệu): sử dụng cho các lệnh CREATE, ALTER và DROP.
• Trigger cho LOGON: sử dụng để kiểm soát các lệnh khi đăng nhập vào máy chủ.

Khi nào sử dụng Trigger ?

• Duy trì bản sao và dẫn xuất của dữ liệu
• Sử dụng trong trường hợp column phải có 1 ràng buộc phức tạp mà ko thể biểu diễn được bằng constraints
• Thay đổi theo tầng hoặc xóa dữ liệu liên quan đến các bảng khác trong 1 cơ sở dữ liệu
• Thiết lập giá trị ban đầu cho cột, giá trị này thường phức tạp mà ko thể sử dụng ràng buộc default
• Hủy bỏ những thay đổi ko đúng để đảm bảo toàn vẹn dữ liệu

Ví dụ Trigger 

CREATE TRIGGER trg_DatHang ON tbl_DatHang AFTER INSERT AS 

BEGIN

UPDATE tbl_KhoHang

SET SoLuongTon = SoLuongTon - (

SELECT SoLuongDat

FROM inserted

WHERE MaHang = tbl_KhoHang.MaHang

)

FROM tbl_KhoHang

JOIN inserted ON tbl_KhoHang.MaHang = inserted.MaHang

END

GO

So sánh Trigger và Stored procedure?

Nguồn tham khảo

https://www.geeksforgeeks.org/difference-between-trigger-and-procedure-in-dbms/

https://www.tutorialspoint.com/difference-between-stored-procedure-and-triggers-in-sql

https://www.tsql.info/sql-server/stored-procedure-vs-trigger.php