SQL--預存程序+觸發器 對比！

標籤：

一、預存程序

一：預存程序：預存程序是一組為了完成特定功能的SQL 語句集，經編譯後儲存在資料庫中。

     可以用預存程序名字和參數來調用預存程序，這樣可以避免代碼重複出現，用起來也方便。

 例：    下面是定義了一個名為Buyfruit的預存程序，參數為購買人的姓名，水果名稱，購買數量三個，此預存程序的作用是，輸入了這三個參數之後，判斷賬戶餘額和庫存是否足夠，足夠的話將賬戶餘額減掉花費，將庫存減掉購買的數量顯示出來，列印一個訂單，和一個明細。

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

create PROCEDURE BuyFruit     @username varchar(20),     @fruitname varchar(20),      @buycount int =  0 AS BEGIN     declare @kc int,@price float,@fruitid varchar(20)     --先把該水果的庫存量找出來     select @fruitid=ids, @kc = numbers,@price=price from fruit where [email protected]           --根據購買數量和庫存的關係，進行購買     if @buycount < @kc     begin         declare @money decimal(18,2)         select @money = account from login where [email protected] --根據使用者名稱找到賬戶餘額         if(@money > @price*@buycount)         begin             update login set [email protected]*@buycount where [email protected]             update fruit set numbers = [email protected] where name[email protected]             declare @ordercode varchar(50)             set @ordercode =‘O‘+cast(getdate() as varchar(50))             insert into orders values(@ordercode,@username,GETDATE())             insert into orderdetails values(@ordercode,@fruitid,@buycount)         end         else         begin             print ‘餘額不足‘         end     end     else     begin         print ‘庫存不足‘     end END

 購買之前資料庫中的內容：

 

購買成功之後資料庫中儲存的內容:

 

添加到訂單的和購買明細：

 

二：觸發器

觸發器是一種特殊的預存程序

觸發器主要是通過事件進行觸發而被自動執行的，而預存程序可以通過預存程序名字而被直接調用

觸發器的主要作用就是其能夠實現由主鍵和外鍵所不能保證的複雜的參照完整性和資料的一致性，另外還有強化約束和級聯啟動並執行功能。

關於inserted和deleted暫存資料表

這兩個表是由系統管理的，儲存在記憶體中，不是儲存在資料庫中，因此不允許使用者直接對其修改，是唯讀，系統在執行插入操作的時候先將資料插入到inserted暫存資料表中，然後再向資料庫的表中插入，在插入下一條時這條被刪除；執行刪除操作的時候，先將資料傳到deleted表裡，再刪除資料，起到一個儲存臨時資料用來恢複或者記錄的作用。

 

下面這個是做了一個刪除時觸發的觸發器，在刪除student表中資料時，將刪除的這一行插入到biandong表裡面

--用於刪除觸發的觸發器：

create trigger TR_STUDENT_DELETE
on student
for delete --for觸發器after觸發器，刪除後觸發
as
declare @no varchar(3),@name varchar(4)
select @no=sno,@name=sname from deleted --用到了暫存資料表
insert into biandong values(@no,@name,‘100‘)
go

--下面執行刪除的時候觸發上面的程式，

delete from student where sname=‘猴子‘

 

還有一種是instead of觸發，觸發的時候用觸發器裡面的程式代替執行操作，即執行觸發器裡面的東西

下面例子，原來三個表，由info表裡的code約束另外兩個表，因此沒法單獨刪除info中的某一行，利用觸發器可以刪除三個表中code為p001的行

 

create trigger TR_INFO_DELETE
on info
instead of delete --instead of 觸發器，刪除的時候替代執行觸發器

as
declare @code varchar(20)
select @code=code from deleted
delete from family where [email protected]
delete from work where [email protected]
delete from info where [email protected]

go

instead of 觸發器建立完成下面開始觸發：

delete from INFO where name =‘胡軍‘

此時刪除了三個表中p001的行

 

D、刪除觸發器：

drop trigger TR_INFO_DELETE

SQL--預存程序+觸發器 對比！