Giáo trình Thực hành SQL - Trần Nguyên Phong (Phần 2)

Nội dung text: Giáo trình Thực hành SQL - Trần Nguyên Phong (Phần 2)

1. Tráön Nguyãn Phong Chæång 3: NGÄN NGÆÎ Î ÂIÃÖUÖ KHIÃØNØ 1JŒQQJŽ–ŠL‹žXNKL‹ QŠŽ¤FVŽ•GX£QJWURQJYL‹£FF‰¢SSKD—WKD\KX\•ER•TX\‹žQFX•D QJŽ”LVŽ•GX£QJŠŒ¢LY—LFD—FF‰XO‹£QK64/KRˆ£FWU‹QFD—FŠŒ¢LWŽ¤QJ&6'/ &ƒXO…›QK*5$17 &‰XO‹£QK*5$17ŠŽ¤FVŽ•GX£QJQKˆšPF‰¢SSKD—WTX\‹žQFKRQJŽ”LVX•GX£QJWU‹Q FD—FŠŒ¢LWŽ¤QJ&6'/KRˆ£FTX\‹žQWKŽ£FWKLFD—FF‰XO‹£QK64/&X—SKD—SFX•DF‰XO‹£QKQD”\ FR—KDLGD£QJQKŽVDX 'D›QJ&‰¢SSKD—WTX\‹žQŠŒ¢LY—LFD—FF‰XO‹£QK GRANT ALL | statement [, ,statementN ] TO account [, ,accountN] 'D›QJ&‰¢SSKD—WTX\‹žQŠŒ¢LY—LFD—FŠŒ¢LWŽ¤QJ&6'/ GRANT ALL | permission [, ,permissionN] ON table_name |view_name [(column1 [, ,columnN])] |ON stored_procedure TO account [, ,accountN] 7URQJŠR—  $//OD”WŽ”NKRD—ŠŽ¤FVŽ•GX£QJNKLPXŒ¢QF‰¢SSKD—WW‰¢WFD•FD—FTX\‹žQFR—WK‹ FKR QJŽ”LVŽ•GX£QJ  6WDWHPHQWOD”F‰XO‹£QKŠŽ¤FF‰¢SSKD—WTX\‹žQFKRQJŽ”LVŽ•GX£QJ&D—FF‰XO‹£QK FR—WK‹ F‰¢SSKD—WFKRQJŽ”LVŽ•GX£QJEDRJŒžP • &5($7('$7$%$6( • &5($7('()$8/7 • &5($7(352&('85( • &5($7(58/( • &5($7(7$%/( • &5($7(9,(: • %$&.83'$7$%$6( • %$&.83/2*
2. Giaïo trçnh thæûc haình SQL  DFFRXQWOD”W‹QWD”LNKRD•QFX•DQJŽ”LVŽ•GX£QJNKLŠˆQJQK‰£SYD”RK‹£WKŒ¢QJ  3HUPLVVLRQOD”PŒ¤WTX\‹žQF‰¢SSKD—WFKRQJŽ”LVŽ•GX£QJWU‹QŠŒ¢LWŽ¤QJ&6'/ YD”ŠŽ¤FTXLG“QKQKŽVDX • &D—FTX\‹žQFR—WK‹ F‰¢SSKD—WWU‹QPŒ¤WED•QJKRˆ£FNKXQJQKQ6(/(&7 ,16(57'(/(7(YD”83'$7( • &D—FTX\‹žQFR—WK‹ F‰¢SSKD—WWU‹QFD—FFŒ¤WFX•DED•QJKRˆ£FNKXQJQKQ 6(/(&7YD”83'$7( • 4X\‹žQFR—WK‹ F‰¢SSKD—WŠŒ¢LY—LWKX•WX£FOŽXWUŽ–(;&87( 9ŒGX› &‰¢SSKD—WTX\‹žQWKŽ£FWKLF‰XO‹£QK&5($7(7$%/(YD”&5($7(9,(:FKRWD”L NKRD•QFR—W‹QOD”GEBXVHU GRANT CREATE TABLE, CREATE VIEW TO db_user &‰¢SSKD—WFKRFD—FWD”LNKRD•QFR—W‹QOD”GEBXVHUYD”GEBXVHUTX\‹žQŠŽ¤F[HPYD” F‰£SQK‰£WGŽ–OL‹£XWU‹QFD—FFŒ¤WKRWHQGLDFKLGLHQWKRDLYD”KVOXRQJFX•DED•QJQKDQYLHQ GRANT SELECT, UPDATE ON nhanvien(hoten,diachi,dienthoai,hsluong) TO db_user1, db_user2 &ƒXO…›QK5(92.( &‰XO‹£QK5(92.(ŠŽ¤FVŽ•GX£QJŠ‹ KX\•ER•TX\‹žQŠD–ŠŽ¤FF‰¢SSKD—WFKRQJŽ”L VŽ•GX£QJWU‹QFD—FŠŒ¢LWŽ¤QJ&6'/KRˆ£FF‰XO‹£QK64/&‰XO‹£QK5(92.(FX–QJFR— KDLGD£QJQKŽVDX 'D›QJ+X\•ER•TX\‹žQŠŒ¢LY—LF‰XO‹£QK REVOKE ALL | statement [, ,statementN] FROM account [, ,accountN] 'D›QJ+X\•ER•TX\‹žQŠŒ¢LY—LŠŒ¢LWŽ¤QJ&6'/ REVOKE ALL | permission [, ,permissionN]} ON table_name | view_name [(column [, ,columnN])] | stored_procedure FROM account [, ,accountN ] 9ŒGX›+X\•ER•TX\‹žQ[HPYD”F‰£SQK‰£WGŽ–OL‹£XWU‹QFŒ¤W KVOXRQJFX•DED•QJ QKDQYLHQŠŒ¢LY—LWD”LNKRD•QFR—W‹QOD”GEBXVHU 7UDQJ
3. Tráön Nguyãn Phong REVOKE SELECT, UPDATE ON nhanvien(hsluong) FROM db_user1 +X\•ER•W‰¢WFD•FD—FTX\‹žQŠD–F‰¢SSKD—WFKRWD”LNKRD•QFR—W‹QOD”GEBXVHU REVOKE ALL FROM db_user Z Y
4. Giaïo trçnh thæûc haình SQL Chæång 4: THUÍ Í TUÛCÛ LÆU TRÆÎ Î VAÌ Ì TRIGGER ,6ˆGX›QJWKXWX›FOˆXWUˆVWRUHGSURFHGXUH C¸c thñ tôc l­u tr÷ lµ mét trong nh÷ng ®èi t­îng c¬ së d÷ liÖu. Cã thÓ xem chóng t­¬ng tù nh­ nh÷ng thñ tôc trong c¸c ng«n ng÷ lËp tr×nh. Mçi mét thñ tôc l­u tr÷ cã thÓ cã c¸c kh¶ n¨ng sau: • NhËn c¸c tham sè ®Çu vµo, thùc thi c¸c c©u lÖnh bªn trong thñ tôc vµ tr¶ vÒ c¸c gi¸ trÞ. • Bªn trong mçi thñ tôc cã thÓ chøa c¸c c©u lÖnh nh»m thùc hiÖn c¸c thao t¸c trªn c¬ së d÷ liÖu (kÓ c¶ viÖc gäi ®Õn c¸c thñ tôc l­u tr÷ kh¸c) • Tr¶ vÒ mét gi¸ trÞ tr¹ng th¸i th«ng qua ®ã cã thÓ x¸c ®Þnh viÖc thùc thi thñ tôc lµ thµnh c«ng hay bÞ lçi. ViÖc sö dông c¸c thñ tôc l­u tr÷ bªn trong c¬ së d÷ liÖu sÏ mang l¹i nh÷ng lîi Ých sau: • Thñ tôc l­u tr÷ cho phÐp module ho¸ c«ng viÖc, t¹o ®iÒu kiÖn thuËn lîi cho viÖc thùc hiÖn c¸c thao t¸c trªn d÷ liÖu. • Thñ tôc l­u tr÷ ®­îc ph©n tÝch, tèi ­u vµ biªn dÞch khi t¹o ra nªn viÖc thùc thi chóng nhanh h¬n nhiÒu so víi viÖc sö dông mét tËp c¸c c©u lÖnh giao t¸c SQL theo nh÷ng c¸ch th«ng th­êng. • Thñ tôc l­u tr÷ cho phÐp chóng ta thùc hiÖn cïng mét yªu cÇu b»ng mét c©u lÖnh ®¬n gi¶n thay v× ph¶i sö dông nhiÒu dßng lÖnh SQL. §iÒu nµy sÏ lµm gi¶m thiÓu sù l­u th«ng trªn m¹ng. • Thay v× cÊp ph¸t quyÒn trùc tiÕp cho ng­êi sö dông trªn c¸c c©u lÖnh SQL, ta cã thÓ cÊp ph¸t quyÒn cho ng­êi sö dông th«ng qua c¸c thñ tôc l­u tr÷, nhê ®ã t¨ng kh¶ n¨ng b¶o mËt ®èi víi hÖ thèng. ,T¹o c¸c thñ tôc l­u tr÷ §Ó t¹o mét sp, ta sö dông c©u lÖnh CREATE PROCEDURE cã có ph¸p nh­ sau: CREATE PROCEDURE procedure_name [;number] [(parameter1 [,parameter2] [parameter255])] AS sql_statements VÝ dô 4.1: CREATE PROC sp_list @bten char(20) AS SELECT hoten, ngaysinh, diachi FROM nhanvien WHERE hoten= @bten Chó ý: NÕu khi gäi thñ tôc, chóng ta truyÒn tham sè cho thñ tôc d­íi d¹ng: 7UDQJ
5. Tráön Nguyãn Phong @tham_sè = gi¸_trÞ Th× thø tù c¸c tham sè kh«ng cÇn ph¶i tu©n theo thø tù nh­ khi t¹o thñ tôc b»ng c©u lÖnh CREATE PROCEDURE. Tuy nhiªn, nÕu nh­ ®∙ cã mét tham sè ®­îc truyÒn gi¸ trÞ theo c¸ch trªn th× tÊt c¶ c¸c tham sè cßn l¹i còng ph¶i ®­îc truyÒn gi¸ trÞ theo c¸ch ®ã. Ta cã thÓ g¸n mét gi¸ trÞ mÆc ®Þnh cho tham sè trong c©u lÖnh CREATE PROCEDURE. Gi¸ trÞ nµy, cã thÓ lµ h»ng bÊt kú, sÏ ®­îc lÊy lµm tham sè cña thñ tôc khi ng­êi sö dông kh«ng cung cÊp gi¸ trÞ cho tham sè khi gäi thñ tôc. VÝ dô 4.2: CREATE PROC sp_list;2 @bten char(20)=‘Nguyen Van A’ AS SELECT * FROM nhanvien WHERE hoten = @bten Víi thñ tôc trªn, nÕu ta gäi msp_list;2 mµ kh«ng cã tham sè th× thñ tôc sÏ lÊy tham sè mÆc ®Þnh lµ ‘NguyÔn V¨n A’ cho @bten. Gi¸ trÞ mÆc ®Þnh cã thÓ NULL. Trong tr­êng hîp nµy, nÕu ng­êi sö dông kh«ng cung cÊp tham sè, SQL Server sÏ thi hµnh thñ tôc theo c¸c tham sè kh¸c. VÝ dô 4.3: Víi c©u lÖnh CREATE PROC sp_list;3 @bten char(20)=NULL,@bluong float AS SELECT * FROM nhanvien WHERE hoten=@bten AND hsluong=@bluong Ta thÓ gäi thñ tôc trªn nh­ sau: msp_list;3 @btuoi=23 mµ kh«ng bÞ lçi. MÆc ®Þnh cã thÓ bao gåm c¸c ký tù ®¹i diÖn (%, _, [], [^] ) nÕu thñ tôc sö dông tham sè víi tõ khãa LIKE. VÝ dô 4.4: CREATE PROC sp_list;4 @bten char(20) =’TrÇn%’ AS SELECT * FROM nhanvien WHERE hoten LIKE @bten ,Th«ng tin tr¶ vÒ tõ c¸c thñ tôc l­u tr÷ C¸c gi¸ trÞ tr¹ng th¸i tr¶ vÒ: C¸c thñ tôc cã thÓ tr¶ vÒ mét gi¸ trÞ nguyªn ®­îc gäi lµ mét tr¹ng th¸i tr¶ vÒ. Gi¸ trÞ nµy chØ ra cho biÕt thñ tôc ®­îc thùc hiÖn thµnh c«ng hay gÆp lçi vµ nguyªn nh©n cña lçi (SQL Server ®∙ ®Þnh nghÜa s½n mét tËp c¸c gi¸ trÞ tr¶ vÒ, c¸c gi¸ trÞ nµy n»m trong kho¶ng [-99;0]; trong ®ã gi¸ trÞ tr¶ vÒ b»ng 0 tøc lµ viÖc thùc hiÖn thñ tôc thµnh c«ng, c¸c gi¸ trÞ cßn l¹i cho biÕt nguyªn do khi bÞ lçi). Gi¸ trÞ tr¶ vÒ do ng­êi sö dông ®Þnh nghÜa Ng­êi sö dông cã thÓ ®Þnh nghÜa c¸c gi¸ trÞ tr¶ vÒ cña m×nh trong c¸c thñ tôc l­u tr÷ b»ng c¸ch bæ sung mét tham sè vµo c©u lÖnh RETURN. TÊt c¶ c¸c sè nguyªn ngo¹i trõ c¸c gi¸ trÞ dµnh riªng cho hÖ thèng ®Òu cã thÓ ®­îc sö dông.
6. Giaïo trçnh thæûc haình SQL VÝ dô 4.5: CREATE PROC sp_exam @bten char(20) AS IF EXISTS (SELECT * FROM nhanvien WHERE hoten = @bten) RETURN 1 ELSE RETURN 2 C¸c tham sè tr¶ vÒ Khi c¶ hai c©u lÖnh CREATE PROCEDURE vµ EXECUTE chøa môc chän OUTPUT cho tªn mét tham sè, thñ tôc cã thÓ sö dông mét biÕn ®Ó tr¶ vÒ trÞ cña tham sè ®ã ®Õn ng­êi gäi. B»ng viÖc sö dông tõ kho¸ OUTPUT, bÊt cø sù thay ®æi nµo ®Õn còng vÉn cßn gi÷ l¹i sau khi thñ tôc ®­îc thùc hiÖn, vµ c¸c biÕn cã thÓ ®­îc sö dông trong c¸c c©u lÖnh SQL bæ sung sau ®ã trong tËp lÖnh hay thñ tôc ®­îc gäi. NÕu tõ kho¸ OUTPUT kh«ng ®­îc sö dông, viÖc thay ®æi ®Õn tham sè sÏ kh«ng ®­îc gi÷ l¹i sau khi kÕt thóc thùc hiÖn thñ tôc. Ngoµi ra, ta cßn cã thÓ dïng RETURN ®Ó tr¶ vÒ gi¸ trÞ. Mét thñ tôc l­u tr÷ cã thÓ sö dông bÊt kú hoÆc tÊt c¶ kh¶ n¨ng sau ®Ó tr¶ vÒ: • Mét hoÆc nhiÒu tËp c¸c gi¸ trÞ. • Mét gi¸ trÞ tr¶ vÒ râ rµng (sö dông c©u lÖnh RETURN). • Mét tham sè OUTPUT. NÕu chóng ta chØ ®Þnh OUTPUT khi thùc hiÖn mét thñ tôc nh­ng tham sè t­¬ng øng kh«ng ®­îc ®Þnh nghÜa víi OUTPUT khi t¹o thñ tôc th× sÏ bÞ lçi. Tuy nhiªn nÕu ta ®Þnh nghÜa OUTPUT cho mét tham sè trong thñ tôc nh­ng kh«ng chØ ®Þnh OUTPUT khi thùc hiÖn th× vÉn kh«ng bÞ lçi (gi¸ trÞ tham sè khi ®ã sÏ kh«ng ®­îc tr¶ vÒ). VÝ dô 4.6: CREATE PROC Chia @sobichia real,@sochia real, @kqua real OUTPUT AS IF (@sochia =0) Print ‘Division by zero’ ELSE SELECT @kqua = @sobichia / @sochia Khi ®ã nÕu ta thùc hiÖn nh­ sau: DECLARE @ketqua real EXEC Chia 100, 2, @ketqua OUT SELECT @ketqua SÏ cho kÕt qu¶ lµ: 50.0 Cßn nÕu thùc hiÖn DECLARE @ketqua real EXEC Chia 100, 2, @ketqua SELECT @ketqua 7UDQJ
7. Tráön Nguyãn Phong SÏ cho kÕt qu¶ lµ: (null) I.3. C¸c qui t¾c sö dông cho sp Sau ®©y lµ mét sè qui t¾c cÇn l­u ý khi t¹o c¸c thñ tôc l­u tr÷ • C©u lÖnh CREATE PROCEDURE kh«ng thÓ kÕt hîp víi c¸c c©u lÖnh SQL kh¸c trong mét khèi lÖnh ®¬n (single batch). • B¶n th©n ®Þnh nghÜa CREATE PROCEDURE cã thÓ bao gåm bÊt kú sè l­îng còng nh­ c©u lÖnh SQL nµo ngo¹i trõ nh÷ng c©u lÖnh sau: CREATE VIEW CREATE TRIGGER CREATE DEFAULT CREATE PROCEDURE CREATE RULE • C¸c ®èi t­îng CSDL kh¸c cã thÓ ®­îc t¹o bªn trong mét thñ tôc l­u tr÷. Ta cã thÓ tham chiÕu mét ®èi t­îng ®­îc t¹o trong cïng thñ tôc miÔn lµ nã ®∙ ®­îc t¹o tr­íc khi tham chiÕu. • Bªn trong mét thñ tôc, ta kh«ng thÓ t¹o mét ®èi t­îng, xo¸ nã vµ sau ®ã t¹o mét ®èi t­îng míi víi cïng tªn. • Ta cã thÓ tham chiÕu c¸c b¶ng t¹m thêi bªn trong mét thñ tôc. • NÕu ta thùc thi mét thñ tôc mµ gäi ®Õn thñ tôc kh¸c, thñ tôc ®­îc gäi cã thÓ truy cËp ®Õn mäi ®èi t­îng ngo¹i trõ c¸c b¶ng t¹m thêi ®­îc t¹o bëi thñ tôc ®Çu tiªn. • NÕu ta t¹o mét b¶ng t¹m thêi riªng (private temporary table) bªn trong mét thñ tôc, b¶ng t¹m thêi chØ tån t¹i cho nh÷ng môc ®Ých cña thñ tôc ®ã; nã sÏ mÊt ®i khi tho¸t ra khái thñ tôc. • Sè tham sè tèi ®a cña mét thñ tôc lµ 255. • Sè biÕn côc bé vµ toµn côc trong mét thñ tôc chØ bÞ giíi h¹n bëi kh¶ n¨ng bé nhí. • C¸c thñ tôc t¹m thêi côc bé (private) vµ toµn côc (public), t­¬ng tù nh­ c¸c b¶ng t¹m thêi, cã thÓ ®­îc t¹o víi dÊu # vµ ## ®øng tr­íc tªn thñ tôc. # biÓu diÔn thñ tôc t¹m thêi côc bé cßn ## biÓu diÔn thñ tôc t¹m thêi toµn côc. I.4 X¸c ®Þnh tªn bªn trong c¸c thñ tôc Bªn trong mét thñ tôc, tªn c¸c ®èi t­îng ®­îc sö dông víi c©u lÖnh ALTER TABLE, CREATE TABLE, DROP TABLE, TRUNCATE TABLE, CREATE INDEX, DROP INDEX, UPDATE STATISTICS vµ DBCC ph¶i ®­îc x¸c ®Þnh víi tªn cña ng­êi së h÷u ®èi t­îng (object owner’s name) nÕu nh­ nh÷ng ng­êi dïng (user) kh¸c sö dông thñ tôc. VÝ dô, ng­êi dïng Mary, lµ së h÷u chñ cña b¶ng marytab, ph¶i chØ ®Þnh tªn cña b¶ng cña m×nh khi nã ®­îc sö dông víi mét trong nh÷ng c©u lÖnh nµy nÕu c« ta muèn nh÷ng user kh¸c cã thÓ thùc hiÖn thñ tôc mµ trong ®ã b¶ng ®­îc sö dông. Qui t¾c nµy lµ cÇn thiÕt v× tªn ®èi t­îng ®­îc ph©n tÝch khi c¸c thñ tôc ®­îc ch¹y. NÕu marytab kh«ng ®­îc chØ ®Þnh vµ user John t×m c¸ch thùc hiÖn thñ tôc, SQL
8. Giaïo trçnh thæûc haình SQL sÏ t×m b¶ng marytab do John së h÷u. VÝ dô d­íi ®©y lµ mét c¸ch dïng ®óng, nã chØ ra cho SQL Server t×m b¶ng marytab do Mary së h÷u: CREATE PROC p1 AS CREATE INDEX marytab_ind ON mary.marytab(col1) I.5 §æi tªn c¸c thñ tôc: Sö dông thñ tôc: sp_rename old_name, new_name Ta chØ cã thÓ ®æi tªn nh÷ng thñ tôc mµ ta së h÷u. Ng­êi së h÷u CSDL cã thÓ thay ®æi tªn cña bÊt kú thñ tôc nµo cña ng­êi sö dông. Thñ tôc ®­îc ®æi tªn ph¶i n»m trong CSDL hiÖn thêi. Ta ph¶i xo¸ vµ t¹o l¹i mét thñ tôc nÕu ta thay ®æi tªn cña mét ®èi t­îng ®­îc tham chiÕu bëi thñ tôc ®ã. §Ó cã ®­îc b¸o c¸o vÒ nh÷ng ®èi t­îng ®­îc tham chiÕu bëi mét thñ tôc, ta sö dông thñ tôc hÖ thèng: sp_depends. §Ó xem néi dung cña ®Þnh nghÜa mét thñ tôc, ta sö dông thñ tôc hÖ thèng: sp_helptext. I.6. Xo¸ thñ tôc: §Ó xo¸ mét thñ tôc, ta sö dông c©u lÖnh: DROP PROCEDURE proc_name II. Sö dông c¸c Trigger Mét trigger lµ mét d¹ng ®Æc biÖt cña thñ tôc l­u tr÷ vµ nã ®­îc thùc hiÖn tù ®éng khi ng­êi dïng ¸p dông c©u lÖnh söa ®æi d÷ liÖu lªn mét b¶ng ®­îc chØ ®Þnh. C¸c trigger th­êng ®­îc sö dông cho viÖc Ðp buéc c¸c qui t¾c lµm viÖc vµ toµn vÑn d÷ liÖu. TÝnh toµn vÑn tham chiÕu cã thÓ ®­îc ®Þnh nghÜa b»ng c¸ch sö dông rµng buéc FOREIGN KEY víi c©u lÖnh CREATE TABLE. NÕu c¸c rµng buéc tån t¹i trong b¶ng cã sù t¸c ®éng cña trigger, nã ®­îc kiÓm tra tr­íc viÖc thùc hiÖn trigger. NÕu c¸c rµng buéc bÞ vi ph¹m, trigger sÏ kh«ng thùc thi. C¸c trigger ®­îc sö dông trong nh÷ng c¸ch sau: • C¸c trigger cã thÓ thay ®æi ®ång lo¹t (cascade change) c¸c b¶ng cã liªn hÖ trong mét CSDL. • C¸c trigger cã thÓ kh«ng cho phÐp hoÆc roll back nh÷ng thay ®æi vi ph¹m tÝnh toµn vÑn tham chiÕu, hñy bá giao t¸c söa ®æi d÷ liÖu. • C¸c trigger cã thÓ ¸p ®Æt c¸c giíi h¹n phøc t¹p h¬n nh÷ng giíi h¹n ®­îc ®Þnh nghÜa b»ng rµng buéc CHECK. Kh¸c víi rµng buéc CHECK, c¸c trigger cã thÓ tham chiÕu ®Õn c¸c cét trong c¸c b¶ng kh¸c. • C¸c trigger cßn cã thÓ t×m sù kh¸c biÖt gi÷a c¸c tr¹ng th¸i cña mét b¶ng tr­íc vµ sau khi s÷a ®æi d÷ liÖu vµ lÊy ra nh÷ng t¸c ®éng dùa trªn sù kh¸c biÖt ®ã. 7UDQJ
9. Tráön Nguyãn Phong II.1 T¹o c¸c trigger Mét trigger lµ mét ®èi t­îng CSDL. Ta t¹o mét trigger b»ng viÖc chØ ®Þnh b¶ng hiÖn hµnh vµ c©u lÖnh söa ®æi d÷ liÖu kÝch ho¹t trigger. Sau ®ã ta x¸c ®Þnh c¸c c«ng viÖc mµ trigger lµm. Mét b¶ng cã thÓ cã tèi ®a 3 lo¹i trigger: mét trigger cËp nhËt (update trigger), mét trigger chÌn (insert trigger) vµ mét trigger xãa (delete trigger). Tuy nhiªn, mçi trigger cã thÓ thùc hiÖn nhiÒu hµm vµ gäi ®Õn 16 thñ tôc. Mçi trigger chØ cã thÓ ¸p dông cho mét b¶ng. Tuy nhiªn, mét trigger ®¬n cã thÓ ¸p dông cho c¶ 3 c«ng viÖc (UPDATE, INSERT vµ DELETE). Ta kh«ng thÓ t¹o mét trigger trªn mét khung nh×n hay mét b¶ng t¹m thêi mÆc dï c¸c trigger cã thÓ tham chiÕu c¸c khung nh×n hay c¸c b¶ng t¹m thêi. C©u lÖnh TRUNCATE TABLE mÆc dï gièng c©u lÖnh DELETE khi kh«ng cã mÖnh ®Ò WHERE nh­ng nã kh«ng thÓ kÝch ho¹t mét trigger. §Ó t¹o míi mét trigger, ta sö dông c©u lÖnh cã có ph¸p nh­ sau: CREATE TRIGGER trigger_name ON table_name FOR {INSERT, UPDATE, DELETE} AS sql_statements HoÆc sö dông mÖnh ®Ò IF UPDATE: CREATE TRIGGER trigger_name ON table_name FOR {INSERT, UPDATE} AS IF UPDATE (column_name) [{ANDOR}UPDATE (column_name) ] sql_statements VÝ dô 4.7: NÕu chóng ta muèn sau khi ta cËp nhËt d÷ liÖu cho b¶ng nhanvien, SQL Server sÏ hiÓn thÞ néi dung cña b¶ng ®Ó xem th× ta t¹o mét trigger nh­ sau: CREATE TRIGGER tgr_check ON nhanvien FOR INSERT, UPDATE AS print ' Ket qua sau khi cap nhat ' SELECT * FROM nhanvien II.2 C¸c gi¸ trÞ null ngÇm ®Þnh vµ hiÓn (implicit and explicit null values) MÖnh ®Ò IF UPDATE(tªn_cét) lµ ®óng cho mét c©u lÖnh INSERT khi mµ cét ®­îc g¸n mét gi¸ trÞ trong danh s¸ch chän hay trong mÖnh ®Ò VALUES. Mét NULL hiÓn (explicit) hay mét mÆc ®Þnh g¸n mét gi¸ trÞ cho mét cét vµ v× thÕ kÝch ho¹t trigger. Víi mét NULL ngÇm ®Þnh, nÕu gi¸ trÞ kh«ng ®­îc x¸c ®Þnh bëi c©u hái hoÆc bëi mÆc ®Þnh ®­îc g¸n, trigger trªn cét ®ã kh«ng ®­îc kÝch ho¹t. VÝ dô 4.8: CREATE TABLE vidu(col1 int NULL,col2 int NOT NULL) GO CREATE TRIGGER tgr_vidu ON vidu
10. Giaïo trçnh thæûc haình SQL FOR INSERT AS IF UPDATE(col1) AND UPDATE(col2) Print ‘Firing’ GO CREATE DEFAULT col2_default AS 99 GO /* IF UPDATE lµ ®óng cho c¶ hai cét, trigger ®­îc kÝch ho¹t */ INSERT vidu(col1,col2) VALUES(1, 2) /* IF UPDATE lµ ®óng cho c¶ hai cét, trigger ®­îc kÝch ho¹t */ INSERT vidu VALUES(1, 2) /* NULL hiÓn: IF UPDATE lµ ®óng cho c¶ hai cét, trigger ®­îc kÝch ho¹t */ INSERT vidu VALUES(null, 2) /* Kh«ng cã mÆc ®Þnh trªn cét col1, IF UPDATE kh«ng ®óng cho c¶ hai cét, trigger kh«ng ®­îc kÝch ho¹t */ INSERT vidu(col2) VALUES(2) /* Kh«ng cã mÆc ®Þnh trªn cét col2, IF UPDATE kh«ng ®óng cho c¶ hai cét, trigger kh«ng ®­îc kÝch ho¹t */ INSERT vidu(col1) VALUES(2) KÕt qu¶ t­¬ng tù ®­îc s¶n sinh víi viÖc sö dông chØ mÖnh ®Ò IF UPDATE(col1) §Ó t¹o mét trigger kh«ng cho phÐp viÖc chÌn c¸c gi¸ trÞ null ngÇm ®Þnh, ta sö dông: IF UPDATE(col2) OR UPDATE(col2) C©u lÖnh SQL trong trigger cã thÓ sau ®ã kiÓm tra xem col1 lµ NULL hay kh«ng. II.3 ViÖc ®æi tªn vµ c¸c trigger NÕu mét b¶ng ®­îc tham chiÕu bëi mét trigger bÞ ®æi tªn, ta ph¶i xo¸ trigger ®ã ®i vµ t¹o l¹i nã ®Ó phï hîp viÖc tham chiÕu cña nã ®Õn b¶ng. Thñ tôc sp_depends cã chøc n¨ng liÖt kª tÊt c¶ c¸c trigger tham chiÕu ®Õn ®èi t­îng (ch¼ng h¹n b¶ng hay khung nh×n) hoÆc tÊt c¶ c¸c b¶ng hay khung nh×n mµ trigger t¸c ®éng. VÝ dô sau ®©y liÖt kª c¸c ®èi t­îng ®­îc tham chiÕu bëi trigger tgr_check: sp_depends tgr_check II.4 HiÓn thÞ th«ng tin vÒ c¸c trigger Do c¸c trigger lµ c¸c ®èi t­îng CSDL nªn chóng ®­îc liÖt kª trong b¶ng hÖ thèng sysobjects. Cét type trong sysobjects x¸c ®Þnh c¸c trigger víi ch÷ viÕt t¾t TR. S¬ ®å thùc thi c¸c trigger ®­îc l­u tr÷ trong b¶ng sysprocedures. Truy vÊn d­íi ®©y t×m c¸c trigger trong mét CSDL: SELECT * FROM sysobjects WHERE type=’TR’ §Ó hiÓn thÞ th«ng tin vÒ mét trigger ta thùc hiÖn thñ tôc: 7UDQJ
11. Tráön Nguyãn Phong sp_help trigger_name C©u lÖnh CREATE TRIGGER cho mçi trigger ®­îc l­u tr÷ trong b¶ng hÖ thèng syscomments. Ta cã thÓ hiÓn thÞ lêi ®Þnh nghÜa trigger b»ng c¸ch sö dông thñ tôc sp_helptext. VÝ dô 4.9: thùc hiÖn sp_helptext tgr_check ta ®­îc kÕt qu¶ nh­ sau: text create trigger tgr_check on nhanvien for insert,update as print ' Ket qua sau khi cap nhat ' select * from nhanvien II.5 Xo¸ trigger Ta cã thÓ xo¸ mét trigger b»ng c¸ch xo¸ nã hoÆc xo¸ b¶ng trigger. Khi mét b¶ng ®­îc xãa, nh÷ng trigger nµo cã liªn quan víi nã còng ®ång thêi bÞ xãa. DROP TRIGGER mÆc ®Þnh cho phÐp ®èi víi ng­êi sö dông b¶ng trigger vµ kh«ng thÓ chuyÓn cho ng­êi kh¸c. Ta cã thÓ xãa mét trigger b»ng c¸ch sö dông c©u lÖnh DROP TRIGGER
12. Giaïo trçnh thæûc haình SQL Chæång 5: PHUÛ Û LUÛCÛ 7URQJFKŽQJQD”\FKX—QJWŒLJL—LWKL‹£XF‰¢XWUX—FYD”GŽ–OL‹£XFX•DFD—FED•QJŠŽ¤FVŽ• GX£QJWURQJFD—FY’GX£WURQJFKŽQJ6DXŠR—JL—LWKL‹£XPŒ¤WVŒ¢KD”PWKŽ”QJVŽ•GX£QJ WURQJ64/6HUYHUŠ‹ JLX—SFD—FED£QWKDPNKD•RWURQJTXD—WUQKWKŽ£FKD”QK &ƒšXWUX‘FYDŽGˆOL…›XFXDEDQJ1+$19,(1YDŽ'219, 1+$19,(1 '219, 0$19  0$'219, +27(1 7(1'219, 1*$<6,1+ ',(17+2$, ',$&+, ',(17+2$, +6/821* 0$'219, ∞ %D•QJ1+$19,(1 0$19 +27(1 1*$<6,1+ ',$&+, ',(17+2$, +6/821* 0$'219, 19 1JX\‹¡Q7K“+RD  /‹'X‰ Q    19 /‹+RD”L1DP  7U‰žQ3KX—    19 +RD”QJ1DP3KRQJ  +RD”QJ'L‹£X    19 7U‰žQ1JX\‹Q3KRQJ  +D”1Œ¤L    19 1JX\‹¡Q+Ž–X7QK  +D”1Œ¤L    19 1JX\‹¡Q7UXQJ.L‹Q  1JX\‹¡Q+X‹£    %D•QJ'219, 0$'219, 7(1'219, ',(17+2$,  3KR”QJ.‹¢WRD—Q   3KR”QJ7Œ FKŽ—F   3KR”QJŠL‹žXKD”QK   3KR”QJŠŒ¢LQJRD£L   3KRQJ7D”LYX£  0†œWV†šKDŽPWKˆ‡ŽQJVˆGX›QJWURQJ64/6HUYHU &D‘FKDŽPWU…QGˆOL…›XNL…˜XQJDŽ\YDŽJL‡Ž D+DŽP'$7($'' 7UDQJ
13. Tráön Nguyãn Phong &X‘SKD‘S'$7($''GDWHSDUWQXPEHUGDWH &Kˆ‘FQ‚QJ+D”PWUD•Y‹žPŒ¤WJLD—WU“NL‹ X'DWH7LPHEˆšQJFD—FKFŒ¤QJWK‹PPŒ¤W NKRD•QJJLD—WU“OD”QXPEHUYD”RQJD”\GDWHŠŽ¤FFKŠ“QK 'DWHSDUWWKDPVŒ¢FKŠ“QK WKD”QK SK‰žQ VH– ŠŽ¤F FŒ¤QJ ŠŒ¢L Y—L JLD—WU“GDWHEDR JŒžP 'DWHSDUW 9L…šWW‚–W \HDU \\\\\\ TXDUWHU TTT PRQWK PPP GD\RI\HDU G\\ GD\ GGG ZHHN ZNZZ KRXU KK PLQXWH PLQ VHFRQG VVV PLOOLVHFRQG PV E+DŽP'$7(',)) &X‘SKD‘S'$7(',))GDWHSDUWVWDUWGDWHHQGGDWH &Kˆ‘FQ‚QJ+D”PWUD•Y‹žNKRD•QJWK”LJLDQJLŽ–DKDLJLD—WU“NL‹ XQD”\ŠŽ¤FFKŠ“QK WX\”WKXŒ¤FYD”RWKDPVŒ¢GDWHSDUW 9ŒGX›KD”P'DWHGLII\HDU    FKRN‹¢WTXD•OD” F+DŽP'$7(3$57 &X‘SKD‘S'$7(3$57GDWHSDUWGDWH &Kˆ‘FQ‚QJ+D”PWUD•Y‹žPŒ¤WVŒ¢QJX\‹QŠŽ¤FWU’FKUDWŽ”WKD”QKSK‰žQŠŽ¤FFK Š“QKE•LWKDPVŒ¢SDUWGDWHWURQJJLD—WU“NL‹ XQJD”\ŠŽ¤FFKŠ“QK 9ŒGX›+D”P'DWH3DUW\HDU  FKRN‹¢WTXD•OD” G+DŽP*(7'$7( &X‘SKD‘S*(7'$7( &Kˆ‘FQ‚QJ+D”PWUD•Y‹žQJD”\KL‹£QWD£L H+DŽP'$<0217+<($5 &X‘SKD‘S'$<GDWH0217+GDWH<($5GDWH &Kˆ‘FQ‚QJ+D”PWUD•Y‹žJLD—WU“QJD”\WŽQJŽ—QJWKD—QJQˆPFX•DJLD—WU“ NL‹ X QJD”\ŠŽ¤FFKŠ“QK &D‘FKDŽPY…—FKX†™L
14. Giaïo trçnh thæûc haình SQL D+DŽP/()7 &X—SKD—S/()7VWULQJQ &KŽ—FQˆQJ+D”PWU’FKUDWŽ”FKXŒ¡LVWULQJQN\—WŽ£W’QKWŽ”E‹QWUD—L E+DŽP5,*+7 &X‘SKD‘S/()7VWULQJQ &Kˆ‘FQ‚QJ+D”PWU’FKUDWŽ”FKXŒ¡LVWULQJQN\—WŽ£W’QKWŽ”E‹QSKD•L F+DŽP68%675,1* &X‘SKD‘S68%675,1*VWULQJPQ &Kˆ‘FQ‚QJ+D”PWU’FKUDWŽ”FKXŒ¡LVWULQJQN\—WŽ£W’QKWŽ”N\—WŽ£WKŽ—P G+DŽP/75,0575,0 &X‘SKD‘S/75,0VWULQJ575,0VWULQJ &Kˆ‘FQ‚QJ+D”PFˆWER•FD—FNKRD•QJWUˆQJWKŽ”DE‹QWUD—LE‹QSKD•LFKXŒ¡LVWULQJ H+DŽP/(1 &X‘SKD‘S/(1VWULQJ &Kˆ‘FQ‚QJ+D”PWUD•Y‹žŠŒ¤GD”LFX•DFKXŒ¡LVWULQJ 7UDQJ
15. Tráön Nguyãn Phong MUÛCÛ LUÛCÛ &+ml1*1*k11*muir1+1*+p$'mu/,j€8 7$ƒ2%$u1*'nv/,kƒ8 &D—FWKXŒ¤FW’QKOL‹QTXDQŠ‹¢QED•QJ 7D£RED•QJEˆšQJWUX\Y‰¢Q64/  6Ž•DŠŒ LED•QJ &+p08ƒ&,1'(;  .+81*1+o19,(: &+ml1*1*k11*mu7+$27$v&'mu/,j€8 758<;8i‚7'nv/,kƒ8 1.1 X¸c ®Þnh b¶ng b»ng mÖnh ®Ò FROM 12 1.2 MÖnh ®Ò WHERE 13 1.3 Danh s¸ch chän trong c©u lÖnh SELECT 14 1.4 TÝnh to¸n c¸c gi¸ trÞ trong c©u lÖnh SELECT 16 1.5 Tõ kho¸ DISTINCT 16 1.6 T¹o b¶ng míi b»ng c©u lÖnh SELECT INTO 17 1.7 S¾p xÕp kÕt qu¶ truy vÊn b»ng ORDER BY 17 1.8 PhÐp hîp vµ to¸n tö UNION 18 1.9 PhÐp nèi 20 1.10 T¹o c¸c dßng thèng kª d÷ liÖu víi COMPUTE BY 24 1.11 Thèng kª d÷ liÖu víi GROUP BY vµ HAVING 26 1.12 Truy váún con (subquery) 27 %l€681*&iƒ31+iƒ79$t;2$w'nv/,kƒ8  %Œ VXQJGŽ–OL‹£X &‰£SQK‰£WGŽ–OL‹£X  ;RD—GŽ–OL‹£X  &+ml1*1*k11*mui,j|8.+,j}1 &i8/kƒ1+*5$17 &i8/kƒ1+5(92.(  &+ml1*7+8t78€&/m875mu9$s75, (5 ,6nu'8ƒ1*7+8u78ƒ&/n875nv6725('352&('85( I.1. T¹o c¸c thñ tôc l­u tr÷ 35 I.2. Th«ng tin tr¶ vÒ tõ c¸c thñ tôc l­u tr÷ 36 I.3. C¸c qui t¾c sö dông cho sp 38 I.4 X¸c ®Þnh tªn bªn trong c¸c thñ tôc 38 I.5 §æi tªn c¸c thñ tôc: 39 I.6. Xo¸ thñ tôc: 39 II. SÖ DÔNG C¸C TRIGGER  II.1 T¹o c¸c trigger 40 II.2 C¸c gi¸ trÞ null ngÇm ®Þnh vµ hiÓn (implicit and explicit null values) 40 II.3 ViÖc ®æi tªn vµ c¸c trigger 41 II.4 HiÓn thÞ th«ng tin vÒ c¸c trigger 41 II.5 Xo¸ trigger 42 &+ml1*3+8€/8€& &i‚8758w&9$t'nv/,kƒ8&8u$%$u1*1+$19,(19$t'219,  0l„76l‚+$t07+nmt1*6nu'8ƒ1*7521*64/6(59(5  &D—FKD”PWU‹QGŽ–OL‹£XNL‹ XQJD”\YD”JL” 2.2 Caïc haìm vãö chuäùi 44