亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

SQL觸發器實例講解

系統 1851 0

SQL觸發器實例1

定義: 何為觸發器?在SQL Server里面也就是對某一個表的一定的操作,觸發某種條件,從而執行的一段程序。觸發器是一個特殊的存儲過程。?

????? 常見的觸發器有三種:分別應用于Insert , Update , Delete 事件。?

????? 我為什么要使用觸發器?比如,這么兩個表:?

????? Create Table Student(????????????? --學生表?

??????? StudentID int primary key,?????? --學號?

??????? ....?

?????? )?

????? Create Table BorrowRecord(?????????????? --學生借書記錄表?

??????? BorrowRecord?? int identity(1,1),?????? --流水號???

??????? StudentID????? int ,??????????????????? --學號?

??????? BorrowDate???? datetime,??????????????? --借出時間?

??????? ReturnDAte???? Datetime,??????????????? --歸還時間?

??????? ...?

????? )?

???? 用到的功能有:?

??????? 1.如果我更改了學生的學號,我希望他的借書記錄仍然與這個學生相關(也就是同時更改借書記錄表的學號);?

??????? 2.如果該學生已經畢業,我希望刪除他的學號的同時,也刪除它的借書記錄。?

???? 等等。?

???? 這時候可以用到觸發器。對于1,創建一個Update觸發器:?

???? Create Trigger truStudent?

?????? On Student???????????????????????? --在Student表中創建觸發器?

?????? for Update????????????????????????? --為什么事件觸發?

???? As??????????????????????????????????????? --事件觸發后所要做的事情?

?????? if Update(StudentID)????????????

?????? begin?

???????? Update BorrowRecord?

?????????? Set StudentID=i.StudentID?

?????????? From BorrowRecord br , Deleted?? d ,Inserted i????? --Deleted和Inserted臨時表?

?????????? Where br.StudentID=d.StudentID?

?????? end????????

?????????????????

???? 理解觸發器里面的兩個臨時的表:Deleted , Inserted 。注意Deleted 與Inserted分別表示觸發事件的表“舊的一條記錄”和“新的一條記錄”。?

???? 一個數據庫系統中有兩個虛擬表用于存儲在表中記錄改動的信息,分別是:?

???????????????????????????? 虛擬表Inserted???????????????????? 虛擬表Deleted?

在表記錄新增時???? 存放新增的記錄???????????????????????? 不存儲記錄?

???????? 修改時?????????? 存放用來更新的新記錄?????????????????? 存放更新前的記錄?

???????? 刪除時?????????? 不存儲記錄???????????????????????????? 存放被刪除的記錄?

???? 一個Update 的過程可以看作為:生成新的記錄到Inserted表,復制舊的記錄到Deleted表,然后刪除Student記錄并寫入新紀錄。?

???? 對于2,創建一個Delete觸發器?

???? Create trigger trdStudent?

?????? On Student?

?????? for Delete?

???? As?

?????? Delete BorrowRecord?

???????? From BorrowRecord br , Delted d?

???????? Where br.StudentID=d.StudentID?

???? 從這兩個例子我們可以看到了觸發器的關鍵:A.2個臨時的表;B.觸發機制。?

SQL觸發器實例2

/*?

建立虛擬測試環境,包含:表[卷煙庫存表],表[卷煙銷售表]。?

請大家注意跟蹤這兩個表的數據,體會觸發器到底執行了什么業務邏輯,對數據有什么影響。?

為了能更清晰的表述觸發器的作用,表結構存在數據冗余,且不符合第三范式,這里特此說明。?

*/?

USE Master?

GO?

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ’U’ AND NAME = ’卷煙庫存表’)?

DROP TABLE 卷煙庫存表?

GO?

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ’U’ AND NAME = ’卷煙銷售表’)?

DROP TABLE 卷煙銷售表?

GO?

--業務規則:銷售金額 = 銷售數量 * 銷售單價 業務規則。?

CREATE TABLE 卷煙銷售表?

(?

卷煙品牌 VARCHAR(40) PRIMARY KEY NOT NULL,?

購貨商 VARCHAR(40) NULL,?

銷售數量 INT NULL,?

銷售單價 MONEY NULL,?

銷售金額 MONEY NULL?

)?

GO?

--業務規則:庫存金額 = 庫存數量 * 庫存單價 業務規則。?

CREATE TABLE 卷煙庫存表?

(?

卷煙品牌 VARCHAR(40) PRIMARY KEY NOT NULL,?

庫存數量 INT NULL,?

庫存單價 MONEY NULL,?

庫存金額 MONEY NULL?

)?

GO?

--創建觸發器,示例1?

