Bài giảng Thủ tục lưu trữ - hàm - trigger

THỦ TỤC LƯU TRỮ - HÀM - TRIGGER

1

BIếN CụC Bộ

biến cục bộ.

 Biến là một đối tượng có thể chứa dữ liệu  Dữ liệu có thể đưa vào các câu lệnh SQL dùng cục bộ  Tên các biến cục bộ phải bắt đầu bằng @  Từ khóa SET hay SELECT được dùng để gán giá trị cho

2

BIếN CụC Bộ (TT)

khi khai báo biến

 DECLARE @Tên_biến Kiểu_dữ_liệu [, ...]  Kiểu dữ liệu text, ntext hoặc image không được chấp nhận

DECLARE @Tongsldat INT, @Hotenncc CHAR(50) DECLARE @Ngayxh DATETIME

 Ví dụ: Để khai báo các biến lưu trữ giá trị tổng số lượng đặt hàng, họ tên nhà cung cấp, ngày xuất hàng. Sử dụng lệnh DECLARE như sau:

3

BIếN CụC Bộ (TT) – GÁN GIÁ TRị CHO BIếN

 Từ khóa SET hay SELECT được dùng để gán giá trị cho

biến.

Hoặc là:

SELECT @ =  Chú ý: Phạm vi hoạt động của biến chỉ nằm trong một

 Cú pháp: SET @ =

thủ tục hoặc một lô có chứa lệnh khai báo biến đó

4

BIếN CụC Bộ (TT) – GÁN GIÁ TRị CHO BIếN

 Ví dụ:

DECLARE @MaxSldat INT SELECT @MaxSldat=MAX(SLDAT) FROM CTDONDH  WHERE MAVTU=‘DD01’

 Để tính ra số lượng đặt hàng cao nhất của mặt hàng “Đầu DVD Hitachi 1 đĩa” có mã vật tư là “DD01”. Sử dụng lệnh SELECT như sau:

5

BIếN CụC Bộ (TT) – XEM GIÁ TRị HIệN HÀNH CủA BIếN

 PRINT @Tên_biến | Biểu_thức_chuỗi  Để tính đồng thời giá trị số lượng đặt hàng thấp nhất và cao nhất, hiển thị kết quả ra màn hình. Ta sử dụng lệnh SELECT và PRINT :

SELECT@MinSldat=MIN(SLDAT), @MaXSldat=MAX(SLDAT) FROM CTDONDH PRINT "Số lượng thấp nhất là : " PRINT @MinSldat PRINT "Số lượng cao nhất là : " +

 DECLARE @MinSldat INT, @MaxSldat INT

CONVERT(VARCHAR(10), @MaxSldat)

6

THủ TụC LƯU TRữ

 Tập hợp biên dịch các câu lệnh T-SQL được lưu trữ với một

tên xác định

 Sử dụng để thực hiện các nhiệm vụ quản trị, hoặc áp dụng

các luật giao dịch phức tạp  Có hai loại thủ tục lưu trữ:

 Thủ tục lưu trữ hệ thống đề cập đến phương pháp quản trị dữ liệu và cập nhật thông tin vào các bảng (thường bắt đầu bằng sp_).

 Thủ tục lưu trữ do người dùng định nghĩa.

7

THủ TụC LƯU TRữ - LợI ÍCH

 Tăng tốc độ thực hiện:

 Thực thi tại server, biên dịch một lần  Tốc độ truy nhập dữ liệu nhanh hơn:

truy suất csdl mỗi khi chúng được biên dịch

 SQl không phải lựa chọn cách tốt nhất để xử lý các lệnh SQL và

 Modular programming:

 Một thủ tục có thể phân thành các thủ tục nhỏ hơn, các thủ tục này có thể được dùng chung giữa các thủ tục khác->giảm thời gian thiết kế và thực thi các thủ tục đồng thời cũng dễ quản lý và gỡ rối.  Sự nhất quán.  Cải thiện sự bảo mật:

thủ tục vì vậy nó thực hiện đúng tác vụ người dùng.

 Nâng cao an toàn bảo mật. Có thể chỉ ra quyền thực thi cho các

8

ĐịNH NGHĨA THủ TụC LƯU TRữ BằNG EM

9

ĐịNH NGHĨA THủ TụC LƯU TRữ BằNG EM (TT)

