Trang chủ / Sql Server

Các Lệnh Hệ Thống Và Các Hàm Sử Dụng Trong Trigger


ON Nganh AFTER Delete AS

BEGIN

select * from inserted select * from deleted END

Kết quả kiểm tra hoạt động của trigger Trigger_DeleteNganh khi hành động Delete được thực thi: Bảng inserted không có dữ liệu và bảng deleted chứa bản ghi bị xóa.

Hình 5 2 Kết quả thực hiện hành động Delete Ví dụ 3 Tạo Trigger 1

Hình 5.2. Kết quả thực hiện hành động Delete

Ví dụ 3: Tạo Trigger Trigger_UpdateNganh cho bảng Ngành. Khi hành động update xảy ra trên bảng Nganh, trigger Trigger_UpdateNganh được kích hoạt. Câu lệnh SELECT bên trong trigger sẽ hiển thị bản ghi chứa trong bảng Inserted và bảng Deleted.

CREATE TRIGGER Trigger_UpdateNganh ON Nganh

AFTER Update AS

BEGIN

select * from inserted select * from deleted END

Kết quả kiểm tra hoạt động của trigger Trigger_UpdateNganh khi hành động Update được thực thi: Bảng inserted chứa bản ghi được cập nhật và bảng deleted chứa bản ghi trước khi được cập nhật.


Hình 5 3 Kết quả thực hiện hành động Update 5 3 2 Các lệnh hệ thống và các 2

Hình 5.3. Kết quả thực hiện hành động Update

5.3.2. Các lệnh hệ thống và các hàm sử dụng trong trigger

1) Các hàm và các lệnh hệ thống

a) Hàm @@ROWCOUNT: Trả về số dòng bị ảnh hưởng bởi câu lệnh T-SQL ngay trước đó trong trigger.

b) Câu lệnh RETURN: Nếu không có dòng nào bị ảnh hưởng bởi hàng động INSERT, UPDATE hoặc DELETE, trigger vẫn kích hoạt. Trong trường hợp này, ta có thể sử dụng câu lệnh RETURN để chấm dứt việc thi hành không cần thiết các câu lệnh của trigger. Lệnh RETURN được sử dụng tại các vị trí trong trigger mà tại đó ta muốn chấm dứt việc thi hành tiếp tục các câu lệnh khác của trigger.

c) Câu lệnh RAISERROR: Được dùng để hiển thị thông báo lỗi hỗ trợ tiếng việt, dùng để đưa ra màn hình câu thông báo mang tính chất cảnh báo. Chuỗi thông báo khi sử dụng RAISERROR sẽ được gởi về cho máy khách từ Server.

Cú pháp:

RAISERROR (<Message ID|Message String>,<Serverity>

,<State> [,<Argument>] [,<…n>]) [WITH option [,…n]] Trong đó:

- Message ID: Số ID của nội dung lỗi trong hệ thống lỗi của SQL Server. Các thông báo lỗi của hệ thống chứa trong bảng sysmessages.

- Message String: Nội dung của thông báo lỗi.

- Serverity: Là Code chỉ dẫn cách phát sinh lỗi. Trong SQL Server, chỉ dẫn lỗi tùy thuộc vào miền đại diện cho lỗi phát sinh:

STT

Miền đại diện

Ý nghĩa

1

1-9

Lỗi phát sinh do dữ liệu,

2

10

Lỗi phát sinh do dữ liệu, nhưng không xuất lỗi về trình

khách

3

11-16

Tạm dừng thủ tục và đưa lỗi về cho trình khách

4

17

Lỗi phát sinh do thực hiện ngoài tài nguyên của cơ sở dữ

Có thể bạn quan tâm!

Xem toàn bộ 323 trang tài liệu này.


STT

Miền đại diện

Ý nghĩa



liệu.

5

18-19

Lỗi phát sinh do nguyên nhân của hệ thống

6

20-25

Lỗi phát sinh do quá trình kết nối, hay do quá trình

truyền dữ liệu.

- Status: Là giá trị về trạng thái, chỉ rò lỗi thuộc về nhóm nào trong hệ thống. Status có giá trị từ 1 đến 27. Giá trị mặc định là 1.

- Argument: Là các tham số được sử dụng để thay thế cho các biến được định nghĩa trong Message ID hoặc Message String. Có thể là 0 hoặc nhiều tham số thay thế, nhưng tổng số các tham số thay thế không thể vượt quá 20. Mỗi tham số thay thế có thể là một biến địa phương hoặc bất kỳ các loại dữ liệu: tinyint, smallint, int, char, varchar, nchar, nvarchar, nhị phân, hoặc varbinary. Ngoài ra không hỗ trợ loại dữ liệu khác.

- WITH OPTION: Là một lựa chọn tùy chỉnh cho các lỗi và có thể là một trong những giá trị sau.

STT

Miền

đại diện

Ý nghĩa


1


LOG

