Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .Net - P4

Tham khảo tài liệu programming microsoft sql server 2000 with microsoft visual basic .net - p4, công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả
Nội dung trích xuất từ tài liệu:
Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .Net - P4 a disabled t r igger, all y ou hav e t o do is enable it . Recall t hat you can disable and enable a t r igger w it h an ALTER TABLE st at em ent for t he t able w it h t he t rigger t hat y ou want t o disable t em porarily. The follow ing scr ipt dem onst rat es t he synt ax for cr eat ing a t rigger for t he MyTable t able cr eat ed earlier in t his chapt er. ( See t he “Cr eat ing a Scalar UDF Wit hout Param et ers” sect ion.) The t r igger pr ot ect s t he t able fr om insert s, updat es, and delet es by rolling back t he t ransact ion associat ed w it h t he t r igger. The script st art s by r em ov ing any previous version of t he t rgKeepMyTableUnt ouched t r igger and t hen begins a CREATE TRI GGER st at em ent . Lik e m ost ot her CREATE st at em ent s, t he CREATE TRI GGER st at em ent m ust occur at t he t op of a bat ch. Therefore, t he code t o drop t he old version ends wit h t he GO k eyw ord. The ON clause of t he CREATE TRI GGER st at em ent designat es t he MyTable t able as t he one t o w hich t he t r igger w ill belong. The FOR clause indicat es t hat t he t r igger w ill fir e for insert , updat e, and delet e ev ent s. The first st at em ent aft er t he AS k eyw ord is a RAI SERROR st at em ent t hat sends a cust om m essage back t o t he Messages pane of Query Analyzer. An inform at ional m essage issued from a t rigger is useful for let t ing a user k now t hat a t r igger fired. The RAI SERROR st at em ent can serv e ot her funct ions as well, but it is a robust alt er nat iv e t o t he PRI NT st at em ent for sending m essages t o t he Messages pane. The st r ing for a cust om m essage can be up t o 400 charact ers. The t railing values 16 and 1 indicat e t he sever it y and st at e for t he err or. For sim ple inform at ional m essages, y ou can consist ent ly apply t hese v alues. The second T- SQL st at em ent in t he script r olls back t he t ransact ion t o m odify t he t able. The ROLLBACK TRAN st at em ent is an abbrev iat ed version of t he ROLLBACK TRANSACTI ON st at em ent . I n eit her form , t his st at em ent rem ov es any insert ed rows, r est or es any colum n values t o t heir nonupdat ed st at e, and adds back any delet ed r ows. You w ill generally want t o use t he ROLLBACK TRAN st at em ent as t he last st at em ent in a t rigger because any st at em ent s aft er ROLLBACK TRAN can m odify t he t able for a t r igger. --trgKeepMyTableUntouched --Drop prior version of trigger. IF EXISTS (SELECT name FROM sysobjects WHERE name = ’trgKeepMyTableUntouched’ AND type = ’TR’) DROP TRIGGER trgKeepMyTableUntouched GO --Create new trigger to keep MyTable table untouched. CREATE TRIGGER trgKeepMyTableUntouched ON MyTable FOR INSERT, UPDATE, DELETE AS RAISERROR(‘Message from trgKeepMyTableUntouched.’,16,1) ROLLBACK TRAN GO The follow ing scr ipt is a collect ion of T- SQL st at em ent s t hat dem onst rat es t he behav ior of t he t rigger as well as how t o disable and rest ore t he t r igger. The first couple of bat ches in t he script at t em pt t o delet e all r ows fr om t he My Table t able and m odify a colum n value in t he t able. Neit her bat ch succeeds because t he t rgKeepMyTableUnt ouched t r igger pr ot ect s t he MyTable t able fr om delet e and updat e ev ent s ( as w ell as insert ev ent s) . I f it becom es essent ial t o m odify a t able wit h a t rigger t hat blocks changes, y ou can t em porar ily disable t he t r igger. The script dem onst rat es t he synt ax for t he t rgKeepMyTableUnt ouched t r igger . You have t o m odify t he My Table t able w it h t he ALTER TABLE st at em ent t o disable it s t r igger. Aft er disabling t he t r igger, t he script changes t he m axim um v alue in t he col1 colum n. Then, in anot her bat ch, t he script r est or es t he init ial m ax im um value. The sc ...