/*?

創建觸發器[T_INSERT_卷煙庫存表],這個觸發器較簡單。?

說明: 每當[卷煙庫存表]發生 INSERT 動作,則引發該觸發器。?

觸發器功能: 強制執行業務規則,保證插入的數據中,庫存金額 = 庫存數量 * 庫存單價。?

注意: [INSERTED]、[DELETED]為系統表,不可創建、修改、刪除,但可以調用。?

重要: 這兩個系統表的結構同插入數據的表的結構。?

*/?

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ’TR’ AND NAME = ’T_INSERT_卷煙庫存表’)?

DROP TRIGGER T_INSERT_卷煙庫存表?

GO?

CREATE TRIGGER T_INSERT_卷煙庫存表?

ON 卷煙庫存表?

FOR INSERT?

AS?

--提交事務處理?

BEGIN TRANSACTION?

--強制執行下列語句,保證業務規則?

UPDATE 卷煙庫存表?

SET 庫存金額 = 庫存數量 * 庫存單價?

WHERE 卷煙品牌 IN (SELECT 卷煙品牌 from INSERTED)?

COMMIT TRANSACTION?

GO?

/*?

針對[卷煙庫存表],插入測試數據:?

注意,第一條數據(紅塔山新勢力)中的數據符合業務規則,?

第二條數據(紅塔山人為峰)中,[庫存金額]空,不符合業務規則,?

第三條數據(云南映像)中,[庫存金額]不等于[庫存數量]乘以[庫存單價],不符合業務規則。?

第四條數據庫存數量為0。?

請注意在插入數據后,檢查[卷煙庫存表]中的數據是否 庫存金額 = 庫存數量 * 庫存單價。?

*/?

INSERT INTO 卷煙庫存表(卷煙品牌,庫存數量,庫存單價,庫存金額)?

SELECT ’紅塔山新勢力’,100,12,1200 UNION ALL?

SELECT ’紅塔山人為峰’,100,22,NULL UNION ALL?

SELECT ’云南映像’,100,60,500 UNION ALL?

SELECT ’玉溪’,0,30,0?

GO?

--查詢數據?

SELECT * FROM 卷煙庫存表?

GO?

/*?

結果集?

RecordId 卷煙品牌 庫存數量 庫存單價 庫存金額?

-------- ------------ -------- ------- ---------?

1 紅塔山新勢力 100 12.0000 1200.0000?

2 紅塔山人為峰 100 22.0000 2200.0000?

3 云南映像 100 60.0000 6000.0000?

4 玉溪 0 30.0000 .0000?

(所影響的行數為 4 行)?

*/?

--觸發器示例2?

/*?

創建觸發器[T_INSERT_卷煙銷售表],該觸發器較復雜。?

說明: 每當[卷煙庫存表]發生 INSERT 動作,則引發該觸發器。?

觸發器功能: 實現業務規則。?

業務規則: 如果銷售的卷煙品牌不存在庫存或者庫存為零,則返回錯誤。?

否則則自動減少[卷煙庫存表]中對應品牌卷煙的庫存數量和庫存金額。?

*/?

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ’TR’ AND NAME = ’T_INSERT_卷煙銷售表’)?

DROP TRIGGER T_INSERT_卷煙銷售表?

GO?

CREATE TRIGGER T_INSERT_卷煙銷售表?

ON 卷煙銷售表?

FOR INSERT?

AS?

BEGIN TRANSACTION?

--檢查數據的合法性:銷售的卷煙是否有庫存,或者庫存是否大于零?

IF NOT EXISTS (?

SELECT 庫存數量?

FROM 卷煙庫存表?

WHERE 卷煙品牌 IN (SELECT 卷煙品牌 FROM INSERTED)?

)?

BEGIN?

--返回錯誤提示?

RAISERROR(’錯誤!該卷煙不存在庫存,不能銷售。’,16,1)?

--回滾事務?

ROLLBACK?

RETURN?

END?

IF EXISTS (?

SELECT 庫存數量?

FROM 卷煙庫存表?

WHERE 卷煙品牌 IN (SELECT 卷煙品牌 FROM INSERTED) AND?

庫存數量 <= 0?

)?

BEGIN?

--返回錯誤提示?

RAISERROR(’錯誤!該卷煙庫存小于等于0,不能銷售。’,16,1)?

--回滾事務?

ROLLBACK?

RETURN?

END?

--對合法的數據進行處理?

--強制執行下列語句,保證業務規則?

UPDATE 卷煙銷售表?

SET 銷售金額 = 銷售數量 * 銷售單價?

WHERE 卷煙品牌 IN (SELECT 卷煙品牌 FROM INSERTED)?

DECLARE @卷煙品牌 VARCHAR(40)?