Tên thủ tục

Nội dung thủ tục (thân thủ tục)

10

TạO THủ TụC LƯU TRữ BằNG T-SQL

 Tạo thủ tục lưu trữ trong csdl hiện thời bằng Cú pháp:

CREATE PROC[EDURE] [() ] [WITH

ECOMPILE| ENCRYPTION| RECOMPILE,

ENCRYPTION]

AS

[DECLARE ]  Các thủ tục lưu trữ có quyền truy cập tới tất cả các đối

tượng khi thủ tục được gọi.

 2100 tham số có thể được sử dụng trong một thủ tục lưu trữ. Tham số bắt đầu bởi @, cần chỉ ra kiểu dliệu của tham số

 Có thể tạo lập nhiều biến cục bộ trong thủ tục  Dung lượng tối đa của thủ tục lưu trữ là 128 MB.

11

TạO THủ TụC LƯU TRữ BằNG T-SQL (TT)

 Ví dụ: Cho CSDL quản lý điểm thi như sau:

12

TạO THủ TụC LƯU TRữ BằNG T-SQL (TT)

dữ liệu  1. Bổ sung thêm môn học cơ sở dữ liệu có mã CST005 và số

đơn vị học trình là 5 vào bảng MONHOC

 Giả sử ta cần thực hiện một chuỗi các thao tác trên cơ sở

 2. Lên danh sách nhập điểm thi môn cơ sở dữ liệu cho các sinh viên học lớp có mã CDT002 (bổ sung thêm vào bảng DIEMTHI các bản ghi với cột MAMONHOC nhận giá trị CST005, cột MASV nhận giá trị lần lượt là mã các sinh viên học lớp có mã CDT002 và các cột điểm là NULL).

13

TạO THủ TụC LƯU TRữ BằNG T-SQL (TT)

 Theo cách thông thường ta sẽ viết 2 lệnh như sau: INSERT INTO monhoc

VALUES(‘CST005’, N‘Cơ sở dữ liệu’,5)

Đây là mã môn học, đã cho trước nên ghi cụ thể ra luôn

SELECT ‘CST005’, masv

 INSERT INTO diemthi(mamh,masv) FROM sinhvien WHERE malop = ‘CDT002’

 Lưu ý: ở đây bạn có cú pháp câu lệnh chèn dữ liệu vào một bảng có tên banga với dữ liệu lấy từ bảng có tên bangb khác như sau: INSERT INTO banga (cot1, cot2) SELECT cot1, cot2 FROM bangb

14

TạO THủ TụC LƯU TRữ BằNG T-SQL (TT)

một thủ tục với các tham số sau @mamh, @tenmh, @sodvht, @malop để nhập dữ liệu cho một môn học bất kỳ và một lớp bất kỳ do người dùng nhập vào khi sử dụng thủ tục.

 Thay vì phải viết 2 câu lệnh như trên, ta có thể định nghĩa

15

TạO THủ TụC LƯU TRữ BằNG T-SQL (TT)

