事務(wù) 游標(biāo) 存儲過程 觸發(fā)器

1、事務(wù) 游標(biāo) 存儲過程 觸發(fā)器1, triggers.Definition: what is a trigger? In SQL Server, a program that performs certain operations on a table, triggers certain conditions, and thus executes. A trigger is a special stored procedure.There are three common triggers: Insert, Update, and Delete, respectively. (SQL Serve

2、r 2000 defines a new trigger, which is not mentioned here.)Why should I use triggers? For example, two tables:Create Table Student (- student table)StudentID, int, primary, key - student number.)Create Table BorrowRecord (- student library record form)BorrowRecord int identity (1,1), serial number:S

3、tudentID int, - student numberBorrowDate datetime - lending timeReturnDAte Datetime - - return time.)The functions used are:1. if I change the students student number, I hope his library record is still relevant to the student (that is to say, to change the number of student books at the same time);

4、2. if the student has graduated, I want to delete his student number at the same time, but also delete its library record.Wait。Flip flops can be used at this point. For 1, create a Update trigger:Create Trigger truStudentOn StudentFor Update-Name:truStudent-func: updates the BorrowRecord of StudentI

5、D, synchronizing with Student.-Use: None-User:System-Author: SapphireStudio () # lazybone-Date: 2003-4-16-Memo: for a short time, lets make a Sample. No debugging.-AsIf Update (StudentID)BeginUpdate BorrowRecordSet br.StudentID=i.StudentIDFrom, BorrowRecord, Br, Deleted, D, Inserted, IWhere br.Stude

6、ntID=d.StudentIDEndUnderstand the two temporary tables inside the trigger: Deleted, Inserted. Note that Deleted and Inserted represent the tables that trigger events, the old record, and the new record, respectively.A Update process can be viewed as generating new records to the Inserted table, and

7、copying the old records to the Deleted table,Then delete the Student record and write the new record.For 2, create a Delete triggerCreate trigger trdStudentOn StudentFor Delete-Name:trdStudent-func: deletes data from BorrowRecord at the same time-Use: None-User:System-Author: SapphireStudio () # laz

8、ybone-Date: 2003-4-16-Memo: for a short time, lets make a Sample. No debugging.-AsDelete BorrowRecordFrom, BorrowRecord, Br, Delted, DWhere br.StudentID=d.StudentIDFrom these two examples, you can see the key to the trigger: A.2 temporary tables, and the B. trigger mechanism.Here we explain only the

9、 simplest triggers. Complicated post description.In fact, I do not encourage the use of triggers. The initial design idea of flip flops has been replaced by cascading.2. cursorsBefore SQL 2000, the cursor was a pain in the heart of SQL Server: the speed of the cow CPU and the hippos appetite (memory

10、). But you cant do without him.What is a cursor? To put it plainly, like high-level languages, it is a mechanism for storing data sets and accessing them one by one.For example, in Delphi, to achieve similar functions: (ha ha, sorry, I will only Delphi, so I can only cite an example of Delphi)/ / th

11、is is a Delphi source codeAdoDataSet1.Close;AdoDataSet1.CommandText:=Select From Student order by StudentID *;AdoDataSet1.Open;While Not adoDAtaSet1.Eof DoBeginYourVar:=adoDAtaSet1.FieldByName (StudentID).AsInteger;DoSomeThing ();.AdoDataSet1.Next;EndThere is no good data access mechanism in SQL Ser

12、ver, and if so, thats the cursor.For example?:For tableCreate Table BorrowRecord (- student library record form)BorrowRecord int identity (1,1), serial number:StudentID int, - student numberStudentFeeID int - - cost settlement number (foreign key)BorrowDate datetime - lending timeReturnDAte Datetime

13、,- return timeFee Money - Library fees.)Create Table StudentFee (- student fee statement)StudentFeeID int primarykey - cost settlement number (primary key)StudentID int, - student numberBorrowBookAllFee, Money - all the library fees.)The relationship is a one to one relationship, and the associated

14、field is StudentFeeIDFor some reason, the StudentFee table data is destroyed, I think StudentFee will cycle over all the total cost of borrowing retry.-Name: part of the code-func: update student book fees-Use:-User:-Author: SapphireStudio () # lazybone-Date: 2003-4-16-Memo: for a short time, lets m

15、ake a Sample. No debugging.- declare a cursorDeclare curStudentFee CursorForSelect StudentFeeID From StudentFee- declare two cost variablesDeclare, mBorrowBookAllFee, Money - total costDeclare iStudentFeeID Int - Library billing number- initializationSet mBorrowBookAllFee=0Set iStudentFeeID=0Open th

16、e cursorOpen curStudentFee- loop and extract recordsFetch, Next, From, curStudentFee, Into, iStudentFeeIDWhile (Fetch_Status=0)Begin- calculate the total cost of a students total book entry from a library recordSelect mBorrowBookAllFee=Sum (BorrowBookAllFee)From BorrowRecordWhere StudentFeeID=iStude