SET @卷煙品牌 = (SELECT 卷煙品牌 FROM INSERTED)?

DECLARE @銷售數量 MONEY?

SET @銷售數量 = (SELECT 銷售數量 FROM INSERTED)?

UPDATE 卷煙庫存表?

SET 庫存數量 = 庫存數量 - @銷售數量,?

庫存金額 = (庫存數量 - @銷售數量)*庫存單價?

WHERE 卷煙品牌 = @卷煙品牌?

COMMIT TRANSACTION?

GO?

--請大家自行跟蹤[卷煙庫存表]和[卷煙銷售表]的數據變化。?

--針對[卷煙銷售表],插入第一條測試數據,該數據是正常的。?

INSERT INTO 卷煙銷售表(卷煙品牌,購貨商,銷售數量,銷售單價,銷售金額)?

SELECT ’紅塔山新勢力’,’某購貨商’,10,12,1200?

GO?

--針對[卷煙銷售表],插入第二條測試數據,該數據 銷售金額 不等于 銷售單價 * 銷售數量。?

--觸發器將自動更正數據,使 銷售金額 等于 銷售單價 * 銷售數量。?

INSERT INTO 卷煙銷售表(卷煙品牌,購貨商,銷售數量,銷售單價,銷售金額)?

SELECT ’紅塔山人為峰’,’某購貨商’,10,22,2000?

GO?

--針對[卷煙銷售表],插入第三條測試數據,該數據中的卷煙品牌在 卷煙庫存表中找不到對應。?

--觸發器將報錯。?

INSERT INTO 卷煙銷售表(卷煙品牌,購貨商,銷售數量,銷售單價,銷售金額)?

SELECT ’紅河V8’,’某購貨商’,10,60,600?

GO?

/*?

結果集?

服務器: 消息 50000,級別 16,狀態 1,過程 T_INSERT_卷煙銷售表,行 15?

錯誤!該卷煙不存在庫存,不能銷售。?

*/?

--針對[卷煙銷售表],插入第三條測試數據,該數據中的卷煙品牌在 卷煙庫存表中庫存為0。?

--觸發器將報錯。?

INSERT INTO 卷煙銷售表(卷煙品牌,購貨商,銷售數量,銷售單價,銷售金額)?

SELECT ’玉溪’,’某購貨商’,10,30,300?

GO?

/*?

結果集?

服務器: 消息 50000,級別 16,狀態 1,過程 T_INSERT_卷煙銷售表,行 29?

錯誤!該卷煙庫存小于等于0,不能銷售。?

*/?

--查詢數據?

SELECT * FROM 卷煙庫存表?

SELECT * FROM 卷煙銷售表?

GO?

/*?

補充:?

1、本示例主要通過一個簡單的業務規則實現來進行觸發器使用的說明,具體的要根據需要靈活處理;?

2、關于觸發器要理解并運用好 INSERTED ,DELETED 兩個系統表;?

3、本示例創建的觸發器都是 FOR INSERT ,具體的語法可參考:?

Trigger語法?

CREATE TRIGGER trigger_name?

ON { table | view }?

[ WITH ENCRYPTION ] --用于加密觸發器?

{?

{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }?

[ WITH APPEND ]?

[ NOT FOR REPLICATION ]?

AS?

[ { IF UPDATE ( column )?

[ { AND | OR } UPDATE ( column ) ]?

[ ...n ]?

| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )?

{ comparison_operator } column_bitmask [ ...n ]?

} ]?

sql_statement [ ...n ]?

}?

}?

4、關于觸發器,還應該注意?

(1)、DELETE 觸發器不能捕獲 TRUNCATE TABLE 語句。?

(2)、觸發器中不允許以下 Transact-SQL 語句:?

ALTER DATABASE CREATE DATABASE DISK INIT?

DISK RESIZE DROP DATABASE LOAD DATABASE?

LOAD LOG RECONFIGURE RESTORE DATABASE?

RESTORE LOG?

(3)、觸發器最多可以嵌套 32 層。?

*/?

--修改觸發器?

--實質上,是將 CREATE TRIGGER ... 修改為 ALTER TRIGGER ...即可。?

--刪除觸發器?

DROP TRIGGER xxx?

GO?

--刪除測試環境?

DROP TABLE 卷煙庫存表?

GO?

DROP TABLE 卷煙銷售表?

GO?

DROP TRIGGER T_INSERT_卷煙庫存表?

GO?

DROP TRIGGER T_INSERT_卷煙銷售表?

GO?

##################################################################?

觸發器的基礎知識和例子?

:create trigger tr_name?

on table/view?

{for | after | instead of } [update][,][insert][,][delete]?

[with encryption]?

as {batch | if update (col_name) [{and|or} update (col_name)] }?