CREATE PROC sp_LenDanhSachDiem( @mamh NVARCHAR(10), @tenmh NVARCHAR(50),

@sodvht

SMALLINT,

@malop CHAR(4) AS BEGIN INSERT INTO monhoc

VALUES(@mamh,@tenmh,@sodvht)

INSERT INTO diemthi(mamh,masv) SELECT @mamh,masv FROM sinhvien WHERE malop=@malop END  Khi thủ tục trên đã được tạo ra, thực hiện được hai yêu cầu trên qua lời

16

gọi thủ tục:  sp_LenDanhSachDiem ‘CST005','Cơ sở dữ liệu',5,‘L002'

THựC THI CÁC THủ TụC NGƯờI DÙNG

 Lời gọi thủ tục có dạng:

[]

 Số lượng các đối số và thứ tự của chúng phải phù hợp với số

lượng và thứ tự của các tham số hình thức.

 Nếu lời gọi thủ tục được thực hiện bên trong một thủ tục khác, bên trong một trigger hay kết hợp với các câu lệnh SQL khác, ta sử dụng cú pháp như sau:

EXEC[UTE] []

 vdụ:  EXECUTE sp_LenDanhSachDiem ‘CST005','Cơ sở dữ liệu',5,‘L002'

 Thứ tự của các đối số được truyền cho thủ tục có thể không cần phải tuân theo thứ tự của các tham số như khi định nghĩa thủ tục nếu tất cả các đối số được viết dưới dạng: @ =

17

VD Sử DụNG BIếN TRONG THủ TụC:

CREATE

PROC

sp_Vidu(@malop1

CHAR(4),@malop2

CHAR(4))

AS DECLARE @tenlop1 NVARCHAR(30) DECLARE @namnhaphoc1 INT DECLARE @tenlop2 NVARCHAR(30) DECLARE @namnhaphoc2 INT SELECT @tenlop1=tenlop,@namnhaphoc1=namnhaphoc FROM lop WHERE malop=@malop1 SELECT @tenlop2=tenlop, @namnhaphoc2=namnhaphoc FROM lop WHERE malop=@malop2  PRINT @tenlop1+' nhap hoc nam '+str(@namnhaphoc1) print @tenlop2+' nhap hoc nam '+str(@namnhaphoc2)   IF @namnhaphoc1=@namnhaphoc2 PRINT 'Hai lớp nhập học cùng năm' ELSE PRINT 'Hai lớp nhập học khác năm'

18

GIÁ TRị TRả Về CủA THAM Số TRONG THủ TụC

 Trường hợp cần giữ lại giá trị của đối số sau khi kết thúc thủ tục,

khai báo tham số của thủ tục theo cú pháp:

@tên_tham_số kiểu_dữ_liệu OUTPUT

 Hoặc: @tên_tham_số kiểu_dữ_liệu OUT  Trong lời gọi thủ tục, sau đối số được truyền cho thủ tục, cũng

phải chỉ định thêm từ khoá OUTPUT (hoặc OUT)

INT, INT, INT OUTPUT)

