Bài giảng Microsoft SQL server: Chương 8 - Phạm Mạnh Cương

ƯƠ

CH

NG VIII

TRIGGER

ươ

ả

ạ

ạ Gi ng viên: Ph m M nh C ng

1

I­ Khái ni m:ệ

• Trigger là một thủ tục không có tham số

• Một Table có thể chứa nhiều Trigger

• Trigger tự động thực hiện khi một trong 3 phát biểu Insert, Update,

Delete thay đổi dữ liệu trên table chứa trigger.

• Trigger chỉ được thực hiện khi phát biểu cập nhật thoả mãn các ràng

buộc đa khai báo trên Table. Do đó, Trigger thường được dùng để

kiểm tra các ràng buộc mà không thể khai báo trên Table như các ràng

buộc liên thuộc tính liên quan hệ, liên bộ liên quan hệ, ràng buộc chu

trình.

• Sử dụng lệnh ROLLBACK TRAN trong Trigger để bãi bỏ phát biểu

cập nhật khi cần thiết.

2

ạ

II­ T o Trigger cho Table:

1. Bằng Lệnh Create Trigger

2. Bằng công cụ quản lý Management Studio

3

ệ

1­ L nh Create Trigger:

CREATE TRIGGER ON

[WITH ENCRYPTION]

AFTER | FOR {DELETE, INSERT, UPDATE}

AS

• Tập con của { DELETE, INSERT, UPDATE} dùng chỉ định những

phát biểu cập nhật nào trên Table sẽ kích hoạt Trigger.

4

ử ụ

ả

ạ

2­ S  d ng b ng t m: Inserted và Deleted

Khi thực hiện Trigger, SQL tự động tạo 2 bảng tạm có cùng cấu trúc với Table

được cập nhật:

• Bảng INSERTED: dùng chứa tạm các mẫu tin mới khi thực hiện lệnh Insert hoặc

mẫu tin chứa dữ liệu đa sửa đổi khi thực hiện lệnh Update.

• Bảng DELETED : dùng chứa tạm các mẫu tin bị xóa khi thực hiện lệnh Delete

hoặc mẫu tin chứa dữ liệu cũ khi thực hiện lệnh Update.

Ta sử dụng dữ liệu trên các table này để thực hiện xử lý bên trong Trigger liên

quan đến các mẫu tin vừa mới thêm, sửa hay xóa.

5

6

ụ

ể

ế

ơ

ơ

ộ Ví d : Khi thêm 1 CTHD, ki m tra RB: M t t hóa đ n. hóa đ n không có quá 10 chi ti

Create Trigger itrg_SoCTHD On CTHD

For Insert

As

If (Select Count(a.SoHD)

From CTHD a Join INSERTED b On a.SoHD = b.SoHD) >= 10

Begin

Print ‘So CTHD Khong the > 10’

RollBack Tran

End

7

ụ

ế

ơ

ả

ả

t hóa đ n th

ố ì ph i gi m s

ế

ơ

ã ghi trên chi ti

t hóa đ n.

Ví d : Khi thêm chi ti t nồ ặ ủ c a m t hàng đ Create Trigger itrg_GiamTon On CTHD

For Insert

As

Declare @D int

Select @D = Count( * ) From MatHang a Join INSERTED b On a.MaMH = b.MaMH

Where SoTon - b.SL < 0

If (@D > 0 )

Begin

Print ‘Khong du hang de ban’

RollBack Tran

Return

End

Update MatHang Set SoTon = SoTon - SL

From INSERTED Where MatHang.MaMH = INSERTED.MaMH

8

ẫ

b) Khi Delete m u tin

9

ế

ộ

ố

t hóa đ n th

ụ ủ

ì ph i tăng s ơ

ế

Ví d : Khi xóa m t chi ti ặ ồ t n c a m t hàng đ

ơ ã ghi trên chi ti

ả t hóa đ n.

Create Trigger trg_XoaCTHD On CTHD

For Delete

As

Update MatHang Set SoTon = SoTon + SL

From DELETED Where MatHang.MaMH = DELETED.MaMH

Go

10

ẫ

c) Khi Update m u tin

11

ụ

ế

ơ

ng trên các chi ti

ố ồ

ì ph iả t hóa đ n th ơ

ế

ử ố ượ Ví d : Khi s a s  l ặ ủ ử s a s  t n c a m t hàng đ

ã ghi trên chi ti

t hóa đ n.

Create Trigger utrg_SuaCTHD On CTHD For Update

As

Declare @D int

Select @D = Count( * ) From MatHang a, DELETED b, INSERTED c

Where a.MaMH = b.MaMH And a.MaMH = c.MaMH And SoTon + b.SL - c.SL < 0

If (@D > 0 )

Begin

Print ‘Khong du hang de ban’

RollBack Tran

Return

End

Update MatHang Set SoTon = SoTon + b.SL - c.SL

From MatHang a, DELETED b, INSERTED c

Where a.MaMH = b.MaMH And a.MaMH = c.MaMH

12

ụ

ử

ủ

ể

ế

ơ

ơ

ộ Ví d : Khi s a SoHD c a CTHD, ki m tra RB: M t hóa đ n không có quá 10 chi ti

t hóa đ n.

Create Trigger utrg_SoCTHD On CTHD For Update As

If (Select Count(a.SoHD) From CTHD a Join INSERTED b On a.SoHD = b.SoHD) > 10

Begin

Print ‘So CTHD Khong the > 10’ RollBack Tran

End

13

ậ

ậ

ậ 3­ Nh n bi

ế ộ ượ t C t đ

c c p nh t :

• Hàm Update() : Trả về giá trị TRUE nếu

Name> được cập nhật dữ liệu.

Ví dụ: Khi sửa thông tin chi tiết hóa đơn:

• Nếu sửa SoHD thì kiểm tra RB "Một hóa đơn không có quá 10 CTHD

• Nếu sửa SL thì thay đổi số tồn của mặt hàng

14

ụ ử ụ

Ví d : S  d ng hàm Update

Create Trigger utrg_CTHD On CTHD For Update As

If Update(SoHD) If (Select Count(a.SoHD) From CTHD a, INSERTED b Where a.SoHD = b.SoHD)>=10

Begin

RollBack Tran Return

If (@D > 0 ) Begin RollBack Tran Return End Update MatHang Set SoTon = SoTon + b.SL – c.SL From MatHang a, DELETED b, INSERTED c Where a.MaMH = b.MaMH And a.MaMH = c.MaMH End

End If Update(SL) Begin

Declare @D int select @D = Count( * ) From MatHang a, DELETED b, INSERTED c Where a.MaMH = b.MaMH And a.MaMH = c.MaMH And SoTon + b.SL – c.SL < 0

15

ự

ự

ệ

ệ

4­ Th c hi n hay không th c hi n Trigger

ALTER TABLE

ENABLE | DISABLE TRIGGER ALL | [,…n]

Ví dụ: Không thực hiện tất cả Triggers của table CTHD

ALTER TABLE DISABLE TRIGGER ALL

Ví dụ: Không thực hiện Trigger itrg_SoCTHD và

utrg_SoCTHD

của table CTHD

ALTER TABLE

DISABLE TRIGGER itrg_SoCTHD, utrg_SoCTHD

16

ử

III­ S a, Xóa Trigger:

1. Sửa Trigger:

ALTER TRIGGER ON

[WITH ENCRYPTION]

AFTER | FOR {DELETE, INSERT, UPDATE}

AS

2. Xóa Trigger:

DROP TRIGGER [,…n]

17

Ả Ơ C M  N

18