17、ntFeeID- update to summary table.Update StudentFee Set BorrowBookAllFee=mBorrowBookAllFeeWhere StudentFeeID=iStudnetFeeIDFetch, Next, From, curStudentFee, Into, mFeeEnd- close the cursorClose curStudentFeeRelease cursordeallocate curstudentfee-關(guān)注游標(biāo)的要點: 1、聲明、打開、關(guān)閉、釋放; 2、 fetch _ status 游標(biāo)提取狀態(tài)標(biāo)志, 0表示正

18、確這里, 我也要提到, 我不鼓勵使用游標(biāo).更多的情況下, 在sql 2000 里面, 函數(shù)已經(jīng)能夠?qū)崿F(xiàn)絕大部分游標(biāo)的功能.好累, 好不容易算是將1、2點講完, 算是上部分把.后面的幾點等會再說了.: (: (.大家給點鼓勵?- 作者: 流星翩翩- 發(fā)布時間: 2005 - 7 - 25 18: 17: 26- - - - - - -這里決定把4與3調(diào)換一下, 先講解4 (存儲過程, 以下用sp來簡稱; 函數(shù), 以下用fn來簡稱)4、存儲過程.存儲過程是數(shù)據(jù)庫編程里面最重要的表現(xiàn)方式了.呵呵, 這里我要提到上次說道的: 我拒絕使用觸發(fā)器.這里我要開始猛批一頓觸發(fā)器了.在sql 2000里, 說實

19、話, 我實在找不出觸發(fā)器可以存在的理由.回憶一下: 觸發(fā)器是一種特殊的存儲過程.它在一定的事件 (insert, update, delete 等) 里自動執(zhí)行.我建議使用sp和級聯(lián)來代替觸發(fā)器.在sql 7 里面, 觸發(fā)器通常用于更新、或刪除相關(guān)表的數(shù)據(jù), 以維護(hù)數(shù)據(jù)的完整.sql 7里面, 沒有級聯(lián)刪除和級聯(lián)修改的功能. 只能建立起關(guān)系.既然sql 2000里面提供了級聯(lián), 那么觸發(fā)器就沒有很好的存在理由.更多的情況下是作為一個向下兼容的技術(shù)而存在.當(dāng)然, 也有人喜歡把觸發(fā)器作為處理數(shù)據(jù)邏輯, 甚至是業(yè)務(wù)邏輯的自動存儲過程. 這種方法并不足取.這里列舉以下使用觸發(fā)器的一些壞處:a、 地下

20、運(yùn)行.觸發(fā)器沒有很好的調(diào)試、管理環(huán)境.調(diào)試一段觸發(fā)器, 要比調(diào)試一段sp更耗費時間與精力.b、類似于goto語句. (過分自由的另外一個說法是: 無政府主義!)一個表, 可以寫入多個觸發(fā)器, 包括同樣for update的10個觸發(fā)器! 同樣for delete的10個觸發(fā)器.也就是說, 你每次要對這個表進(jìn)行寫操作的時候, 你要一個一個檢查你的觸發(fā)器, 看看他們是做什么的, 有沒有沖突.或許, 你會很牛b的對我說: 我不會做那么傻b的事情, 我記得住我做了些什么! 3個月以后呢? 10個月以后呢? 你還會對我說你記得住么?c、嵌套觸發(fā)器、遞歸觸發(fā)器你敢說你這么多的觸發(fā)器中不會存在table1更

21、新了table2表,This triggers the Table2 table to update the TAble3, and the TAble3 trigger triggers the Table1 update again. Table2?It might also happen: your program updates the Table1.Fd1, the trigger immediately updates the Table1.fd1, triggers the event again, and the trigger updates Table1.fd1 again

22、.Of course, SQL Server can set and prevent applications from entering the dead loop, but the results may not be what you want.I cant think of any more harm to the trigger because I left it a long time ago. Come on, its not approved. Sour is a hobby! I recommend using full stored procedures to implem

23、ent data logic and transaction logic!Lets talk about the formatting of SP (my personal programming habits). Good habits help maintain the day after.Create Proc spBuyBook (- storage process head, including name, parameters, documentationiBookID int, ID - parameter bookiOperatorID int - Operator ID)-

24、documentation-Name Name: spBuyBook -func: business logic stored procedure for a book function-Return: -1, 0, right; did not find the book; -2, update Book -3. error; the return value to explain.-Use: spDoSomething, spDoSomething2. The reference. the external program, such as SP, FN, VW etc.-User: la

25、zybone the stored procedure users-Author: SapphireStudio () # lazybone author-Date: 2003-5-4 last updated date-Memo: for a short time, lets make a Sample. No debugging. note-As - program startBeginBegin Tran - activation affairsExec spDoSomething - call other spIf Error0 - to determine whether error

26、sBeginRollback Tran - rollback transactionRaisError (SQL SERVER),An error occurred in spBuyBook: calling spDoSomeThing. , 16, 1) with Log - logReturn -1 - error numberEnd. - - more of the other codeCommit Tran - commit transactionEndShit, why am I carrying my back like this? When does not crash, but

27、 at this time! Lost a lot. : (3 minutes of silence below.1.2.3.All right, go on! Recall what you just wrote, ing.AA, several elements of the stored procedure: the A. parameter, the B. variable, the C. statement, the D. return value, the E. management, the stored procedureBB and more advanced program

28、ming elements: A. systems, stored procedures, B. system tables, C. exception handling, D. temporary tables, e. dynamic SQL, F. extended stored procedures, and g.DBCC commandsAA.a parameter: the main points of knowledge include input parameter, output parameter and parameter default valueSample:Creat

29、e, Proc, spTesti, int, =0 - enter parameterso, int, output - output parameters)AsSet o=i*2 - value the output parameterUse the Sample:Declare o intExec spTest 33, o outputSelect o - at this point, o should be equal to 33*2=66.-The above code is not tested, written by hand. I hope it doesnt go wrong:

30、)# lazybone - SapphireStudioWonderful world, in the 3 legged software network ()!-AA.b variables: there are already instances of declared variables in AA.a, that is, Declare, o, intAA.c statement: in Sql Server, it is unthinkable to use only standard SQL statements, which are generally considered as