說明:?

1 tr_name :觸發器名稱?

2 on table/view :觸發器所作用的表。一個觸發器只能作用于一個表?

3 for 和after :同義?

4 after 與instead of :sql 2000新增項目afrer 與 instead of 的區別?

After?

在觸發事件發生以后才被激活,只可以建立在表上?

Instead of?

代替了相應的觸發事件而被執行,既可以建立在表上也可以建立在視圖上?

5 insert、update、delete:激活觸發器的三種操作,可以同時執行,也可選其一?

6 if update (col_name):表明所作的操作對指定列是否有影響,有影響,則激活觸發器。此外,因為delete 操作只對行有影響,?

所以如果使用delete操作就不能用這條語句了(雖然使用也不出錯,但是不能激活觸發器,沒意義)。?

7 觸發器執行時用到的兩個特殊表:deleted ,inserted?

deleted 和inserted 可以說是一種特殊的臨時表,是在進行激活觸發器時由系統自動生成的,其結構與觸發器作用的表結構是一?

樣的,只是存放 的數據有差異。?

續?

下面表格說明deleted 與inserted 數據的差異?

deleted 與inserted 數據的差異?

Inserted?

存放進行insert和update 操作后的數據?

Deleted?

存放進行delete 和update操作前的數據?

注意:update 操作相當于先進行delete 再進行insert ,所以在進行update操作時,修改前的數據拷貝一條到deleted 表中,修改后?

的數據在存到觸發器作用的表的同時,也同時生成一條拷貝到insered表中

?

?

?

SQL Server 2000中的觸發器使用

?

觸發器是數據庫應用中的重用工具,它的應用很廣泛。這幾天寫一個化學數據統計方面的軟件,需要根據采樣,自動計算方差,在這里,我使用了觸發器。

  下面我摘錄了SQL Server官方教程中的一段關于觸發器的文字,確實有用的一點文字描述。

  可以定義一個無論何時用INSERT語句向表中插入數據時都會執行的觸發器。

  當觸發INSERT觸發器時,新的數據行就會被插入到觸發器表和inserted表中。inserted表是一個邏輯表,它包含了已經插入的數據行的一個副本。inserted表包含了INSERT語句中已記錄的插入動作。inserted表還允許引用由初始化INSERT語句而產生的日志數據。觸發器通過檢查inserted表來確定是否執行觸發器動作或如何執行它。inserted表中的行總是觸發器表中一行或多行的副本。

  日志記錄了所有修改數據的動作(INSERT、UPDATE和DELETE語句),但在事務日志中的信息是不可讀的。然而,inserted表允許你引用由INSERT語句引起的日志變化,這樣就可以將插入數據與發生的變化進行比較,來驗證它們或采取進一步的動作。也可以直接引用插入的數據,而不必將它們存儲到變量中。

  示例

  在本例中,將創建一個觸發器。無論何時訂購產品(無論何時向Order Details表中插入一條記錄),這個觸發器都將更新Products表中的一列(UnitsInStock)。用原來的值減去訂購的數量值即為新值。

?

?

?

USE Northwind

?

CREATE TRIGGER OrdDet_Insert

ON [Order Details]

FOR INSERT

AS

UPDATE P SET

UnitsInStock = P.UnitsInStock – I.Quantity

FROM Products AS P INNER JOIN Inserted AS I

ON P.ProductID = I.ProductID

?

  DELETE觸發器的工作過程

  當觸發DELETE觸發器后,從受影響的表中刪除的行將被放置到一個特殊的deleted表中。deleted表是一個邏輯表,它保留已被刪除數據行的一個副本。deleted表還允許引用由初始化DELETE語句產生的日志數據。

  使用DELETE觸發器時,需要考慮以下的事項和原則:

  ·當某行被添加到deleted表中時,它就不再存在于數據庫表中;因此,deleted表和數據庫表沒有相同的行。

  ·創建deleted表時,空間是從內存中分配的。deleted表總是被存儲在高速緩存中。

  ·為DELETE動作定義的觸發器并不執行TRUNCATE TABLE語句,原因在于日志不記錄TRUNCATE TABLE語句。

  示例

  在本例中,將創建一個觸發器,無論何時刪除一個產品類別(即從Categories表中刪除一條記錄),該觸發器都會更新Products表中的Discontinued列。所有受影響的產品都標記為1,標示不再使用這些產品了。

?

?

?

USE Northwind

?

CREATE TRIGGER Category_Delete

ON Categories

FOR DELETE

AS

UPDATE P SET Discontinued = 1

FROM Products AS P INNER JOIN deleted AS d

ON P.CategoryID = d.CategoryID

