ả
H Qu n Tr C S D ị ơ ở ữ ệ Li uệ
TRIGGER & CURSOR
N i dung
ộ
I.Trigger
ộ ữ ệ đ ng I.1. Khái quát v trigger ề I.2.Làm vi c v i trigger ệ ớ I.3.Trigger ki m tra ràng bu c d li u ể I.4.Trigger c p nh t giá tr t ậ ậ ị ự ộ
II.Cursor
II.1.Khái ni m v CurSor : ệ ề
II.2Th t các thao tác khi x lý d li u trên CurSor : ứ ự ữ ệ ử
I.Trigger
ề
I.1 Khái quát v trigger I.1.1Trigger là gì?
ố ấ ủ ụ ộ ạ
ư c liên k t v i
ư ộ ầ
i nh ng không ế ớ ầ ố ầ
- C u trúc g n gi ng nh m t th t c n i t có tham s đ u vào và đ u ra và ph i đ ả ượ m t b ng ho c b ng o trong CSDL. ộ ả
ặ ả - Không th g i mà đ đ ng và đ ự
c s ượ ử đ ng và c th c hi n t ệ ự ộ ậ ậ ị ự ộ
ậ
ử ụ
− K t h p v i các hành đ ng INSERT/ UPDATE/ DELETE
ả ể ọ ượ d ng trong vi c : tính toán, c p nh t giá tr t ệ ụ ki m tra d li u nh p. ữ ệ ể Khai báo s d ng : ộ ớ trên b ng hay b ng o. ả ả
ở ạ ở ệ ế ợ ả ạ
- Khi t o ra, tham gia vào transaction kh it ob i câu l nh ng ng. ậ ữ ệ ươ ứ c p nh t d li u t ậ
I.1.2.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 c t p không th dùng ộ ể ả ứ ạ
ộ ể ủ ậ ậ
ộ
c toàn v n.
ữ ệ ệ ạ ả ả ẹ ụ ử ứ ể
+ Các ràng bu c mô t constraint. + G i ràng bu c Rollback Tran đ h y thao tác c p nh t ọ khi vi ph m ràng bu c. + B o đ m d li u luôn đ ượ ả + B o đ m vi c ki m tra th ng d ng không làm h ư ả d li u có s n. ữ ệ ẵ
ị ậ ự ộ
- Tính toán, t ổ ậ ữ ệ ể ả ậ
ả ẹ
ụ ứ ả
- Ch đ nh các b y l ơ ị ỉ
ụ
+ Tăng tính thân thi n c a ng d ng. + D dàng nh n ra các l đ ng c p nh t giá tr : ậ + B sung các hành đ ng c p nh t d li u đ đ m ộ b o tính toàn v n d li u. ữ ệ + Đ n gi n hóa vi c xây d ng ng d ng. ệ ự i d hi u: ẫ ỗ ễ ể ủ ứ ệ ỗ i khi l p trình. ậ ễ ậ
I.1.3. Các h n ch trên trigger : ế ạ
c t o và tham chi u b ng t m. ạ
ế ả ấ ố ượ ng s n ẵ
- Không đ ượ ạ - Không t o hay thay đ i, xóa c u trúc các đ i t ổ ạ có trong CSDL.
i dùng ề ườ
- CREATE/ ALTER/ DROP - Không gán, c p quy n cho ng ấ - GRAND/ REVOKE
I.1.4. Các lo i trigger : ạ
Có hai lo i :ạ
- AFTER(FOR)trigger : là lo i ng m đ nh, lo i trigger này ạ ầ ạ ị
ệ ệ ự ệ
ạ
ạ ộ ủ
s th c hi n các l nh bên trong sau khi đã th c hi n xong ẽ ự s ki n kích ho t trigger. ự ệ INSTEAD OF trigger : là lo i trigger mà ho t đ ng c a ạ s ki n g i trigger s b b qua và thay vào đó là các l nh ệ ẽ ị ỏ ự ệ ọ c th c hi n. trong trigger đ ệ ự ượ
Khi trigger th c thi theo các s ki n Insert ho c
I.1.5 Các b ng trung gian Inserted và Deleted ả
ự ữ ệ
Các b ng này ch đ
ự ệ ặ c chuy n sang Delete thì d li u khi thay đ i s đ ể ổ ẽ ượ các b ng Inserted và Deleted, là 2 b ng t m th i. ờ ả c s d ng v i các l nh trong ả ả
i ph n d li u đã ớ ầ ạ ệ ữ ệ
ử ụ ỉ ượ trigger và dùng đ khôi ph c l ụ ạ ể thay đ i (roll back). ổ
c thêm m i trong hành đ ng ứ ữ ệ ượ ộ ớ
a. Inserted : - Ch a d li u đ Insert/Update ở ả c hai lo i trigger ạ
c c p nh t d ậ ữ ả ớ ả ự ự ượ ậ
ộ
ữ ệ ị c hai lo i trigger ạ
ự ự ượ ậ ớ ả ả c c p nh t d ậ ữ
- Có - C u trúc b ng gi ng v i b ng th c s đ ố ấ li uệ b. Deleted : - Ch a d li u b xóa trong hành đ ng Delete/Update ứ - Có ở ả - C u trúc b ng gi ng v i b ng th c s đ ố ấ li uệ
ữ ệ
c. Hành đ ng update trong SQL Server ộ - Xóa 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. ậ ớ ớ ữ ệ ậ
I.1.6.Khi nào c n dùng Trigger ? ầ
ể ố ớ
ư ể ộ ộ ề ộ
a. Ki m tra các ràng bu c d li u ph c t p. ứ ạ ộ ữ ệ - Đ i v i ràng bu c toàn v n d li u đ n gi n nh ki m ả ơ ẹ ữ ệ tra ràng bu c mi n giá tr ,ki m tra ràng bu c thu c ộ ị ể tính...thì nên dùng Constraint.
ộ ể
ặ
- Đ i v i các ràng bu c ph c t p: Vd: ki m tra tính toàn ứ ạ ố ớ v n trên nhi u c t ho c dòng thì dùng trigger. Nó cho ẹ phép ta xây d ng các câu l nh đ d li u thay đ i theo ý ệ mu n.ố
ề ộ ự ể ữ ệ ổ
b. Khi các bi n pháp ệ
ả ả ả ả ể ỏ
ố ể ữ
ậ
ự ệ
ẹ ữ ệ nh ư b o đ m toàn v n d li u b o đ m toàn v n d li u ẹ ữ ệ Constraints không th th a mãn yêu c u c a ng d ng. ụ ầ ủ ứ VD: mu n ki m tra ràng bu c kèm theo nh ng thông ộ báo... c khi Constraints s ki m tra d li u tr ướ ữ ệ ẽ ể c kích ho t Nh p vào Table, còn Trigger đ ạ ượ khi các s ki n Insert, Update, Delete x y ra ả r i.ồ
d ữd ữ c chu n hóa thì có th x y ra ặ ể ả ầ
ẩ trong CSDL. Yêu c u đ t ra là ọ ơ ậ ấ
ả ổ ổ
c. Khi CSDL ch a đ ư ượ nhi u v trí ừ , ch aứ li u th a ề ị ở ệ nhi u v trí li u th a ở ề ị ừ ệ d li u c n c p nh t th ng nh t trong m i n i. ố ữ ệ ầ ậ ề ữ ệ gi a các b ng thay đ i dây chuy n d li u thay đ i dây chuy n d li u d. Khi x y raả ữ ề ữ ệ v i nhau.D li u b ng này thay đ i kéo theo d li u trong ữ ệ ổ ớ c thay đ i theo. b ng khác cũng đ ổ ả ữ ệ ả ượ
I.2. Làm vi c v i trigger ệ ớ
I.2.1.T o m i Trigger. ớ ạ
Create Trigger Ten_Trigger On Ten_Bang {[Instead of] | [For | After] } { [Insert] ,[Update] , [Delete] } AS [Declare Bien_Cuc_Bo] Các L nh bên trong ệ
I.2.2. Xóa Trigger:
Drop Trigger Ten_Trigger
I.2.3. S a n i dung Trigger:
ử ộ
Alter Trigger Ten_Trigger On Ten_Bang For [Insert], [Update],[Delete] As [Declare bien_cuc_bo] . Các l nh bên trong
ệ
Trong đó:
- Ten_Bang: là tên b ng mà trigger t o m i s liên ớ ẽ ạ ả
k t.ế
ị ỉ ạ
ỉ ề ạ
ậ
ườ ặ
ng. ậ ữ ệ ậ ộ
tác đ ng vào b ng đ kích ho t Trigger. - Instead of: Ch đ nh đây là trigger lo i Instead of trigger. M i b ng ch có quy n t o 1 instead of trigger ỗ ả cho 1 hành đ ng c p nh t. ậ ộ - For ho c After: n u t o trigger thông th ế ạ - Insert, Update, Delete: Hành đ ng c p nh t d li u ạ ể ả ộ
ị ẽ
Imsert lúc này không có d li u. ữ ệ
c thêm s sao chép ẽ ượ
+ Khi Delete th c thi, d li u b xoá s sao chép vào ự ữ ệ Delete. B ngả Imsert b ng ả Delete + Khi Insert th c thi, d li u đ ữ ệ ự vào b n ả Inserted
ẽ ượ ả
ủ ộ Deleted, còn Deleted ượ ẽ c c p ậ Inserted s là các dòng sau khi đã đ Inserted
Inserted. Deleted lúc này không có d li u. B ng ả Deleted lúc này ữ ệ + Khi Update th c thi, d li u cũ ch u tác đ ng c a ị ữ ệ ự c sao chép vào b ng câu l nh s đ ệ trong b ng ả nh t. ậ
I.2.4. Trigger l ngnhau:
ồ
- Hành đ ng c pnh t -> Trigger -> C p nh t
ậ
ậ
ậ
ậ
ộ
b ng khác -> Trigger trên b ng t ả
ả
- Instead of trigger không phát sinh l
i trên chính
ng ng. ươ ứ ạ
b ng mà nó liên k t. ả
ế ậ
ộ
ệ
ậ
ả
ọ
ố
- Hành đ ng c p nh t ->Instead of trigger ậ ->g i câu l nh c p nh t xu ng b ng -> ậ Instead of trigger
II. CURSOR :
II.1. Khái ni m v Cursor : ệ ề
_ Các l nh c a SQL Server làm vi c trên m t nhóm nhi u m u tin. ệ ủ ệ ề ẫ ộ
ấ ể ẫ ộ
ố ượ
ng recordset trong VB i m t th i đi m. _ Cursor là c u trúc giúp làm vi c v i t ng m u tin t ờ ạ ệ ớ ừ CSDL, gi ng đ i ng dùng đ ch a d li u l y t Hay cursor là đ i t ố ố ể ứ ữ ệ ấ ừ t ượ
II.2 Th t các thao tác khi x lý d li u trên Cursor : ứ ự ữ ệ ử
II.2.1. Đ nh nghĩa bi n Cursor ế ị
II.2.2. M Cursor ở
II.2.3. Duy t và x lý d li u trên Cursor ữ ệ ử ệ
II.2.4. Đóng và gi i phóng Cursor ả
II.2.1. Đ nh nghĩa bi n Cursor b ng l nh DECLARE : ệ ế ằ ị
Cú pháp khai báo bi n Cursor : ế
Declare Tên_cursor Cursor
[Local/Global]
[Forward_only/Scroll]
[Static/Dynamic/Keyset]
[Read_only/Scroll_lock]
For câu l nh Select ệ
[For update [Of danh sách c t]]ộ
_ Có hai lo i Cursor : Local và Global ạ
LoCal :ch s d ng trong ph m vi khai báo(m c đ nh) ỉ ử ụ ặ ị ạ
Global :s d ng chung cho c k t n i ả ế ố ử ụ
_ Cách di chuy n m u tin trong Cursor: Forward only và Scroll ể ẫ
ng t c ra ể ỉ ộ ướ tr ừ ướ
ForWard_Only :ch di chuy n m t h sau(m cđ nh) ặ ị
Scroll : di chuy n tùy ý ể
_ Cách qu n lý d li u trong Cursor : Static, Dynamic và Keyset ữ ệ ả
ữ ệ ặ ổ
Static : d li u trên Cursor không thay đ i m c dù d li u trong b ng ngu n thay đ i(m c đ nh) ả ữ ệ ồ ặ ị ổ
ữ ệ ả ổ
Dynamic :d li u trên Cursor s thay đ i khi d li u trong b ng ẽ ngu n thay đ i ữ ệ ổ ồ
KeySet :gi ng ố Dynamic nh ng ch thay đ i nh ng dòng b c p ị ậ ữ ư ổ ỉ
nh tậ
* X lý :ử
Read_Only :ch đ c(m c đ nh) ặ ị ỉ ọ
Scroll_Lock : đ c/ghi ọ
*Câu l nh select : không ch a các m nh đ Into,Compute,Compute by ệ ứ ệ ề
*Danh sách c t c p nh t : c ộ ậ ậ là danh sách các c t s thay đ i đ ộ ẽ ổ ượ
c ể ở ử ụ ệ ị ướ
II.2.2. S d ng l nh OPEN đ m ra Cursor đã đ nh nghĩa tr đó:
Cú pháp: Open tên_Cursor
: II.2.3. Đ c và x lý trên t ng dòng d li u bên trong cursor ữ ệ ừ ử ọ
Cú pháp : Fetch [ Next / Prior / First / Last
/ Absolute n / Relative n ]
From Tên_Cursor
[Into Danh_sách_bi n]ế
Trong đó:
H ng di chuy n : ướ ể
Next : Di chuy n v sau ể ề
Prior: Di chuy n v tr c ể ề ướ
First : Di chuy n v đ u ể ề ầ
Last : Di chuy n v cu i ể ề ố
m u tin đ u ứ ẩ ừ ẩ ầ
Absolute n: di chuy n đ n m u tin th n tính t ế ể m u tin cu i tiên. N u ế n<0 : tính t ố ừ ẩ
m u tin hi n ứ ế ể ẩ ừ ẩ ệ
Relative n :di chuy n đ n m u tin th n tính t hành
ể ể
ệ ố @@Fetch_Status
Trong quá trình di chuy n đ ki m tra vi c di chuy n có thành ể ể ệ công hay không, ta ki m tra bi n h th ng ế ể n u ế @@Fetch_Status <>0 th t b i ấ ạ
II.2.4. Đóng Cursor l : i b ng l nh CLOSE và DEALLOCATE ạ ằ ệ
_ Sau khi Close , có th m l i. ể ở ạ
Cú pháp: Close Tên_Cursor
_ Gi i phóng CurSor kh i b nh : ả ỏ ộ ớ
Cú pháp: Deallocate Tên_Cursor