Ghi lại những lỗi trong bản ghi lỗi và đăng nhập áp dụng cho trường hợp của Microsoft SQL Server Database Engine. Lỗi đăng nhập trong bản ghi lỗi hiện bị giới hạn tối đa của

440 byte. Chỉ có một thành viên với vai trò máy chủ sysadmin cố định hoặc một người dùng với quyền truy cập

TRACE ALTER mới có thể chỉ định VỚI LOG.

2

NOWAIT

Gửi thông báo lỗi ngay lập tức tới máy khách.


3


SETERROR

Thiết lập @@ERROR và giá trị của ERROR_NUMBER bằng giá trị của Message ID or 50000, bất kể mức độ lỗi là

gì.

d) Câu lệnh ROLLBACK TRANSACTION: Được dùng để bỏ qua toàn bộ thao tác trước đó là nguyên nhân kích hoạt trigger hoặc quay lui toàn bộ lô xử lý (batch) của trigger. Một lỗi không xác định nào đó cũng làm cho quay lui toàn bộ giao tác. Nếu muốn hoàn tất giao tác trong tất cả các trường hợp thì không nên dùng câu lệnh ROLLBACK TRANSACTION, ngoại trừ có lỗi không xác định nào đó xảy ra trong lúc thực hiện giao tác.

Ví dụ: Tạo Trigger để kiểm tra ràng buộc khóa ngoại trên bảng thí sinh với yêu cầu khi thêm mới bản ghi thì sẽ kiểm tra giá trị trên trường mã ngành, nếu giá trị này chưa có trong bảng thì bỏ qua thao tác thêm và hiển thị câu thông báo lỗi.


CREATE TRIGGER Trigger_Khoa ON thisinh

AFTER INSERT AS

BEGIN

declare @man varchar(20) if @@Rowcount=1

begin

select @man=(select man from inserted) if not exists

(select 'true' from nganh where man=@man) begin

raiserror('Mã ngành này không có trong bảng ngành',16,1) rollback tran

return end end


END

else


begin

raiserror('Chỉ cho phép thêm một bản ghi',16,1) rollback tran

return end

2) Kiểm tra trường được cập nhật

a) Mệnh đề UPDATE

Tác dụng: Xác định có hay không một hành động thêm hoặc cập nhật xảy ra trên một trường. Hàm này chỉ có hiệu lực trong Trigger. Hàm trả về giá trị TRUE nếu trường được cập nhật, trả về giá trị FALSE nếu trường không được cập nhật.

Cú pháp: UPDATE(Field_Name)

Trong đó Field_Name là tên trường

Chú ý: Khi thao tác thêm xảy ra trên một bảng nào đó, thì tất cả các trường trên bảng đó đều xảy ra cập nhật cho dù có tồn tại một trường mà thao tác thêm ta không hề yêu cầu (trường không được yêu cầu chấp nhận mang giá trị NULL).

Để kiểm tra một trường trong bảng, ta dùng cú pháp:


If Update(Field_Name) Begin

<Statement_SQL> End

Để kiểm tra nhiều hơn một trường trong bảng, ta dùng cú pháp: If Update(Field_Name1) or Update(Field_Name2)

Begin

<Statement_SQL> End

Ví dụ 1: Tạo trigger khi thêm bản ghi cho bảng ngành để kiểm tra nếu có thay đổi tên ngành thì đưa ra thông báo.

CREATE TRIGGER Trigger_InsertN ON Nganh

AFTER Insert AS

BEGIN

IF UPDATE(Tenn)

RAISERROR(„Trường tên ngành đã được cập nhật‟,16,1)

END

Trigger Trigger_InsertN được tạo và gắn cho bảng Nganh. Khi thao tác thêm xảy ra trên bảng Nganh, trigger Trigger_InsertN được kích hoạt. Câu lệnh IF UPDATE(TenN) dùng để kiểm tra có hay không việc Update trên trường TenN. UPDATE(TenN) sẽ trả về giá trị là TRUE nếu có Update xảy ra trên trường TenN.

Dùng câu lệnh sau để kiểm tra hoạt động của Trigger:

Insert into Nganh values(„MN012‟)

Khi thực hiện câu lệnh kiểm tra này, trigger sẽ xuất câu thông báo „Truong Tenn da xay ra cap nhat‟. Điều này chứng tỏ có xảy ra việc cập nhật trên TenN, mặc dù thao tác thêm không cập nhật trường TenN.

Ví dụ 2: Tạo Trigger cho bảng MuaBan để kiểm tra khi cập nhật đơn giá bán của một mặt hàng thì đơn giá bán không thể nhỏ hơn đơn giá nhỏ nhất của mỗi lần bán hàng và không cho phép cập nhật mã khách hàng, mã mặt hàng.

CREATE TRIGGER Trigger_UpdateDG ON Muaban

AFTER update AS


BEGIN

If update(mamh) or update(makh) begin

raiserror('Không thể cập nhật mã mặt hàng hoặc mã khách hàng',16,1) rollback tran

end


if update(dongia) begin

if exists

(select 'True' from inserted

where dongia< (select avg(dongia) from muaban where muaban=1)and muaban=1)