?

  UPDATE觸發器的工作過程

  可將UPDATE語句看成兩步操作:即捕獲數據前像(before image)的DELETE語句,和捕獲數據后像(after image)的INSERT語句。當在定義有觸發器的表上執行UPDATE語句時,原始行(前像)被移入到deleted表,更新行(后像)被移入到inserted表。

  觸發器檢查deleted表和inserted表以及被更新的表,來確定是否更新了多行以及如何執行觸發器動作。

  可以使用IF UPDATE語句定義一個監視指定列的數據更新的觸發器。這樣,就可以讓觸發器容易的隔離出特定列的活動。當它檢測到指定列已經更新時,觸發器就會進一步執行適當的動作,例如發出錯誤信息指出該列不能更新,或者根據新的更新的列值執行一系列的動作語句。

  語法

?

?

?

IF UPDATE (<column_name>)

?

  例1

  本例阻止用戶修改Employees表中的EmployeeID列。

?

?

?

USE Northwind

?

GO

CREATE TRIGGER Employee_Update

ON Employees

FOR UPDATE

AS

IF UPDATE (EmployeeID)

BEGIN?

RAISERROR ('Transaction cannot be processed.\

***** Employee ID number cannot be modified.', 10, 1)

ROLLBACK TRANSACTION

END

?

?

  INSTEAD OF觸發器的工作過程

  可以在表或視圖上指定INSTEAD OF觸發器。執行這種觸發器就能夠替代原始的觸發動作。INSTEAD OF觸發器擴展了視圖更新的類型。對于每一種觸發動作(INSERT、UPDATE或 DELETE),每一個表或視圖只能有一個INSTEAD OF觸發器。

  INSTEAD OF觸發器被用于更新那些沒有辦法通過正常方式更新的視圖。例如,通常不能在一個基于連接的視圖上進行DELETE操作。然而,可以編寫一個INSTEAD OF DELETE觸發器來實現刪除。上述觸發器可以訪問那些如果視圖是一個真正的表時已經被刪除的數據行。將被刪除的行存儲在一個名為deleted的工作表中,就像AFTER觸發器一樣。相似地,在UPDATE INSTEAD OF觸發器或者INSERT INSTEAD OF觸發器中,你可以訪問inserted表中的新行。

  不能在帶有WITH CHECK OPTION定義的視圖中創建INSTEAD OF觸發器。

示例

  在本例中,創建了一個德國客戶表和一個墨西哥客戶表。放置在視圖上的INSTEAD OF觸發器將把更新操作重新定向到適當的基表上。這時發生的插入是對CustomersGer表的插入而不是對視圖的插入。

  創建兩個包含客戶數據的表:

?

?

?

SELECT * INTO CustomersGer FROM Customers WHERE Customers.Country = 'Germany'

?

SELECT * INTO CustomersMex FROM Customers WHERE Customers.Country = 'Mexico'

GO

?

  在該數據上創建視圖:

?

?

?

CREATE VIEW CustomersView AS

?

SELECT * FROM CustomersGer

UNION

SELECT * FROM CustomersMex

GO

?

  創建一個在上述視圖上的INSTEAD OF觸發器:

?

?

?

CREATE TRIGGER Customers_Update2

?

ON CustomersView

INSTEAD OF UPDATE AS

DECLARE @Country nvarchar(15)

SET @Country = (SELECT Country FROM Inserted)

IF @Country = 'Germany'

BEGIN

UPDATE CustomersGer

SET CustomersGer.Phone = Inserted.Phone

FROM CustomersGer JOIN Inserted

ON CustomersGer.CustomerID = Inserted.CustomerID

END

ELSE

IF @Country = 'Mexico'

BEGIN

UPDATE CustomersMex

SET CustomersMex.Phone = Inserted.Phone

FROM CustomersMex JOIN Inserted

ON CustomersMex.CustomerID = Inserted.CustomerID

END

?

  通過更新視圖,測試觸發器:

?

?

?

UPDATE CustomersView SET Phone = ' 030-007xxxx'?

?

WHERE CustomerID = 'ALFKI'

SELECT CustomerID, Phone FROM CustomersView?

WHERE CustomerID = 'ALFKI'

SELECT CustomerID, Phone FROM CustomersGer?

WHERE CustomerID = 'ALFKI'

?

  那么具體的講,對于多列數據,如何計算方差呢?:

?

?

?

CREATE TRIGGER [calT1T2T3] ON dbo.DCLB?

?

FOR INSERT,UPDATE

AS

update P

SET

/**//*

計算方差的觸發器

*/

P.T1=(I.P1+I.P2+I.P3+I.P4+I.P5+I.P6),

P.T2=(I.Y1+I.Y2+I.Y3+I.Y4+I.Y5+I.Y6 ),

P.T3=SQRT(P.T1*P.T1+P.T2*P.T2)

FROM DCLB AS P INNER JOIN Inserted AS I

ON P.SID = I.SID

?

  觸發器的使用很方便,而且也很簡單,重要的是理解inserted過程。可將UPDATE語句看成兩步操作:即捕獲數據前像(before image)的DELETE語句,和捕獲數據后像(after image)的INSERT語句。當在定義有觸發器的表上執行UPDATE語句時,原始行(前像)被移入到deleted表,更新行(后像)被移入到inserted表。觸發器檢查deleted表和inserted表以及被更新的表,來確定是否更新了多行以及如何執行觸發器動作。

?

?

認識了一下觸發器的作用!

?

/*

建立虛擬測試環境,包含:表[卷煙庫存表],表[卷煙銷售表]。

請大家注意跟蹤這兩個表的數據,體會觸發器到底執行了什么業務邏輯,對數據有什么影響。

為了能更清晰的表述觸發器的作用,表結構存在數據冗余,且不符合第三范式,這里特此說明。

*/?

USE Master

GO

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME = '卷煙庫存表')

