4. Trigger

Gửi bởi: Nguyễn Thị Thu Hiếu 18 tháng 2 2020 lúc 9:18:38

Mục lục

* * * * *

1. Khái niệm

Trigger là một kiểu stored procedure đặc biệt được kích nổ (thực thi) một cách tự động khi xảy ra một sự kiện trên Database server và không thể thực thi bằng tay. Trigger ược chia ra làm hai nhóm: DML và DDL trigger.

DML triggers (hay Standart triggers) thực thi khi một người sử dụng cố gắng sửa đổi dữ liệu thông qua sự kiện thao tác dữ liệu (data manipulation language - DML) INSERT, UPDATE, hoặc DELETE trên bảng hoặc view. DML triggers thường được sử dụng trong chính sách đảm bảo các quy tắc thương mại hoặc đảm bảo tính toàn vẹn dữ liệu.
DDL triggers được thực thi đáp ứng các sự kiện định nghĩa lược đồ dữ liệu (data definition language - DDL). Nhóm lệnh chính của các lệnh định nghĩa lược đồ dữ liệu là CREATE, ALTER, và DROP. Nhóm DDL trigger là nhóm trigger mới được bổ xung trong SQL Server 2005 Database Engine.

DML triggers có hai kiểu, chúng được xử lý khác nhau. Kiểu phổ biến với mọi người nhất đó là kiểu AFTER trigger và kiểu thứ hai đó là INSTEAD OF trigger.

AFTER Triggers: Khi các câu lệnh thay đổi dữ liệu được thực hiện trên một bảng có định nghĩa trigger, một vài xử lý xuất hiện trước khi trigger thực sự nổ. Đầu tiên bộ truy vấn sẽ kiểm tra trên bảng có bất cứ các ràng buộc nào hay không. Nếu có, SQL Server sẽ tiến hành kiểm tra tính hợp lệ của dữ liệu trên mọi ràng buộc nào. Nếu dữ liệu đang thêm vào hoặc đang sửa đổi mà không thỏa các ràng buộc thì bộ truy vấn sẽ dừng câu lệnh trên và khi đó trigger sẽ không được kích nổ. Ngược lại, khi kiểm tra thành công thì các trigger sẽ được thực thi.

Trước khi bất cứ câu lệnh nào chứa trigger thực sự được thực hiện, SQL Server tạo ra hai bảng đặc biệt lưu trong bộ nhớ có cùng cấu trúc với bảng mà trên đó trigger được tạo.

Table INSERTED chứa các giá trị đang được Add vào bảng.
Table DELETED chứa các giá trị đang bị xóa từ bảng.

Nếu trigger được định nghĩa là INSERT trigger thì SQL Server sẽ chỉ tạo bảng INSERTED, còn nếu là DELETE trigger thì thì SQL Server sẽ chỉ tạo bảng DELETED. Cuối cùng là nếu trigger được định nghĩa là UPDATE trigger thì SQL Server sẽ tạo cả hai bảng vì INSERTED sẽ chứa ảnh của hàng sau khi thay đổi còn bảng DELETED chứa ảnh của hàng trước khi thay đổi.

INSTEAD OF trigger: Là một đặc điểm thêm vào của SQL Server 2000. Như tên gọi đã ám chỉ, INSTEAD OF trigger được kích nổ thay cho hành động được sử dụng để kích nó.

Nghĩa là, nếu một trigger được định nghĩa là INSTEAD OF INSERT trigger thì trigger này sẽ được nổ khi câu lệnh Insert được thực hiện trên bảng. Sau khi câu lệnh sửa đổi dữ liệu được gửi đi thì INSTEAD OF trigger được kích nổ và hiện lập tức ngay lập tức. Các ràng buộc không được kiểm tra trước khi trigger được kích nổ, mặc dù các bảng INSERTED, DELETED vẫn được tạo. Sau khi các bảng này được tạo thì quá trình xử lý trigger tương tự như quá trình xử lý của stored procedure. INSTEAD OF trigger có thể được tạo trên bảng hoặc trên view.

2. Tạo trigger