31、 standard SQL statements, such as:Select, Update, DeleteTherefore, we need to introduce more powerful features, that is, the T-SQL statement:Assignment statement: SetLoop statement: WhileBranch statement: if, Case (Case statement cannot be used alone, different from general high-level language)Lets

32、give you an example:Sample:Declare i intSet i=0While i100BeginIf i=20BeginSelect Case Cast (i As Float) /2 When (i/2) then (Cast i As varchar (3) + doubleElse (Cast i As varchar (3) + oddEndEndSet i=i+1End-The above code to determine the singular and plural within 20.# lazybone - SapphireStudioWonde

33、rful world, in the 3 legged software network ()!-AA.d return valueSample:Create Proc spTest2AsReturn 22Use the SampleDeclare i intExec i=spTest2Select iAA.e manages stored procedures: creating, modifying, deleting.Respectively as:Create, Proc., Alter, Proc. Drop, Proc.BB and more advanced programmin

34、g elements: A. systems, stored procedures, B. system tables, C. exception handling, D. temporary tables, e. dynamic SQL, F. extended stored procedures, and g.DBCC commandsHa ha, the following courses are charged! (joking. Actually, Im going to put it in the back.)3, function.The function is a new fe

35、ature of SQL 2000. General programming languages have functions, so I dont have to explain what the function is. :)Perhaps a lot of friends will ask, can I use stored procedures? Why do I use functions?One point specifically pointed out here: FN can be nested in Select statements, and SP cannot.Here

36、 is not going to very much a cursor, of course, in my program, basically abandoned the cursor (here that is basic! Because there are still many places to pay, the guide list is not allowed Instead, the FN was adopted. The cursor is too consuming resources. Cannot stand. I was almost moved to tears.F

37、N is actually much simpler than sp. Because of its uncertainty,It also made him suffer a lot of restrictions.A small particle that functions as a function:Create Function fnTest (i int)Returns bitAsBeginDeclare b bitIf (Cast (i, As, Float) /2 = = (i/2)Set b= 1ElseSet b= 0Return bEnd-The above code w

38、hether i is odd or even.# lazybone - SapphireStudioWonderful world, in the 3 legged software network ()!-Use the Sample:Create Table #TT (FD1 int)Declare i intSet i=0While i=20BeginInsert, Into, #tt, Values (i)Set i=i+1EndSelect fd1,whether even =dbo.fnTest (FD1) - here calling the function, pay att

39、ention to Kazakhstan: be sure to add his owner. function beforeFrom #ttDrop Table #tt-The code above a virtual data, and then determine the data in the table is odd or even.# lazybone - SapphireStudioWonderful world, in the 3 legged software network ()!-With SP programming basics, writing FN is not

40、a hard job. As I mentioned earlier, FN is restricted, and here is a little list:Chair1. can only call deterministic functions and cannot call uncertain functions. For example, you cannot call GetDate (), and the uncertain functions you define.Chair2. cannot use dynamic SQL. Such as: Execute, sp_Exec

41、uteSQL (this is my most painful thing, crying.)Chair3. cannot call the extended stored procedureChair4. cannot call the Update statement to update the tableChair5.You cant create tables (Create, TAble) inside the function, and modify the tables (Alter, TAble)Wait. Head in. Anyway, a little unpredict

42、able consequences can not be returned, the consequences can not be used.5. transactionWhat is business? These are the terminology specific to the database. Lazybone here verbal explanation: it is the many things as a thing to deal with. That is, we are on the same boat, to live together, to over, ov

43、er together!Why should I use transactions? Im here to give you a very vulgar and very vulgar example:I go to the bank to save money, so there are so many steps:1, the money to the staff; 2, staff fill out the form; 3, the list for me to sign; 4, staff confirmed and entered the computer.If, if I give

44、 the money to the staff, then go to 3, I sign it. That guy suddenly had a heart attack, and over dropped. Then I didnt enter my computer yet, but I paid the money and signed it, and I didnt have any other records. Am I not going to die? My hard-earned money! Give it back to me!Thus, the term Transaction is created in the database, that is, either succeed, fail, and restore to the original state.Or write programs to:Create, Proc, SP, Im going to deposit (M,