DROP TABLE 卷煙庫存表

GO

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME = '卷煙銷售表')

DROP TABLE 卷煙銷售表

GO

--業務規則:銷售金額 = 銷售數量 * 銷售單價 業務規則。

CREATE TABLE 卷煙銷售表

(

卷煙品牌 VARCHAR(40) PRIMARY KEY NOT NULL,

購貨商 VARCHAR(40) NULL,

銷售數量 INT NULL,

銷售單價 MONEY NULL,

銷售金額 MONEY NULL

)

GO

--業務規則:庫存金額 = 庫存數量 * 庫存單價 業務規則。

CREATE TABLE 卷煙庫存表

(

卷煙品牌 VARCHAR(40) PRIMARY KEY NOT NULL,

庫存數量 INT NULL,

庫存單價 MONEY NULL,

庫存金額 MONEY NULL

)

GO

--創建觸發器,示例1

/*

創建觸發器[T_INSERT_卷煙庫存表],這個觸發器較簡單。

說明: 每當[卷煙庫存表]發生 INSERT 動作,則引發該觸發器。

觸發器功能: 強制執行業務規則,保證插入的數據中,庫存金額 = 庫存數量 * 庫存單價。

注意: [INSERTED]、[DELETED]為系統表,不可創建、修改、刪除,但可以調用。

重要: 這兩個系統表的結構同插入數據的表的結構。

*/

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'TR' AND NAME = 'T_INSERT_卷煙庫存表')

DROP TRIGGER T_INSERT_卷煙庫存表

GO

CREATE TRIGGER T_INSERT_卷煙庫存表

ON 卷煙庫存表

FOR INSERT

AS

--提交事務處理

BEGIN TRANSACTION

--強制執行下列語句,保證業務規則

UPDATE 卷煙庫存表

SET 庫存金額 = 庫存數量 * 庫存單價

WHERE 卷煙品牌 IN (SELECT 卷煙品牌 from INSERTED)

COMMIT TRANSACTION

GO

/*?

針對[卷煙庫存表],插入測試數據:

注意,第一條數據(紅塔山新勢力)中的數據符合業務規則,

第二條數據(紅塔山人為峰)中,[庫存金額]空,不符合業務規則,

第三條數據(云南映像)中,[庫存金額]不等于[庫存數量]乘以[庫存單價],不符合業務規則。

第四條數據庫存數量為0。

請注意在插入數據后,檢查[卷煙庫存表]中的數據是否 庫存金額 = 庫存數量 * 庫存單價。

*/

INSERT INTO 卷煙庫存表(卷煙品牌,庫存數量,庫存單價,庫存金額)

SELECT '紅塔山新勢力',100,12,1200 UNION ALL

SELECT '紅塔山人為峰',100,22,NULL UNION ALL

SELECT '云南映像',100,60,500 UNION ALL

SELECT '玉溪',0,30,0

GO

--查詢數據

SELECT * FROM 卷煙庫存表

GO

/*

結果集

RecordId 卷煙品牌 庫存數量 庫存單價 庫存金額

-------- ------------ -------- ------- ---------

1 紅塔山新勢力 100 12.0000 1200.0000

2 紅塔山人為峰 100 22.0000 2200.0000

3 云南映像 100 60.0000 6000.0000

4 玉溪 0 30.0000 .0000

(所影響的行數為 4 行)

*/

--觸發器示例2

