如何使用MS SQL的Stored Procedure與Trigger (2)

°★Shadow御､

上篇大致說明了MySQL的Stored Procedure與Trigger, 現在來談談Microsoft的SQL server (以SQL 2000為例, 2003/2005則大同小異)

雖然都是database的stored procedure與trigger,但是語法卻不同,另人頗為頭痛

Microsoft SQL的stored procedure主要以Transaction SQL語法 (T-SQL),除了procedure語法差異外, SQL語法本身也略有不同 ...


MS SQL使用 SELECT TOP 20 * FROM MY_TABLE
MySQL使用 SELECT * FROM MY_TABLE LIMIT 0,20

以上都是挑選資料中的前20筆資料

MS SQL使用LCASE,UCASE,INSTR,MID等函數來處理字串, 但MySQL與Oracle則使用LOWER, UPPER, LOCATE, SUBSTRING ... (MySQL string function reference, MySQL 5.0 reference)

Microsft走的路線以T-SQL及VB語法的方式, 因此只要T-SQL/VB熟悉, SQL server就易如反掌了 ... 因此筆者都以ANSI-SQL, T-SQL, VB, PHP這些概念將MySQL, Oracle, MS SQL一次通通學習起來




我們來建立一個簡單的例子:

CREATE PROCEDURE TEST_ADD
@emp_no varchar(10)
AS
SET NOCOUNT ON
BEGIN TRAN
INSERT testtable (emp_no) VALUES (@emp_no);
COMMIT TRAN
GO

以上紅色的字是依需要修改

有了這個stored procedure後就可以執行
EXEC TEST_ADD('0000000001')

SET NOCOUNT ON主要是希望不需回覆筆數
也就是"停止在部份結果集中傳回顯示Transact-SQL 陳述式或預存程序所影響之資料列數的訊息"

但是如果你希望執行本機stored procedure去操作遠端機器database

就必須改成

CREATE PROCEDURE TEST_ADD
@emp_no varchar(10)
AS

SET NOCOUNT ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET XACT_ABORT ON
BEGIN TRAN
INSERT LinkedServerName.master.dbo.testtable (emp_no) VALUES (@emp_no);
COMMIT TRAN
GO

並且不能在SQL Server Enterprise Manager下去新增stored Procedured
而要在Query Analyzer (前文提過: http://okayro.com/thread-113643-1-1.html)

以下是使用到Linked Server必須宣告的:

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET XACT_ABORT ON

(2)Trigger

建立SQL Server的Trigger首先打開SQL Server Enterprise Manager, 選取要建立trigger的table, 按下滑鼠右鑑 ->所有工作 -> 管理觸發程序 (畫面如下)


(1)Stored Procedure

建立SQL Server的stored procedure首先打開SQL Server Enterprise Manager, 選取預存程序, 按下滑鼠右鑑 ->新增預存程序 (畫面如下)

然後鍵入

CREATE TRIGGER trigger_name ON tableName
FOR INSERT
AS
SET NOCOUNT ON
DECLARE @newSeq_id VARCHAR(10)
SELECT @newSeq_id = (SELECT seq_id FROM Inserted)
UPDATE TableName2 set counter=counter+1 where seq_id = @newSeq_id

以上就是做: 當表格有任何資料插入, 則去另一表格對應欄位去累加

如果如同上面說的, 要運作到遠端的Linked Server, 除了要建立linked server外, 程序要改成:

CREATE TRIGGER trigger_name ON tableName
FOR INSERT
AS

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET NOCOUNT ON
SET XACT_ABORT ON

DECLARE @newSeq_id VARCHAR(10)
SELECT @newSeq_id = (SELECT seq_id FROM Inserted)
UPDATE LinkedServerName.master.dbo.TableName2 set counter=counter+1 where seq_id = @newSeq_id

以上只是大略敘述使用方式, 至於UPDATE/INSERT/DELETE以及更深的T-SQL就要自己再研究啦 ...