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?

TRIGGERS	PROCEDURES
Một Trigger sẽ thực thi tự động mỗi khi có các sự kiện INSERT, DELETE, UPDATE xảy ra trong một table.	Một Procedure sẽ được thi khi khi được gọi thông qua lệnh exec, EXECUTE, hay đơn giản procedure_name
Không thể gọi một trigger trong một trigger khác.	Có thể gọi một procedure trong một procedure khác.
Cú pháp: CREATE TRIGGER TRIGGER_NAME	Cú pháp: CREATE PROCEDURE PROCEDURE_NAME
Các mệnh đề trong transaction như COMMIT, ROLLBACK, SAVEPOINT đều không được sử dụng trong trigger	Tất cả các mệnh đề trong transaction như COMMIT, ROLLBACK, SAVEPOINT đều được sử dụng trong procedure.
Trigger được sử dụng để duy trì tính toàn vẹn của dữ liệu bằng cách thực thi các mã lệnh trên các dòng dữ liệu mỗi khi xảy ra các hoạt động có thể ảnh hưởng đến dữ liệu.	Procedure được dùng để thực thi các công việc cụ thể được người dùng định nghĩa.
Trigger không có giá trị trả về cũng như các tham số đầu vào.	Hoạt động như một hàm, procedure có thể nhận các tham số và trả về các kết quả.