SQL Server: Tạo các trigger DML
Các trigger DML được thực thi khi sự kiện DML xảy ra trong các bảng hoặc khung nhìn. Những sự kiện DML này bao gồm các câu lệnh INSERT, UPDATE và DELETE. Các trigger DML có thể thực thi khi hoàn thành các sự kiện DML hoặc ở vị trí của các sự kiện DML.
Các trigger DML bắt buộc tính toàn vẹn tham chiếu bằng cách xếp tầng các thay đổi tới các bảng có liên quan khi một hàng được sửa đổi. Các trigger DML có thể thực hiện nhiều hành động cho mỗi câu lệnh sửa đổi.
Các trigger DML gồm ba loại chính:
· Trigger INSERT
· Trigger UPDATE
· Trigger DELETE
Các loại trigger DML
Giới thiệu về các bảng Inserted và Deleted
Những câu lệnh SQL trong các trigger DML sử dụng hai loại bảng đặc biệt để sửa đổi dữ liệu trong cơ sở dữ liệu. Khi dữ liệu được chèn, cập nhật hoặc xóa thì SQL Server tạo ra và quản lý những bảng này một cách tự động. Các bảng tạm thời lưu trữ dữ liệu ban đầu cũng như dữ liệu đã sửa đổi. Những bảng này gồm Inserted và Deleted.
Bảng Inserted
Bảng Inserted chứa bản sao các bản ghi được sửa đổi với hoạt động INSERT và UPDATE trên bảng trigger (bảng trigger là bảng trên đó trigger được định nghĩa). Hoạt động INSERT và UPDATE sẽ tiến hành chèn các bản ghi mới vào bảng Inserted và bảng trigger.
Bảng Deleted
Bảng Deleted chứa bản sao của các bản ghi được sửa đổi với hoạt động DELETE và UPDATE trên bảng trigger. Những hoạt động này sẽ xóa các bản ghi từ bảng trigger và chèn chúng vào bảng Deleted.
Chú ý: Bảng Inserted và Deleted về khía cạnh vật lý chúng không tồn tại trong cơ sở dữ liệu. Chúng được tạo ra và hủy bỏ bất cứ khi nào với bất kỳ sự kiện kích hoạt nào xảy ra.
Trigger INSERT
Trigger INSERT được thực thi khi một bản ghi mới được chèn vào bảng. Trigger INSERT đảm bảo rằng giá trị đang được nhập phù hợp với các ràng buộc được định nghĩa trên bảng đó.
Khi người dùng chèn một bản ghi vào bảng tương ứng, thì trigger INSERT sẽ lưu một bản sao của bản ghi đó vào bảng Inserted. Sau đó nó sẽ kiểm tra xem giá trị mới trong bảng Inserted phù hợp với các ràng buộc đã chỉ định hay không. Nếu bản ghi hợp lệ, trigger INSERT tiến hành chèn nó vào bảng trigger, nếu không nó sẽ hiển thị thông báo lỗi.
Trigger INSERT được tạo ra bằng cách sử dụng từ khóa INSERT trong câu lệnh CREATE TRIGGER và ALTER TRIGGER.
Sau đây là cú pháp cho việc tạo ra trigger INSERT:
[IF UPDATE (column_name)...]
[{AND | OR} UPDATE (column_name)...]
<sql_statements>
, trong đó:
schema_name: chỉ ra tên của lược đồ có chứa bảng/trigger đó.
trigger_name: chỉ ra tên của trigger do người dùng tự đặt.
table_name: chỉ ra bảng mà trên đó trigger DML được tạo ra (bảng trigger).
WITH ENCRYPTION: mã hóa văn bản của câu lệnh CREATE TRIGGER.
FOR: chỉ ra rằng trigger DML thực thi sau khi các hoạt động sửa đổi được hoàn tất.
INSERT: chỉ ra rằng trigger DML này sẽ được gọi bởi các hoạt động chèn.
UPDATE: trả về giá trị Boolean cho biết xem hành động INSERT hoặc UPDATE đã được thực hiện hay chưa trên một cột cụ thể.
column_name: là tên của cột để kiểm tra về hành động UPDATE.
AND: Kết hợp hai biểu thức Boolean và trả về TRUE khi cả hai biểu thức là TRUE.
OR: Kết hợp hai biểu thức Boolean và trả về TRUE nếu ít nhất một biểu thức là TRUE.
sql_statement: chỉ ra các câu lệnh SQL được thực thi trong trigger DML.
Đoạn mã dưới đây sẽ tạo ra trigger INSERT có tên CheckWithdrawal_Amount trên bảng có tên là Account_Transactions. Khi một bản ghi mới được đưa vào, và nếu số tiền rút vượt quá 80000 thì trigger INSERT sẽ hiển thị thông báo lỗi và quay lui giao tác sử dụng câu lệnh ROLLBACK TRANSACTION (giao tác là tập hợp một hoặc nhiều câu lệnh được xử lý như một đơn vị công việc. Giao tác có thể thành công hoặc thất bại, do đó tất cả các câu lệnh trong đó cùng thành công hay thất bại. Câu lệnh ROLLBACK TRANSACTION được dùng khi muốn hủy bỏ hoặc quay lui một giao tác).
IF (SELECT Withdrawal From inserted) > 80000
BEGIN
PRINT 'Số tiền rút không thể vượt quá 80000.'
ROLLBACK TRANSACTION
END
Đoạn mã sau đây sẽ chèn một bản ghi trong đó Số tiền rút (Withdrawal) là 90000. Điều này làm cho trigger INSERT hiển thị thông báo lỗi và quay lui giao tác.
(TransactionID, EmployeeID, CustomerID, TransactionTypeID, TransactionDate, TransactionNumber, Deposit, Withdrawal)
VALUES (1008,'E08','C08','T08','05/02/12','TN08', 300000,90000)
Thông báo lỗi sau được hiển thị như được chỉ ra bằng câu lệnh PRINT:
Số tiền rút không thể vượt quá 80000.
Trigger UPDATE
Trigger UPDATE sao chép bản ghi gốc vào bảng Deleted và bản ghi mới vào bảng Inserted khi bản ghi được cập nhật. Sau đó nó đánh giá bản ghi mới để xác định xem các giá trị này có phù hợp với các ràng buộc đã chỉ định trong bảng trigger hay không.
Nếu các giá trị mới là hợp lệ thì bản ghi từ bảng Inserted sẽ được sao chép vào bảng trigger. Tuy nhiên, nếu các giá trị mới là không hợp lệ thì thông báo lỗi được hiển thị. Ngoài ra, bản ghi gốc được sao chép từ bảng Deleted sẽ được đưa trở lại bảng trigger.
Trigger UPDATE được tạo ra bằng cách sử dụng từ khóa UPDATE trong câu lệnh CREATE TRIGGER và ALTER TRIGGER.
Sau đây là cú pháp cho việc tạo ra trigger UPDATE ở mức bảng:
[IF UPDATE (column_name)...]
[{AND | OR} UPDATE (column_name)...]
<sql_statements>
, trong đó:
FOR UPDATE: chỉ ra rằng trigger DML này sẽ được gọi sau các hoạt động cập nhật.
Đoạn mã sau đây tạo ra trigger UPDATE ở mức bảng trên bảng EmployeeDetails. Khi một bản ghi được sửa đổi thì trigger UPDATE được kích hoạt. Nó sẽ kiểm tra xem ngày tháng năm sinh có lớn hơn ngày hôm nay không. Nó sẽ hiển thị thông báo lỗi cho các giá trị không hợp lệ và quay lui hoạt động sửa đổi bằng cách sử dụng câu lệnh ROLLBACK TRANSACTION.
IF (SELECT BirthDate From inserted) > getDate()
BEGIN
PRINT 'Ngày tháng năm sinh không thể lớn hơn ngày hôm nay.'
ROLLBACK
END
Đoạn mã dưới cập nhật một bản ghi trong đó ngày tháng năm sinh không hợp lệ được chỉ định. Điều này làm cho trigger UPDATE hiển thị thông báo lỗi và quay lui giao tác.
Thông báo lỗi sau được hiển thị như được chỉ ra bằng câu lệnh PRINT:
Ngày tháng năm sinh không thể lớn hơn ngày hôm nay.
Những trigger UPDATE có thể được tạo ra ở mức cột hoặc ở mức bảng. Những trigger ở mức cột thực thi khi các lần cập nhật được thực hiện trong cột chỉ định. Những trigger ở mức bảng thực thi khi các lần cập nhật được thực hiện bất cứ đâu trong toàn bộ bảng. Để tạo ra một trigger UPDATE ở mức cột ta sử dụng hàm UPDATE().
Hàm UPDATE()
Hàm này trả về một giá trị Boolean, nó chỉ ra liệu hành động UPDATE hoặc INSERT đã được thực hiện trên một khung nhìn hoặc cột cụ thể của một bảng hay chưa. Hàm UPDATE () có thể được sử dụng ở bất cứ nơi nào bên trong phần thân của một trigger UPDATE hoặc INSERT.
Sau đây là cú pháp cho hàm UPDATE():
UPDATE(tên_cột)
, trong đó:
tên_cột: là tên của cột để kiểm tra về hành động INSERT hoặc UPDATE.
Đoạn mã sau tạo ra trigger UPDATE có tên Accounting trên bảng Account_Transactions để cập nhật các cột TransactionID hoặc EmployeeID.
ON Account_Transactions
AFTER UPDATE
AS
IF ( UPDATE (TransactionID) OR UPDATE (EmployeeID) )
BEGIN
RAISERROR (50009, 16, 10)
END;
GO
Còn đoạn mã sau sẽ tạo trigger UPDATE có tên Check_EmployeeID ở mức cột trên cột EmployeeID của bảng EmployeeDetails nhằm mục đích ngăn chặn thay đổi giá trị trên cột này. Nếu người dùng tiến hành sửa giá trị trên cột EmployeeID thì trigger UPDATE được kích hoạt và thông báo lỗi được hiển thị.
IF UPDATE(EmployeeID)
BEGIN
PRINT 'Bạn không thể thay đổi ID của nhân viên'
ROLLBACK TRANSACTION
END
Đoạn mã dưới cập nhật một bản ghi trong đó giá trị trong cột EmployeeID đang được sửa đổi. Điều này làm cho trigger UPDATE được kích hoạt, nó hiển thị thông báo lỗi và quay lui giao tác đó.
Trigger Delete
Ta có thể tạo ra trigger DELETE để hạn chế người dùng không xoá một bản ghi cụ thể trong bảng. Những điều sau đây sẽ xảy ra nếu người dùng cố gắng xóa bản ghi:
· Bản ghi bị xóa khỏi bảng trigger và chèn vào bảng Deleted
· Kiểm tra các ràng buộc liên quan khi xóa
· Nếu có ràng buộc trên bản ghi để ngăn chặn việc xóa, trigger DELETE sẽ hiển thị thông báo lỗi
· Bản ghi đã xóa được lưu trữ trong bảng Deleted được sao chép ngược lại bảng trigger.
Trigger DELETE được tạo bằng cách sử dụng từ khóa DELETE trong câu lệnh CREATE TRIGGER.
Sau đây là cú pháp cho việc tạo ra một trigger DELETE:
, trong đó:
DELETE: chỉ ra rằng trigger DML này sẽ được gọi bởi các hoạt động xóa.
Đoạn mã sau tạo ra một trigger DELETE trên bảng Account_Transactions. Nếu bản ghi có giá trị 'T01' thuộc một TransactionID bị xóa thì trigger DELETE được kích hoạt và thông báo lỗi được hiển thị.
ON Account_Transactions
FOR DELETE
AS
IF 'T01' IN (SELECT TransactionID FROM deleted)
BEGIN
PRINT 'Không thể xóa bản ghi này.'
ROLLBACK TRANSACTION
END
Đoạn mã dưới thử xóa bản ghi từ bảng Account_Transactions, trong đó mã giao dịch là 'T01'.
Thông báo lỗi được hiển thị như được chỉ ra bằng câu lệnh PRINT:
Không thể xóa bản ghi này.