begin

raiserror('Đơn giá không hợp lệ',16,1) rollback tran

end

end

END

b) Mệnh đề COLUMNS_UPDATED

Tác dụng: Cho phép kiểm tra cùng một lúc nhiều trường. Hàm trả về một giá trị dạng nhị phân có ý nghĩa chỉ ra những trường nào được kiểm tra khi thêm hoặc cập nhật.

Khi làm việc với mệnh đề COLUMNS_UPDATED, ta phải biết được các trường có thứ tự như thế nào trong bảng. Thứ tự đó được gọi là COLUMN_ID. Để biết được thứ tự của các trường trong một bảng, ta dùng cú pháp sau:

Cú pháp:

SELECT TABLE_NAME, COLUMN_NAME, COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME)

,COLUMN_NAME,'ColumnID') AS COLUMN_ID

FROM <DATABASE_NAME>.INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = <Table_Name_IN_DB> Trong đó:

- DATABASE_NAME: là tên cơ sở dữ liệu chứa bảng cần xem

- Table_Name_IN_DB: là tên bảng dữ liệu cần xem. Ví dụ 1: Xem thứ tự các trường trong bảng ngành SELECT TABLE_NAME, COLUMN_NAME,


COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME)

,COLUMN_NAME, 'ColumnID') AS COLUMN_ID FROM qlmbh.INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'Muaban'

Kết quả thứ tự các trường của bảng Mua bán:

Hình 5 4 Thứ tự các trường trong bảng Muaban Ví dụ 2 Xem thứ tự các trường 3

Hình 5.4. Thứ tự các trường trong bảng Muaban

Ví dụ 2: Xem thứ tự các trường trong bảng thí sinh. SELECT TABLE_NAME, COLUMN_NAME,

COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME)

,COLUMN_NAME, 'ColumnID') AS COLUMN_ID FROM qlts.INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'Thisinh'

Kết quả thứ tự các trường của bảng thí sinh:

Hình 5 5 Thứ tự các trường trong bảng Thisinh Trường hợp 1 Bảng không có quá 4

Hình 5.5. Thứ tự các trường trong bảng Thisinh Trường hợp 1: Bảng không có quá 8 trường

Để chỉ định những trường nào cần kiểm tra, ta sử dụng một bit mặt nạ (mask) tượng trưng cho vị trí mỗi trường trong table.

Ta có bảng biểu diễn 8 trường đầu tiên của table và bit mặt nạ được gán cho mỗi trường.

Trường

1

2

3

4

5

6

7

8

Bít mặt nạ

1

2

4

8

16

32

64

128


Từ bảng trên, ta có công thức tính bít mặt nạ cho từng trường:

POWER(2,(i-1))=2i-1

Trong đó i là trường thứ i trong bảng dữ liệu.

Để kiểm tra các trường có được cập nhật không thì thông qua một giá trị và giá trị này được tính như sau:

U=a0*20+a1*21+a2*22+a3*23+a4*24+a5*25+a6*26+a7*27

Trong đó,

- U là giá trị tính được

- ai =1 nếu trường thứ i+1 cần kiểm tra, ai=0 nếu trường thứ i+1 không cần kiểm tra.

- Để kiểm tra trường số 2 có được cập nhật hay không thì U=0*20+1*21+0*22+0*23+0*24+0*25+0*26+0*27=2

- Để kiểm tra trường số 5,6 có được cập nhật hay không thì U=0*20+0*21+0*22+0*23+1*24+1*25+0*26+0*27=48

Cú pháp:

- COLUMNS_UPDATED()>0: Tìm thấy trường được cập nhật

- (COLUMNS_UPDATED() & U)=U hoặc

(COLUMNS_UPDATED() & U) > 0: Tìm thấy tất cả các trường được cập nhật.

- (COLUMNS_UPDATED() | U)!=U: Tìm thấy bất kỳ trường khác không chỉ định được cập nhật.

Ví dụ 1:

- Để kiểm tra một trong các trường trong bảng mua bán có được cập nhật hay không thì ta dùng đoạn lệnh:

If COLUMNS_UPDATED()>0

- Để kiểm tra tất cả các trường số lượng, đơn giá trong bảng mua bán có được cập nhật hay không thì ta dùng đoạn lệnh:

If COLUMNS_UPDATED() & 48)=48, hoặc If COLUMNS_UPDATED() & 48)>0

- Để kiểm tra chỉ cho phép cập nhật trường số lượng, đơn giá trong bảng mua bán thì ta dùng đoạn lệnh.

If (COLUMNS_UPDATED() | 48)!=48

Ví dụ 2: Tạo Trigger cho bảng Mua bán kiểm tra trường số lượng và đơn giá có cùng được cập nhật hay không.

Create TRIGGER Trigger_ColumnUpdate1 ON muaban

AFTER UPDATE AS

..... Xem trang tiếp theo?
⇦ Trang trước - Trang tiếp theo ⇨

Ngày đăng: 16/07/2022