/*

創建觸發器[T_INSERT_卷煙銷售表],該觸發器較復雜。

說明: 每當[卷煙庫存表]發生 INSERT 動作,則引發該觸發器。

觸發器功能: 實現業務規則。

業務規則: 如果銷售的卷煙品牌不存在庫存或者庫存為零,則返回錯誤。

否則則自動減少[卷煙庫存表]中對應品牌卷煙的庫存數量和庫存金額。

*/

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'TR' AND NAME = 'T_INSERT_卷煙銷售表')

DROP TRIGGER T_INSERT_卷煙銷售表

GO

CREATE TRIGGER T_INSERT_卷煙銷售表

ON 卷煙銷售表

FOR INSERT

AS

BEGIN TRANSACTION

--檢查數據的合法性:銷售的卷煙是否有庫存,或者庫存是否大于零

IF NOT EXISTS (

SELECT 庫存數量

FROM 卷煙庫存表

WHERE 卷煙品牌 IN (SELECT 卷煙品牌 FROM INSERTED)

)

BEGIN

--返回錯誤提示

RAISERROR('錯誤!該卷煙不存在庫存,不能銷售。',16,1)

--回滾事務

ROLLBACK

RETURN

END

IF EXISTS (

SELECT 庫存數量

FROM 卷煙庫存表

WHERE 卷煙品牌 IN (SELECT 卷煙品牌 FROM INSERTED) AND

庫存數量 <= 0?

)

BEGIN

--返回錯誤提示

RAISERROR('錯誤!該卷煙庫存小于等于0,不能銷售。',16,1)

--回滾事務

ROLLBACK

RETURN

END

--對合法的數據進行處理

--強制執行下列語句,保證業務規則

UPDATE 卷煙銷售表

SET 銷售金額 = 銷售數量 * 銷售單價

WHERE 卷煙品牌 IN (SELECT 卷煙品牌 FROM INSERTED)

DECLARE @卷煙品牌 VARCHAR(40)

SET @卷煙品牌 = (SELECT 卷煙品牌 FROM INSERTED)

DECLARE @銷售數量 MONEY

SET @銷售數量 = (SELECT 銷售數量 FROM INSERTED)

UPDATE 卷煙庫存表

SET 庫存數量 = 庫存數量 - @銷售數量,

庫存金額 = (庫存數量 - @銷售數量)*庫存單價

WHERE 卷煙品牌 = @卷煙品牌

COMMIT TRANSACTION

GO

--請大家自行跟蹤[卷煙庫存表]和[卷煙銷售表]的數據變化。

--針對[卷煙銷售表],插入第一條測試數據,該數據是正常的。

INSERT INTO 卷煙銷售表(卷煙品牌,購貨商,銷售數量,銷售單價,銷售金額)

SELECT '紅塔山新勢力','某購貨商',10,12,1200

GO

--針對[卷煙銷售表],插入第二條測試數據,該數據 銷售金額 不等于 銷售單價 * 銷售數量。

--觸發器將自動更正數據,使 銷售金額 等于 銷售單價 * 銷售數量。

INSERT INTO 卷煙銷售表(卷煙品牌,購貨商,銷售數量,銷售單價,銷售金額)

SELECT '紅塔山人為峰','某購貨商',10,22,2000

GO

--針對[卷煙銷售表],插入第三條測試數據,該數據中的卷煙品牌在 卷煙庫存表中找不到對應。

--觸發器將報錯。

INSERT INTO 卷煙銷售表(卷煙品牌,購貨商,銷售數量,銷售單價,銷售金額)

SELECT '紅河V8','某購貨商',10,60,600

GO

/*

結果集

服務器: 消息 50000,級別 16,狀態 1,過程 T_INSERT_卷煙銷售表,行 15

錯誤!該卷煙不存在庫存,不能銷售。

*/

--針對[卷煙銷售表],插入第三條測試數據,該數據中的卷煙品牌在 卷煙庫存表中庫存為0。

--觸發器將報錯。

INSERT INTO 卷煙銷售表(卷煙品牌,購貨商,銷售數量,銷售單價,銷售金額)

SELECT '玉溪','某購貨商',10,30,300

GO

/*

結果集

服務器: 消息 50000,級別 16,狀態 1,過程 T_INSERT_卷煙銷售表,行 29

錯誤!該卷煙庫存小于等于0,不能銷售。

*/

--查詢數據

SELECT * FROM 卷煙庫存表

SELECT * FROM 卷煙銷售表

GO

