4, stored procedure.
The stored procedure is the most important way of expressing database programming.
Oh, here I want to mention the last time: I refuse to use the trigger. Here I am starting to have a trigger.
In SQL 2000, tell the truth, I really can't find the reason for the trigger. Recall: The trigger is a special stored procedure. It is automatically executed in certain events (INSERT, UPDATE, DELETE, etc.). I recommend using SP and cascading instead of triggers.
In SQL 7, triggers are typically used to update, or delete data for related tables to maintain data. SQL 7, there is no capabilities that delete and cascade and cascaded modifications. Only the relationship can only be established. Since SQL 2000 provides cascading, there is no good reason for the trigger. More in the case of existence as a downwardly compatible technique.
Of course, some people like to use the trigger as the processing data logic, even the automatic stored procedure of business logic. This method is not enough. Here is some of the disadvantages of the following triggers:
a, "underground" operation.
The trigger does not have a good debugging, the management environment. Debug a trigger, more time and energy than debugging SP.
b, similar to the goto statement. (Different sayings of excessive freedom are: no governmentism!)
A table, you can write multiple triggers, including 10 triggers that are the same for Update! The same 10 triggers for the delete. In other words, you have to write this table every time, you have to check your trigger, see what they are doing, there is a conflict.
Perhaps, you will be very cattle B. I said to me: I won't be so silly B, I remember what I did! 3 months later? 10 months later? Will you tell me what you remember?
C, nested trigger, recursive trigger
You dare to say that you don't have Table1 updated Table2 table in so much trigger, which triggers table2 table update Table3, Table3 trigger triggers Table1 update Table2
Perhaps this happens: Your program updates Table1.fd1, trigger Rapida updates Table1.fd1, trigger event again, the trigger updates Table1.fd1 again ...
Of course, SQL Server can set up and avoid applications to enter the dead cycle, but the result is obtained, maybe it is not what you want.
I can't think of more harm of trigger, because I have already abandoned it. Forget it, do not batch it, acid is all people hobby! I recommend using a fully stored procedure to implement data logic and transaction logic!
Let's talk about SP writing format (I personally program habits). Good habits contribute to future maintenance.
Create Proc SpbuyBook (- @@ stored procedure head, including name, parameter, description document
@ibookid int, - book ID - @@ parameter
@ioperatorid Int - Operator ID
)
-------------------------------------------------- ----- @@ instruction document
--Name: spbuybook @@name
--Func: The function of purchasing a book business logic @@ stored procedure
- Return: 0, correct; -1, did not find this book; -2, update Book expression error; -3 ..... @@ return value interpretation - ISE: SPDoSomething, SPDoSomething2 .... @@ reference Those external procedures, such as SP, FN, VW, etc.
--User: True @@ This stored procedure
--Author: lazy # sapphirestudio (www.chair3.com) @@
--Date: 2003-5-4 @@ Last Update Date
--Memo: Temporarily writing, give everyone a SAMPLE. No debugging. @@ 注
-------------------------------------------------- -----
AS - @@ program start
Begin
Begin Tran - @@ activation
EXEC SPDoSomething - @@ calls other SP
IF @@ error <> 0 - @@ judgment error
Begin
Rollback Tran - @@ 回滚 事 事
Raiserror ('SQL Server, SpBuybook: calls spdosomething error.', 16, 1) with log - @@ record log
Return -1 - @@ 回 错 错 号
end
.... - More Other Codes
Commit tran - @@ Submit transaction
end
Ok, continue! Recall the content you wrote ing ...
AA, several elements of the stored procedure: a. Parameter b. Variable c. Statement D. Return Value E. Manage Store BB, More Advanced Programming Features: a. System Stored Procedure B. System Table C. Exception D. Temporary table e. Dynamic SQL F. Extended Storage Procedure G.dbcc Command AA.A.A Parameters: Input Parameters, Output Parameters, Parameter Default Sample: Create Proc SPTEST (@i int = 0, - Input Parameters @ o Int output - Output parameter) AS set @ o @ i * 2 - 付 输 输数: Declare @o int exec sptest 33, @ o output select @o - At this point @o should be equal to 33 * 2 = 66. -------------------------------------------------- -------------------- The above code is not tested, and write it hand. I hope that I will not be wrong :) aa.b variable: AA.A has a declared variable, that is, DECLARE @o int aa.c statement: In SQL Server, if only the standard SQL statement will be unimaginable, It is usually considered that the standard SQL statement is then there, such as: select, update, delete, therefore, we need to introduce more more powerful features, that is, T-SQL statement: assignment statement: SET loop statement: while branch statement: IF Case (CASE statement can not be used separately, together with the general high-level language): Sample: Declare @i int set @ i = 0
While @i <100 Begin if @i <= 20 Begin Select Case Cast (@i as float) / 2 when (@i as varchar (3)) 'is a double number' else CAST (@i as varchar (3)) 'is a single number' end end @ i = @ i 1 end ------------------------- --------------------------------------------- The above code judgment 20 Single number and double number. -------------------------------------------------- -------------------- AA.D Return Value Sample: CREATE PROC SPTEST2 AS RETURN 22
Use the sample declare @i int exec @ i = SPTEST2 Select @i aa.e Manage stored procedure: Create, modify, delete. CREATE PROC ..., ALTER Proc ..., Drop Proc ... BB, more advanced programming elements: a. System store procedure b. System Table c. Exception Process D. Temporary Table E. Dynamic SQL F Extended stored procedure g.dbcc command haha, the following course charges! ! (Joke, I actually plan to put it behind.) 3, function. The function is a new feature of SQL 2000. The general programming language has functions, I don't have to explain what the function is. :) Perhaps many friends will ask: I can't use the stored procedure. Why should I use a function? It is particularly pointed here: Fn can be nested in a SELECT statement, while SP cannot be. It is not planning a large number of special cursors. Of course, in my program, basically abandon the cursor (specifically explained here, "Basic"! Because there are still many local cost guides, it is not possible.) Fn. Cursors are too resource. Can't stand it ... I am going to be tears ... FN is actually much simpler than SP. Because of its uncertainty, it has also made him a lot of limitations. Lifting a function of small particles: Create function fntest (@i int) Returns bit as begin declare @B bit if (Cast (@i as float) / 2) = (@ I / 2) set @ b = 1 else set @ B = 0 return @B end ------------------------------------------------------------------------------------------------------- --------------------------- The above code is judged @i is the number or double number. Use the sample: create table #tt (fd1 int) Declare @i int set @ i = 0 while @i <= 20 begin insert into #tt values (@i) set @ i = @ i 1 End SELECT FD1, ' Whether the double number '= dbo.fntest (fd1) - call the function here, note that the function must be added to his Owner. From #tt
Drop Table #tt ---------------------------------------------- ------------------------ The above code virtual segment, then determines that the data table is singular or double number. With the SP programming foundation, it is not difficult to write FN. I mentioned it just now, FN is limited, here is a slight list: chair1. You can only call the deterministic function, you cannot call the uncertain function. For example, you cannot call getDate (), as well as your own uncertainties. CHAIR2. Dynamic SQL cannot be used. Such as: execute, sp_executesql (this is my most painful thing, crying ...) Chair3. Cannot call the extended stored procedure Chair4. Cannot call the UPDATE statement to update Chair5. Create a table inside the function (CREATE Table), modification table (ALTER TABLE) 5. What is the transaction? These are the specific terms unique to the database. The lazy worm is explained here: it is to treat multiple things as a thing. That is, everyone is on a boat, to live together, Over is over! Why should I use a transaction? Here, I have a very vulgar example: I have saved money to banks, so there are such steps: 1. Give money to staff; 2, staff will fill the order; 3, sign me the list; 4 , The staff confirmed and entered the computer. If so, after I handed the money to the staff, I was signed by 3 I signed. The buddy suddenly had a heart attack, OVER lost, then my money has not yet entered the computer, but I have paid the money and sign confirmed, and there is no other record. Don't I miss it? ? ? My hard-earned money! Hurry back to me! ! Thus, such a term in the database: transaction, or success, either failure, and restore the original. Or writer: Create Proc sp I go to deposit (@m Money, @ioperator int) as begin Declare @i int start - activation transaction EXEC @ i = SP pay @ m, @ Ioperator if @i <> 0 - This is generally used in system error @@ error. I haven't used the example here. Need to be based on the actual situation.