CREATE PROCEDURE sp_Conghaiso( @a @b @c AS SELECT @c=@a+@b

19

GIÁ TRị TRả Về CủA THAM Số TRONG THủ TụC (TT)



Thực hiện lời gọi thủ tục trong một tập các câu lệnh như sau: DECLARE @tong INT SELECT @tong=0 EXECUTE sp_Conghaiso 100,200,@tong OUTPUT SELECT @tong => câu lệnh “SELECT @tong” sẽ cho kết quả là: 300



20

THủ TụC LƯU TRữ - THAM Số GIÁ TRị MặC ĐịNH

 Tham số với giá trị mặc định được khai báo theo cú pháp

như sau: @ =

21

THủ TụC LƯU TRữ - THAM Số GIÁ TRị MặC ĐịNH

CREATE PROC sp_TestDefault( AS

BEGIN

@tenlop NVARCHAR(30)=NULL, @noisinh NVARCHAR(100)='Huế') IF @tenlop IS NULL

SELECT hodem,ten FROM sinhvien INNER JOIN lop ON sinhvien.malop=lop.malop WHERE noisinh=@noisinh

ELSE

SELECT hodem,ten FROM sinhvien INNER JOIN lop

ON sinhvien.malop=lop.malop

WHERE noisinh=@noisinh AND

tenlop=@tenlop

END

22

THủ TụC LƯU TRữ - THAM Số GIÁ TRị MặC ĐịNH (TT)

 Cho biết họ tên của các sinh viên sinh tại Huế:

 sp_testdefault

  Cho biết họ tên của các sinh viên lớp Tin K24 sinh tại Huế:

 sp_testdefault @tenlop='Tin K24‘

 Cho biết họ tên của các sinh viên sinh tại Nghệ An:

 sp_testDefault @noisinh=N'Nghệ An‘

Nẵng:  sp_testdefault @tenlop='Tin K26',@noisinh='Đà Nẵng'

 Cho biết họ tên của các sinh viên lớp Tin K26 sinh tại Đà

23

THủ TụC LƯU TRữ - BIÊN DịCH LạI THủ TụC

đổi tới các chỉ số

 Các thủ tục lưu trữ được biên dịch lại để phản ánh sự thay

 Có ba cách để biên dịch lại các thủ tục:  Sử dụng thủ tục hệ thống sp_recompile  Chỉ rõ WITH RECOMPILE với lệnh CREATE PROCEDURE  Chỉ rõ WITH RECOMPILE với lệnh EXECUTE

24

THủ TụC LƯU TRữ - THAY ĐổI THủ TụC LƯU TRữ

thủ tục lưu trữ

 Câu lệnh ALTER PROCEDURE được sử dụng để sửa một

 Cú pháp giống như lệnh CREATE PROCEDURE  Sự thay đổi này vẫn giữ lại các quyền người dùng  Cp:

RECOMPILE|ENCRYPTION|

[WITH RECOMPILE,ENCRYPTION] AS

 ALTER PROCEDURE tên_thủ_tục [(danh_sách_tham_số)]

25

THủ TụC LƯU TRữ - XÓA THủ TụC

sau: 

 Sử dụng câu lệnh DROP PROCEDURE với cú pháp như

DROP PROCEDURE

26

THủ TụC LƯU TRữ - TổNG KếT

biên dịch lại.

 Một thủ tục lưu trữ là một nhóm các câu lệnh SQL được

 Người phát triển CSDL hoặc người quản trị hệ thống viết thủ tục để chạy các nhiệm vụ quản trị thông thường, hoặc để ứng dụng các luật giao dịch phức tạp. Thủ tục lưu trữ chứa các thao tác hoặc các câu lệnh truy vấn dữ liệu.

 Các thủ tục lưu trữ tăng tốc độ thực thi của truy vấn, hỗ trợ truy cập dữ liệu nhanh, hỗ trợ việc lập trình theo mô đun, duy trì tính nhất quán, và tăng tính bảo mật.

27

THủ TụC LƯU TRữ - TổNG KếT (TT)

 Sử dụng thủ tục hệ thống sp_recompile  Chỉ rõ WITH RECOMPILE với lệnh CREATE PROCEDURE  Chỉ rõ WITH RECOMPILE với lện EXECUTE

 Có ba cách để biên dịch lại các thủ tục lưu trữ:

một thủ tục lưu trữ.

 Câu lệnh ALTER PROCEDURE được sử dụng để sửa chữa

28

HÀM

 Hàm là đối tượng cơ sở dữ liệu tương tự như thủ tục.  Điểm khác biệt giữa hàm và thủ tục:

thức (chẳng hạn, trong dsách chọn của lệnh SELECT).

 Hàm trả về một giá trị thông qua tên hàm còn thủ tục thì không.  Có thể sử dụng hàm như là một thành phần của một biêu

cho mục đích riêng của mình

 Hàm do HQT CSDL cung cấp sẵn  Người sử dụng có thể định nghĩa các hàm nhằm phục vụ

29

HÀM

 Hàm gồm 3 loại:

 Các hàm thao tác tập hợp  Hàm thao tác bản ghi  Hàm vô hướng

30

HÀM

 Các hàm thao tác với tập bản ghi có thể được dùng thay

cho tên các bảng trong SQL.

 Các hàm tập hợp tính toán cho ra kết quả là một giá trị

đơn nhất (ví dụ tính tổng hay trung bình).

 Các hàm vô hướng thao tác trên một giá trị và trả về một giá trị. Các hàm này có thể được dùng trong các biểu thức.

31

HÀM – ĐịNH NGHĨA HÀM

RETURNS (kiểu_trả_về_của_hàm) AS

BEGIN

các_câu_lệnh_của_hàm

END

 CREATE FUNCTION tên_hàm ([danh_sách_tham_số])

32

HÀM – ĐịNH NGHĨA HÀM (TT)

thu(@ngay DATETIME) RETURNS

CREATE FUNCTION NVARCHAR(10)

AS BEGIN DECLARE @st NVARCHAR(10) SELECT @st=CASE DATEPART(DW,@ngay) WHEN 1 THEN 'Chu nhật' WHEN 2 THEN 'Thứ hai' WHEN 3 THEN 'Thứ ba' WHEN 4 THEN 'Thứ tư' WHEN 5 THEN 'Thứ năm' WHEN 6 THEN 'Thứ sáu' ELSE 'Thứ bảy' END RETURN (@st) /* Trị trả về của hàm */ END

33

HÀM – Sử DụNG HÀM

masv,

hodem,

ten,

 SELECT

dbo.thu(ngaysinh), ngaysinh FROM sinhvien WHERE malop=’C24102’

34

HÀM – HÀM VớI GIÁ TRị TRả Về LÀ Dữ LIệU KIểU BảNG

FUNCTION

tên_hàm

([danh_sách_tham_số]) RETURNS TABLE AS RETURN (câu_lệnh_select)

 CREATE

 Qui tắc:

TABLE.

 Kiểu trả về của hàm được chỉ định bởi mệnh đề RETURNS

 Trong phần thân của hàm chỉ có duy nhất một câu lệnh RETURN xác định giá trị trả về của hàm thông qua duy nhất một câu lệnh SELECT (không sử dụng bất kỳ câu lệnh nào khác trong phần thân của hàm).

35

HÀM – HÀM VớI GIÁ TRị TRả Về LÀ Dữ LIệU KIểU BảNG (TT)

CREATE FUNCTION func_XemSV(@khoa SMALLINT) RETURNS TABLE AS

RETURN(SELECT masv,hodem,ten,ngaysinh FROM sinhvien INNER JOIN lop

ON sinhvien.malop=lop.malop WHERE khoa=@khoa)

 Vd: định nghĩa hàm xemsv

36

HÀM – HÀM VớI GIÁ TRị TRả Về LÀ Dữ LIệU KIểU BảNG (TT)

dụng cú pháp sau:

 Khi cần sử dụng nhiều câu lệnh trong phần thân hàm, sử

RETURNS @ TABLE <định_nghĩa_bảng> AS BEGIN

RETURN

END

 CREATE FUNCTION ([])

37

HÀM – HÀM VớI GIÁ TRị TRả Về LÀ Dữ LIệU KIểU BảNG (TT)

 Cấu trúc bảng trả về bởi hàm được xác định dựa vào định nghĩa của bảng trong mệnh đề RETURNS.  Biến @ trong mệnh đề RETURNS có phạm vi sử dụng trong hàm và được sử dụng như một tên bảng.

 Câu lệnh RETURN trong thân hàm không chỉ định giá trị trả về. Giá trị trả về của hàm chính là các dòng dữ liệu trong bảng có tên là @ được định nghĩa trong mệnh đề RETURNS

 Lưu ý:

38

HÀM – HÀM VớI GIÁ TRị TRả Về LÀ Dữ LIệU KIểU BảNG (TT) – VÍ Dụ

CREATE

FUNCTION

Func_Tongsv(@khoa

SMALLINT) RETURNS

@bangthongke TABLE (

makhoa

NVARCHAR(5),

tenkhoa

NVARCHAR(50),

tongsosv

INT

) AS

BEGIN

IF @khoa=0

INSERT INTO @bangthongke

SELECT khoa.makhoa,tenkhoa,COUNT(masv)

FROM (khoa INNER JOIN lop

ON khoa.makhoa=lop.makhoa) INNER JOIN sinhvien

ON lop.malop=sinhvien.malop

GROUP BY khoa.makhoa,tenkhoa

ELSE

39

HÀM – HÀM VớI GIÁ TRị TRả Về LÀ Dữ LIệU KIểU BảNG (TT)

INSERT INTO @bangthongke

SELECT khoa.makhoa,tenkhoa,COUNT(masv)

FROM (khoa INNER JOIN lop

ON khoa.makhoa=lop.makhoa)

INNER JOIN sinhvien

ON lop.malop=sinhvien.malop

WHERE khoa=@khoa

GROUP BY khoa.makhoa,tenkhoa

RETURN /*Trả kết quả về cho hàm*/

END

40

HÀM – HÀM VớI GIÁ TRị TRả Về LÀ Dữ LIệU KIểU BảNG (TT)

SELECT * FROM dbo.func_TongSV(25)  Sẽ cho kết quả thống kê tổng số sinh viên khoá 25 của mỗi khoa:

 Câu lệnh:

SELECT * FROM dbo.func_TongSV(0)  Cho ta biết tổng số sinh viên hiện có (tất cả các khoá) của mỗi

khoa

 Còn câu lệnh:

41

TRIGGER – TRIGGER LÀ GÌ?

 Cấu trúc gần giống như một thủ tục nội tại nhưng

 Không có tham số đầu vào và đầu ra  Phải được liên kết với một bảng/ bảng ảo trong CSDL

việc:  Tính toán, cập nhật giá trị tự động  Kiểm tra dữ liệu nhập

 Không thể gọi mà được thực hiện tự động. Sử dụng trong

 Khai báo sử dụng

bảng hay bảng ảo

 Kết hợp với các hành động INSERT/UPDATE/DELETE trên

nhật dữ liệu tương ứng

 Khi tạo ra, tham gia vào transaction khởi tạo bởi câu lệnh cập

42

CÁC Xử LÝ BÊN TRONG TRIGGER

 Kiểm tra các ràng buộc dữ liệu phức tạp

phạm ràng buộc

 Các ràng buộc mô tả phức tạp, không thể dùng constraint  Gọi hành động Rollback Tran để hủy thao tác cập nhật khi vi

 Bảo đảm dữ liệu luôn được toàn vẹn  Bảo đảm việc kiểm thử ứng dụng không làm hư dữ liệu có sẵn

 Tính toán, tự động cập nhật giá trị

vẹn dữ liệu

 Bổ sung các hành động cập nhật dữ liệu để đảm bảo tính toàn

 Đơn giản hoá việc xây dựng ứng dụng

 Chỉ định các bẫy lỗi dễ hiểu

 Tăng tính thân thiện của ứng dụng  Dễ dàng nhận ra các lỗi khi lập trình

43

CÁC HạN CHế TRÊN TRIGGER

trong CSDL  CREATE/ALTER/DROP

 Không được tạo và tham chiếu bảng tạm  Không tạo hay thay đổi, xoá cấu trúc các đối tượng sẵn có

 Không gán, cấp quyền cho người dùng

 GRAND/REVOKE

44

CÁC LOạI TRIGGER

 SQL Server 2000 có hai loại trigger

 Chạy sau các hành động kiểm tra dữ liệu của các Rule, Constraint  Dữ liệu đã bị tạm thời thay đổi trong bảng

 Trigger thông thường: AFTER (FOR) trigger

 Chạy trước các hành động kiểm tra dữ liệu  Dữ liệu chưa hề bị thay đổi  Có thể thay thế hành động cập nhật dữ liệu bằng các hành động khác

 INSTEAD OF trigger

45

CÁC BảNG TRUNG GIAN INSERTED VÀ DELETED

 Inserted

liệu được

thêm mới

trong hành động

INSERT/UPDATE  Có ở cả hai loại trigger  Cấu trúc bảng giống với bảng thực sự được cập nhật dữ liệu

 Chứa dữ

 Deleted

 Chứa dữ liệu bị xoá trong hành động DELETE/UPDATE  Có ở cả hai loại trigger  Cấu trúc bảng giống với bảng thực sự được cập nhật dữ liệu

 Hành động update trong SQL Server

 Xoá dòng dữ liệu cũ  Thêm vào dòng dữ liệu mới với thông tin đã cập nhật

46

LÀM VIệC VớI TRIGGER

47

TạO MớI TRIGGER

hoặc Query Analyzer.

 Trigger có thể được tạo bằng công cụ Enterprise Manager,

được sử dụng để tạo ra trigger.

CREATE TRIGGER Tên_Trigger ON Tên_bảng

{ [ INSTEAD OF ] | [ FOR | AFTER ] }

{ [ INSERT [, UPDATE [,DELETE ] ] ] }

AS

[DECLARE Biến_cục_bộ]

Các_lệnh

 Trong cả hai trường hợp, câu lệnh CREATE TRIGGER

48

MÔ Tả

 Tên bảng

 Tên bảng mà trigger tạo mới sẽ liên kết

động cập nhật  FOR hoặc AFTER

 INSTEAD OF: chỉ định đây là trigger loại instead of trigger  Mỗi bảng chỉ có quyền tạo một instead of trigger cho một hành

 Nếu tạo trigger thông thường  INSERT, UPDATE, DELETE

trigger.

 Hành động cập nhật dữ liệu tác động vào bảng để kích hoạt

49

XÓA TRIGGER

DROP TRIGGER Tên_trigger

 Cú pháp

50

SửA NộI DUNG TRIGGER

ALTER TRIGGER Tên_Trigger ON Tên_bảng

FOR INSERT [, UPDATE [,DELETE ]]

AS

[DECLARE Biến_cục_bộ]

Các_lệnh

 Sửa nội dung

51

TRIGGER LồNG NHAU

 Trigger có thể lồng nhau

Trigger trên bảng tương ứng

 Hành động cập nhật  Trigger  Cập nhật bảng khác 

kết  Cập nhật  Instead of Trigger  Gọi câu lệnh cập nhật xuống bảng 

Instead of trigger  Số cấp lồng tối đa

 Instead Of trigger không phát sinh lại trên chính bảng mà nó liên

 32 cấp  Sử dụng biến @@NestedLevel

 EXEC sp_configure 'nested triggers', [0 | 1]

 Cấu hình cho phép trigger lồng nhau

52

TRIGGER KIểM TRA RÀNG BUộC Dữ LIệU

53

KHI THÊM MớI MẫU TIN

 Thường dùng để kiểm tra

 Khóa ngoại, Miền giá trị, Liên thuộc tính trong cùng một bảng  Liên thuộc tính của nhiều bảng khác nhau

lỗi cụ thể bằng tiếng Việt  Nếu đã khai báo các ràng buộc này bằng constraint

 3 loại đầu tiên, chỉ dùng trigger nếu muốn cung cấp các báo

 Các cấu trúc lệnh thường dùng khi kiểm tra

 If Else  If Exists  Raiserror  Rollback Tran

54

KHI HủY Bỏ MẫU TIN

 Tương tự, kiểm tra các ràng buộc như trigger INSERT  Nên kiểm tra ràng buộc khoá ngoại

dữ liệu trên bảng khác

 Thông thường ràng buộc này dẫn đến việc phải cập nhật một số

 Chú ý: SQL Server 2000 có thuộc tính CASCADE DELETE

55

KHI SửA ĐổI MẫU TIN

 Tương tự, kiểm tra các ràng buộc như trigger INSERT

thực hiện tự động

 Ràng buộc khoá ngoại có thể sử dụng CASCADE UPDATE để

If Update(Tên_cột)

Xử lý

 Xác định cột đang được cập nhật

56

TRIGGER CậP NHậT GIÁ TRị Tự ĐộNG

 Sau khi kiểm tra ràng buộc trigger có thể

bảo toàn vẹn dữ liệu: Cập nhật giá trị tự động

 Rollback nếu dữ liệu không hợp lệ  Thực hiện tiếp các hành động cập nhật trên bảng khác để đảm

 Vd: Insert  CTGiaoHang  Cập nhật bảng TONKHO

 Các hành động cập nhật thường thực hiện  Hủy bỏ dữ liệu do quan hệ khoá ngoại  Tính lại các cột 'tính toán' trong các bảng liên quan

 Vị trí thực hiện

 Trong cùng trigger kiểm tra ràng buộc đã định nghĩa  Sau khi kiểm tra dữ liệu đã hợp lệ (thoả mãn các ràng buộc)

57

INSTEAD OF TRIGGER

giới hạn  Group By, Order By, Distinct  Ràng buộc khoá ngoại  Thiếu các cột NOT NULL trong bảng

 Bảng ảo thông thường có thể được cập nhật nhưng có nhiều

 Trigger Instead of

hợp trên bảng gốc

 Xảy ra trước khi SQL Server kiểm tra ràng buộc  Thay đổi hành động cập nhật vào bảng ảo bằng hành động thích

58

VÍ Dụ

Select D.SoDH, NgayDH, MaNhaCC, V.MaVTu, TenVTu, SoLuong, DonGia From CTDONDH CT, DONDH D, VATTU V Where CT.SoDH = D.SoDH And CT.MaVTu = V.MaVTu

CREATE

tg_vw_CTDONDH_BI

TRIGGER INSTEAD OF INSERT ON vw_CTDONDH

AS -- Nếu chưa có đơn đặt hàng, thêm đơn đặt hàng vào DONDH Insert Into DONDH Select SoDH, NgayDH, MaNhaCC Where SoDH Not In (Select SoDH From Inserted From DonDH) -- Nếu chưa có vật tư, thêm vật tư vào bảng VATTU Insert Into VATTU(MaVTu, TenVTu) Select MaVTu, TenVTu From Inserted Where MaVTu Not In (Select MaVTu From VATTU) -- Thêm các chi tiết đặt hàng vào CTDONDH Insert Into CTDONDH Select SoDH, MaVTu, SoLuong, DonGia From Inserted

 Tạo bảng ảo sau

59