/*

補充:

1、本示例主要通過一個簡單的業務規則實現來進行觸發器使用的說明,具體的要根據需要靈活處理;

2、關于觸發器要理解并運用好 INSERTED ,DELETED 兩個系統表;

3、本示例創建的觸發器都是 FOR INSERT ,具體的語法可參考:

Trigger語法

CREATE TRIGGER trigger_name?

ON { table | view }?

[ WITH ENCRYPTION ] --用于加密觸發器

{

{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }

[ WITH APPEND ]

[ NOT FOR REPLICATION ]

AS

[ { IF UPDATE ( column )

[ { AND | OR } UPDATE ( column ) ]

[ ...n ]

| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )

{ comparison_operator } column_bitmask [ ...n ]

} ]?

sql_statement [ ...n ]?

}?

}?

4、關于觸發器,還應該注意

(1)、DELETE 觸發器不能捕獲 TRUNCATE TABLE 語句。

(2)、觸發器中不允許以下 Transact-SQL 語句:

ALTER DATABASE CREATE DATABASE DISK INIT?

DISK RESIZE DROP DATABASE LOAD DATABASE?

LOAD LOG RECONFIGURE RESTORE DATABASE?

RESTORE LOG?

(3)、觸發器最多可以嵌套 32 層。

*/

--修改觸發器

--實質上,是將 CREATE TRIGGER ... 修改為 ALTER TRIGGER ...即可。

--刪除觸發器

DROP TRIGGER xxx

GO

--刪除測試環境

DROP TABLE 卷煙庫存表

GO

DROP TABLE 卷煙銷售表

GO

DROP TRIGGER T_INSERT_卷煙庫存表

GO

DROP TRIGGER T_INSERT_卷煙銷售表

GO

##################################################################

觸發器的基礎知識和例子

:create trigger tr_name

on table/view

{for | after | instead of } [update][,][insert][,][delete]

[with encryption]

as {batch | if update (col_name) [{and|or} update (col_name)] }

說明:

1 tr_name :觸發器名稱

2 on table/view :觸發器所作用的表。一個觸發器只能作用于一個表

3 for 和after :同義

4 after 與instead of :sql 2000新增項目afrer 與 instead of 的區別

After?

在觸發事件發生以后才被激活,只可以建立在表上?

Instead of

代替了相應的觸發事件而被執行,既可以建立在表上也可以建立在視圖上

5 insert、update、delete:激活觸發器的三種操作,可以同時執行,也可選其一

6 if update (col_name):表明所作的操作對指定列是否有影響,有影響,則激活觸發器。此外,因為delete 操作只對行有影響,

所以如果使用delete操作就不能用這條語句了(雖然使用也不出錯,但是不能激活觸發器,沒意義)。

7 觸發器執行時用到的兩個特殊表:deleted ,inserted

deleted 和inserted 可以說是一種特殊的臨時表,是在進行激活觸發器時由系統自動生成的,其結構與觸發器作用的表結構是一

樣的,只是存放 的數據有差異。

下面表格說明deleted 與inserted 數據的差異

deleted 與inserted 數據的差異

Inserted?

存放進行insert和update 操作后的數據?

Deleted

存放進行delete 和update操作前的數據

注意:update 操作相當于先進行delete 再進行insert ,所以在進行update操作時,修改前的數據拷貝一條到deleted 表中,修改后

的數據在存到觸發器作用的表的同時,也同時生成一條拷貝到insered表中

?

來源: http://skyeywolf.blog.163.com/blog/static/957272362008102741645902/

SQL觸發器實例講解


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦!!!

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 在线黄色影院 | 亚洲国产一区在线二区三区 | 日韩一区二区三区在线观看 | 精品一区二区三区的国产在线观看 | 亚洲一区二区三区麻豆 | 国产成人精品一区二区免费 | 国产第一页精品 | 久久中文字幕综合不卡一二区 | 91成人国产网站在线观看 | 俺去也理论网站 | 91福利国产在线观看一区二区 | 精品国产乱码久久久久久浪潮 | 99久热只有精品视频免费看 | 视频一区二区欧美日韩在线 | 国产精品免费久久久久影院 | 四虎精品免费永久在线 | 在线不卡日韩 | 日本爱情动作片网址 | 国产精品视频成人 | 久久永久免费 | 国产精品伦理 | 国产成人精品男人的天堂网站 | 国产农村一一级特黄毛片 | 精品久久中文网址 | 国产精品资源网站在线观看 | 成年女人视频网站免费m | 久久女人天堂 | 国内精品久久久久久久影视麻豆 | 免费一级成人毛片 | 欧美激情视频二区三区 | 久久久高清免费视频 | 国产中日韩一区二区三区 | 美女黄频免费观看 | 久久99精品久久久久久黑人 | 亚洲伦理精品久久 | 精品国产一区二区三区四区色 | 午夜香蕉视频 | 天天操天天噜 | 精品亚洲视频在线观看 | 免费看欧美一级特黄a毛片 免费看欧美一级特黄α大片 | 看全色黄大色黄大片色责看的 |