a) Dùng T-SQL để tạo trigger

Ta dùng T-SQL để tạo trigger theo cú pháp sau:

- Tạo DML Trigger:

CREATE TRIGGER trigger_name ON {table | view }

[WITH ENCRYPTION]

{

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

[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 ]

Trong đó:

trigger_name: tham số chỉ định tên của trigger sẽ tạo.
ON: Chỉ định lấy tên của bảng hoặc view mà ta sẽ xây dựng trigger trên đó.
table | view: Tên của bảng hoặc của view.
WITH ENCRYPTION: Dùng để chỉ định code của trigger mã hóa lưu trữ trên bảng syscomments mà người khác không thể xem code của trigger đó.
FOR: Định nghĩa hành động mà trigger được kích nổ và kiểu của trigger ta đang tạo. AFTER trigger sẽ là trigger mặc định nếu chỉ có chỉ định FOR.AFTER: Chỉ định AFTER trigger. Tùy chọn này chỉ định riêng không lẫn với từ khóa INSTEAD OF. AFTER triggers không được định nghĩa trên view.INSTEAD OF: Từ khóa chỉ định đây là INSTEAD OF trigger. Tùy chọn này chỉ định để không lẫn với từ khóa AFTER.DELETE: Chỉ định rằng trigger ta đang tạo sẽ được kích nổ đáp ứng với hành động DELETE của bảng hoặc view.INSERT: Chỉ định rằng trigger ta đang tạo sẽ được kích nổ đáp ứng với hành động INSERT của bảng hoặc view.UPDATE: Chỉ định rằng trigger ta đang tạo sẽ được kích nổ đáp ứng với hành động UPDATE của bảng hoặc view.NOT FOR REPLICATION: Chỉ định rằng bất cứ bản sao nào của các hành động chạy ngầm dưới bảng này đều không được kích nổ trigger này.AS: Chỉ định phần code của trigger bắt đầu từ đây.IF UPDATE (column): Được dùng trong các trigger INSERT, UPDATE. Cấu trúc này được dùng để kiểm tra các sửa đổi trên cột chỉ định và sau đó là các hành động trên nó.{AND | OR} UPDATE (column): Chỉ định rằng ta có thể sử dụng chuỗi các cấu trúc UPDATE với nhau để kiểm tra một vài cột tại cùng một thời điểm....n: Chỉ định ta có thể lặp lại các cấu trúc trên nếu cần thiết.IF(COLUMNS_UPDATED():Chỉ dùng trong các trigger INSERT, UPDATE. Hàm trả về một bit chỉ định cột bị chỉnh sửa trong quá trình INSERT, UPDATE trên bảng cơ sở.bitwise_operator: Được dùng để so sánh với bit trả về của hàm COLUMNS_UPDATED()updated_bitmask: Được sử dụng để kiểm cột nào thực sự được Update trong câu lệnh Insert hoặc Update.sql_statement: Các câu lệnh T-SQL... n: Chỉ định lặp lại các câu lệnh T-SQL..

- Tạo DDL Trigger:

CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH ENCRYPTION ]

{ FOR |AFTER } {event_type } [,...n ]

AS { sql_statement [ ; ] [ ...n ]}

Trong đó:

DATABASE:Chỉ định phạm vi của DDL trigger là database hiện thời. Trigger sẽ kích nổ khi sự kiện event_type xảy ra trên database hiện thời.
ALL SERVER: Chỉ định phạm vi của DDL trigger là server hiện thời. Trigger sẽ kích nổ khi sự kiện event_type xảy ra trên server hiện thời.
event_type: Là tên của sự kiện mà là nguyên nhân kích nổ DDL trigger. Các sự kiện này có thể là: CREATE_FUNCTION, CREATE_INDEX, GRANT_DATABASE, CREATE_TABLE, ALTER_VIEW, ALTER_TABLE, DROP_TABLE, DROP_VIEW, .v.v...

Ví dụ 4.44. Tạo một AFTER INSERT Trigger. Trong ví dụ này ta định nghĩa AFTER INSERT Trigger trên bảng TriggerTableChild. Trigger này có nhiệm vụ kiểm tra xem có sự tương ứng với các dòng của bảng TriggerTableParent hay không. Nếu không có sự tương ứng nó sẽ thực hiện roll back (cuốn lại) lại giao dịch đó và dòng thông báo lỗi hiện lên. Nếu có sự tương ứng thì giao dịch được thực hiện một cách bình thường.

CREATE TABLE TriggerTableParent (

TriggerID INT,



TriggerText VARCHAR(32)

) GO



INSERT INTO TriggerTableParent

VALUES

(1,

'Trigger

Text

1')

INSERT INTO TriggerTableParent

VALUES

(2,

'Trigger

Text

2')

INSERT INTO TriggerTableParent

VALUES

(3,

'Trigger

Text

3')

INSERT INTO TriggerTableParent

VALUES

(4,

'Trigger

Text

4')

INSERT INTO TriggerTableParent

GO

VALUES

(5,

'Trigger

Text

5')



CREATE TABLE TriggerTableChild (

TriggerID INT, TriggerSubText VARCHAR(32)

) GO



CREATE TRIGGER trTriggerTableChildInsert ON TriggerTableChild

FOR INSERT AS

IF (SELECT COUNT(*)FROM TriggerTableParent TTP

INNER JOIN INSERTED I ON (TTP.TriggerID= I.TriggerID))= 0 BEGIN

ROLLBACK TRANSACTION

RAISERROR ('No corresponding record was found in the TriggerTableParent table for this insert.', 11, 1)

END ELSE

Print 'This vas inserted'

GO

SET NOCOUNT ON

INSERT INTO TriggerTableChild VALUES(1, 'Sub Trigger Text 1') INSERT INTO TriggerTableChild VALUES(2, 'Sub Trigger Text 2') INSERT INTO TriggerTableChild VALUES(3, 'Sub Trigger Text 3') INSERT INTO TriggerTableChild VALUES(6, 'Sub Trigger Text 6') GO

Ví dụ 4.45. Dùng bảng INSERTED và DELETED

CREATE TRIGGER trTriggerTableParentUpdate1 ON TriggerTableParent1

AFTER UPDATE AS

SET NOCOUNT ON


PRINT 'Contents of the INSERTED Table:' SELECT *

FROM INSERTED

PRINT 'Contents of the DELETED Table:' SELECT *

FROM DELETED

PRINT 'Contents of the TriggerTableParent Table:' SELECT TTP.* FROM TriggerTableParent1 TTP

INNER JOIN INSERTED I ON

(TTP.TriggerID = I.TriggerID)


ROLLBACK TRANSACTION GO

UPDATE TriggerTableParent1

SET TriggerText = 'Changed Trigger Text 1' WHERE TriggerID = 1

Khi sử dụng lệnh UPDATE, kết quả cho thấy bảng INSERTED chứa các giá trị mới; bảng DELETED chứa các giá trị cũ và bảng TriggerTableParent1 chứa các giá trị mới.

Ví dụ 4.46. Sử dụng cấu trúc IF UPDATED trong UPDATE Trigger

CREATE TRIGGER trTriggerTableChildUpdate ON TriggerTableChild1

AFTER UPDATE AS

IF UPDATE(TriggerID) BEGIN

IF (SELECT COUNT(*)FROM TriggerTableParent1 TTP INNER JOIN INSERTED I ON

(TTP.TriggerID = I.TriggerID)) = 0

BEGIN

RAISERROR ('No parent record exists for this modification. Transaction cancelled.', 11,



END GO



END

1)

ROLLBACK TRANSACTION RETURN



UPDATE TriggerTableChild1 SET TriggerID = 7

WHERE TriggerID = 1 GO

Ví dụ 4.47. Tạo INSTEAD OF Trigger. Bởi vì INSTEAD OF Trigger được kích nổ trước khi bất cứ dữ liệu nào được sửa đổi trong CSDL, do đó vai trò của hai bảng INSERTED và DELETED bị giảm nhẹ hơn. Ta xét ví dụ sau tương tự như ví dụ 4.45.

CREATE TABLE TriggerTableParent2 (

TriggerID INT, TriggerText VARCHAR(32)

) GO

INSERT INTO TriggerTableParent2 VALUES(1,'Trigger Text 1') INSERT INTO TriggerTableParent2 VALUES(2,'Trigger Text 2') INSERT INTO TriggerTableParent2 VALUES(3,'Trigger Text 3') INSERT INTO TriggerTableParent2 VALUES(4,'Trigger Text 4') INSERT INTO TriggerTableParent2 VALUES(5,'Trigger Text 5') GO

CREATE TABLE TriggerTableChild2 (

TriggerID INT, TriggerSubText VARCHAR(32)

) GO

CREATE TRIGGER trTriggerTableParent2InsteadOfUpdate ON TriggerTableParent2

INSTEAD OF UPDATE AS

SET NOCOUNT ON



PRINT 'Contents of the INSERTED Table:' SELECT *

FROM INSERTED

PRINT 'Contents of the DELETED Table:' SELECT *

FROM DELETED

PRINT 'Contents of the TriggerTableParent Table:' SELECT TTP.*

FROM TriggerTableParent2 TTP INNER JOIN INSERTED I ON

(TTP.TriggerID = I.TriggerID)



ROLLBACK TRANSACTION GO



UPDATE TriggerTableParent2

SET TriggerText = 'Changed Trigger Text 1' WHERE TriggerID = 1

GO

Khi thực hiện lệnh Update, ta thấy bảng cơ sở chưa được chèn dữ liệu do trigger đã được thực thi trước khi có sự sửa đổi dữ liệu, đây chính là đặc điểm của INSTEAD OF Trigger. Cụ thể khi sử dụng lệnh UPDATE, kết quả cho thấy bảng INSERTED chứa các giá trị mới; bảng DELETED chứa các giá trị cũ và bảng TriggerTableParent2 chứa các giá trị cũ.

Ví dụ 4.48. View của các bảng và INSTEAD OF Trigger.

Một trong những đặc điểm nổi bật chính của INSTEAD OF Trigger là cho phép người sử dụng thực hiện các câu lệnh thay đổi dữ liệu trên view của nhiều bảng. Trong ví dụ này, ta xây dựng một INSTEAD OF Trigger thực hiện chức năng này.

SET NOCOUNT ON GO

CREATE TABLE ViewTable1

(

KeyColumn INT, Table1Column VARCHAR(32)

)

GO



INSERT

INTO

ViewTable1

VALUES

(1,

'ViewTable1

Value

1')

INSERT

INTO

ViewTable1

VALUES

(2,

'ViewTable1

Value

2')

INSERT

INTO

ViewTable1

VALUES

(3,

'ViewTable1

Value

3')

INSERT

INTO

ViewTable1

VALUES

(4,

'ViewTable1

Value

4')

INSERT

INTO

ViewTable1

VALUES

(5,

'ViewTable1

Value

5')

INSERT

INTO

ViewTable1

VALUES

(6,

'ViewTable1

Value

6')

INSERT

INTO

ViewTable1

VALUES

(7,

'ViewTable1

Value

7')

INSERT

INTO

ViewTable1

VALUES

(8,

'ViewTable1

Value

8')

INSERT

INTO

ViewTable1

VALUES

(9,

'ViewTable1

Value

9')

INSERT INTO ViewTable1 VALUES (10, 'ViewTable1 Value 10') GO



CREATE TABLE ViewTable2 (

KeyColumn INT, Table2Column VARCHAR(32)

) GO



INSERT

INTO

ViewTable2

VALUES

(1,

'ViewTable2

Value

1')

INSERT

INTO

ViewTable2

VALUES

(2,

'ViewTable2

Value

2')

INSERT

INTO

ViewTable2

VALUES

(3,

'ViewTable2

Value

3')

INSERT

INTO

ViewTable2

VALUES

(4,

'ViewTable2

Value

4')

INSERT

INTO

ViewTable2

VALUES

(5,

'ViewTable2

Value

5')

INSERT

INTO

ViewTable2

VALUES

(6,

'ViewTable2

Value

6')

INSERT

INTO

ViewTable2

VALUES

(7,

'ViewTable2

Value

7')

INSERT

INTO

ViewTable2

VALUES

(8,

'ViewTable2

Value

8')

INSERT

INTO

ViewTable2

VALUES

(9,

'ViewTable2

Value

9')

INSERT INTO ViewTable2 VALUES (10, 'ViewTable2 Value 10') GO



CREATE VIEW TestView1 AS

SELECT VT1.KeyColumn, VT1.Table1Column, VT2.Table2Column

FROM ViewTable1 VT1 INNER JOIN ViewTable2 VT2 ON

(VT1.KeyColumn = VT2.KeyColumn)

GO

INSERT INTO TestView1 VALUES (11, 'ViewTable1 Value 11', 'ViewTable2 Value 11')

GO

CREATE TRIGGER trTestView1InsteadOfInsert ON TestView1

INSTEAD OF INSERT AS

DECLARE @intKeyColumn INT DECLARE @vchTable1Column VARCHAR(32) DECLARE @vchTable2Column VARCHAR(32) DECLARE @intError INT



SET NOCOUNT ON

SELECT @intKeyColumn=KeyColumn, @vchTable1Column = Table1Column, @vchTable2Column = Table2Column FROM INSERTED

BEGIN TRANSACTION

INSERT INTO ViewTable1 VALUES(@intKeyColumn,@vchTable1Column)

SELECT @intError = @@ROWCOUNT

INSERT INTO ViewTable2 VALUES(@intKeyColumn, @vchTable2Column)

SELECT @intError = @intError + @@ROWCOUNT

IF ((@intError < 2) OR (@intError % 2) <> 0) BEGIN

RAISERROR('An error occurred during the multitable insert.', 1, 11)

ROLLBACK TRANSACTION RETURN

END

COMMIT TRANSACTION GO

INSERT INTO TestView1 VALUES (11, 'ViewTable1 Value 11','ViewTable2 Value 11')

GO

Trong đoạn script trên, đầu tiên ta tạo 2 bảng và chèn 10 dòng vào hai bảng đó. Hai bảng đó có chung một cột là KeyColumn và ta tạo view thể hiện dữ liệu của hai bảng. Lệnh INSERT sẽ thực hiện chèn dữ liệu vào hai bảng. Lệnh INSERT đầu tiên sẽ bị lỗi vì có nhiều bảng trong mệnh đề FROM của câu lệnh SELECT tạo view đó. Lệnh tiếp theo sẽ được thực hiện bởi trigger đã tạo.

* Tiến trình xử lý giao dịch - TRANSACTION: DBMS phải đảm bảo một giao dịch được xử lý như một đơn vị cơ sở. Điều này có nghĩa là khi DBMS đang thực hiện tiến trình xử lý câu lệnh đầu tiên trong một giao dịch thì thì tiến trình này phải được tiếp tục cho đến khi tất cả các câu lệnh trong giao dịch được thực hiện thành công, giao dịch này không bị bẻ gãy. Tất cả các câu lệnh trong một giao dịch phải được xử lý như một đơn vị công việc.

Khi một tiến trình bị bẻ gãy ở giữa một giao dịch, như kết quả của việc re boot, hệ thống bị vỡ, thì DBMS phải đưa database về trạng thái tồn tại trước khi giao dịch được bắt đầu. Một câu lệnh SQL đặc biệt để làm điều này đó là ROLL BACK. Nếu tất cả câu lệnh trong đã được thực hiện thành công thì sự thay đổi Database được thực hiện bởi câu lệnh COMMIT. COMMIT và ROLL BACK là các câu lệnh SQL thao chuẩn ANSI/ISO, giống như các câu lệnh SELECT, INSERT, .v.v...

Ví dụ 4.49. DDL Trigger.

CREATE TRIGGER safety ON DATABASE

FOR DROP_TABLE, ALTER_TABLE AS

PRINT 'You must disable Trigger "safety" to drop or alter tables!'

ROLLBACK

3. Các thao tác quản lý trigger

* Xem mã trigger: Dùng thủ tục sp_helptext.

Ví dụ 4.49. Xem code của trigger trTestView1InsteadOfInsert Exec sp_helptext trTestView1InsteadOfInsert go

* Xem những trigger nào đang tồn tại trên một bảng hoặc một view: Dùng thủ tục sp_helptrigger.

Ví dụ 4.50. Xem các trigger trên bảng HOSOSV

Use QLDiemSV Go

Exec sp_helptrigger HOSOSV go

* Thay đổi nội dung trigger: Để thay đổi trigger ta dùng câu lệnh ALTER TRIGGER theo cú pháp sau:

- Sửa DML Trigger:

ALTER TRIGGER trigger_name ON {table | view }

[WITH ENCRYPTION]

{

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

[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 ]

}

- Sửa DDL Trigger:

ALTER TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH ENCRYPTION ]

{ FOR |AFTER } {event_type } [,...n ]

AS { sql_statement [ ; ] [ ...n ]}

Ví dụ 4.51. Thay đổi nội dung trigger trTestView1InsteadOfInsert

ALTER TRIGGER trTestView1InsteadOfInsert ON TestView1

INSTEAD OF INSERT AS

DECLARE @intKeyColumn INT DECLARE @vchTable1Column VARCHAR(32) DECLARE @vchTable2Column VARCHAR(32) DECLARE @intError INT



SET NOCOUNT ON



SELECT @intKeyColumn=KeyColumn, @vchTable1Column = Table1Column, @vchTable2Column = Table2Column FROM INSERTED



BEGIN TRANSACTION

INSERT INTO ViewTable1 VALUES(@intKeyColumn,@vchTable1Column)

SELECT @intError = @@ROWCOUNT

INSERT INTO ViewTable2 VALUES(@intKeyColumn, @vchTable2Column)

SELECT @intError = @intError + @@ROWCOUNT

IF ((@intError < 2) OR (@intError % 2) <> 0) BEGIN

ROLLBACK TRANSACTION RETURN

END

COMMIT TRANSACTION GO

* Xóa một trigger: Dùng câu lệnh DROP TRIGGER.

- Xóa DML Trigger:

DROP TRIGGER schema_name.trigger_name [ ,...n ]

- Xóa DDL Trigger:

DROP TRIGGER trigger_name [ ,...n ] ON { DATABASE | ALL SERVER }

Ví dụ 4.52. Xóa trigger trTestView1InsteadOfInsert

DROP TRIGGER trTestView1InsteadOfInsert
go

* Vô hiệu hóa hoặc làm cho có hiệu lực một trigger ta dùng câu lệnh:

DISABLE | ENABLE TRIGGER{[ schema . ]

trigger_name [ ,...n ] | ALL }

ON { object_name | DATABASE | ALL SERVER } [ ; ]

Ví dụ 4.53. Vô hiệu hóa trigger trTriggerTableParent2InsteadOfUpdate Trên bảng TriggerTableParent2.

ALTER TABLE TriggerTableParent2
DISABLE TRIGGER trTriggerTableParent2InsteadOfUpdate GO.

Ví dụ 4.54. Vô hiệu hóa trigger [trTriggerTableChildInsert] trên bảng TriggerTableChild.

DISABLE TRIGGER [trTriggerTableChildInsert] ON [dbo].[TriggerTableChild]

Ví dụ 4.55. Làm cho trigger trTriggerTableParent2InsteadOfUpdate trên bảng TriggerTableParent2 có hiệu lực.

ALTER TABLE TriggerTableParent2
ENABLE TRIGGER trTriggerTableParent2InsteadOfUpdate GO.

Được cập nhật: 19 giờ trước (18:55:09) | Lượt xem: 930

Các bài học liên quan

Hỏi đáp

MÔN HỌC

4. Trigger

1. Khái niệm

2. Tạo trigger

3. Các thao tác quản lý trigger

Các bài học liên quan