Thực tập cơ sở dữ liệu Lớp th11a giáo viên: Đỗ Thị Mai Hường Khoa cntt

LỚP TH11A

GIÁO VIÊN: ĐỖ THỊ MAI HƯỜNG
BUỔI 10 - Trigger

Mục đích: Giúp sinh viên có được kỹ năng lập trình trên hệ quản trị cơ sở dữ liệu SQL Server.

Yêu cầu: Sinh viên kiểm tra toàn vẹn dữ liệu, thành thạo cú pháp lệnh lập trình trên hệ quản trị cơ sở dữ liệu SQL Server, cụ thể Lập trình tạo Trigger. Nâng cao khả năng làm việc theo nhóm: tạo trigger, kiểm tra toàn vẹ dữ liệu, lập trình thao tác với dữ liệu phù hợp với thực tế.

( Sinh viên đọc tài liệu tham khảo Giáo trình thực hành SQL từ trang 35-42, Slides bài giảng của Giáo viên: Chương 7 Lập trình T_SQL )

I. Lý thuyết: Trigger

Trigger là một kiểu thủ tục được kích họat tự động theo các sự kiện (events), nhằm:

• 
  So sánh kiểu dữ liệu.
  
• 
  Đọc dữ liệu từ các bảng nằm trong cơ sở dữ liệu khác.
  
• 
  Thay đổi theo tầng hoặc xoá liên tục các bảng liên quan trong một cơ sở dữ liệu
  
• 
  Huỷ bỏ các thay đổi không đúng
  
• 
  Kiểm tra các ràng buộc phức tạp hơn việc bắt lỗi bằng ràng buộc CHECK
  

- Có 02 lọai triggers:

+ Data Modification Language –DML (For | After triggers, Instead-of triggers)

+ Data Definition Language - DDL triggers (For | After triggers)

CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH [ ENCRYPTION ] | [ EXECUTE AS CALLER | SELF | 'user_login' ] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement [ ; ] [ ...n ] }

Execute As User = 'user'

DISABLE TRIGGER { [ schema . ] trigger_name [ ,...n ] | ALL } ON {DATABASE | ALL SERVER } [ ; ]

DDL triggers là các triggers được tự động gọi sau khi máy thực hiện các lệnh sau:

Create Table, Drop Table, Alter Procedure, Drop Schema, Create Login, …

• 
  Cú pháp với DML Trigger:
  

• 
  Tạo CREATE TRIGGER ON |
  

{[FOR| AFTER] [insert],[update],[delete] | Instead of}

• 
  Xoá Drop Trigger
  
• 
  DISABLE TRIGGER { trigger_name [ ,...n ] | ALL } ON object_name
  

With encryption: nội dung của trigger sẽ được mã hóa.

• 
  AFTER (FOR): các câu lệnh bên trong trigger sẽ được thực hiện sau khi các sự kiện tạo nên trigger đã xảy ra rồi.
  
• 
  INSTEAD OF: sẽ bỏ qua sự kiện đã kích hoạt trigger mà thay vào đó sẽ thực hiện các dòng lệnh SQL bên trong Trigger
  

• 
  Trigger Insert: Trigger được phát biểu bởi For insert. Trigger được thực hiện khi tiến hành thêm một mẫu tin vào bảng. Mẫu tin cần thêm sẽ được lưu trong một bảng tạm có tên là Inserted.
  
• 
  Trigger Delete: Trigger được phát biểu bởi For delete. Trigger được thực hiện khi tiến hành xóa một mẫu tin trong bảng. Mẫu tin bị xóa sẽ được lưu trong một bảng tạm có tên là deleted.
  
• 
  Trigger Update: Trigger được phát biểu bởi For update. Trigger được thực hiện khi tiến hành sửa một mẫu tin trong bảng. Mẫu tin bị thay đổi sẽ được lưu trong 2 bảng tạm có tên là Inserted (chứa giá trị mới) và Deleted (chứa giá trị cũ).
  

• 
  Trigger không thể được tạo ra trên bảng tạm thời hay bảng hệ thống. Trigger chỉ có thể được kích hoạt một cách tự động bởi một trong các event Insert, Update, Delete. Có thể áp dụng trigger cho View. 
  
• 
  Inserted và Deleted là 2 table tạm chỉ chứa trên bộ nhớ và chỉ có giá trị bên trong trigger mà thôi (nghĩa là chỉ nhìn thấy được trong trigger mà thôi). Ta có thể dùng thông tin trong 2 table này để so sánh dữ liệu cũ và mới hoặc kiểm tra xem dữ liệu mới.
  

Dạng INSTEAD OF sẽ bỏ qua sự kiện đã kích hoạt trigger mà thay vào đó sẽ thực hiện các dòng lệnh SQL bên trong Trigger

INSTEAD OF được chia làm 3 loại nhỏ: INSTEAD OF INSERT, INSTEAD OF UPDATE và INSTEAD OF DELETE.

Ví dụ:

go

go

create table HangHoa(MaHD int, NgayHD Datetime, GhiChu ntext,

go

create Table NhapHangHoa(MaHD int, NgayHD Datetime, GhiChu ntext)

create Table NhapHangHoaCT(MaHD int,MaSP NVarchar(10), Soluong int, DonGia float)

go

CREATE TRIGGER dbo.Trig_Instead ON HangHoa instead of INSERT

insert into NhapHangHoa select MaHD , NgayHD , GhiChu from inserted

insert into NhapHangHoaCT select MaHD ,MaSP , Soluong , DonGia from inserted

End go

select * from hanghoa

select * from NhapHangHoa

select * from NhapHangHoaCT

Trong bảng nhân viên, kiểm tra:

1. 
   Mỗi nhân viên chỉ có nhiều nhất một người quản lý cấp trên trực tiếp ( đã được Primary key đảm bào nên không phải làm già).
   
2. 
   Mỗi nhân viên không được làm thủ trưởng của chính mình.
   
3. 
   Kiểm tra xem thủ trưởng của nhân viên có tồn tại không.
   
4. 
   Chỉ có một người trong cơ quan là có chức vụ cao nhất (Ma_NQL null).
   
5. 
   Khi xóa một nhân viên x thì sẽ tự động thay nhân viên x trong trường Ma_NQL bằng nhân viên cấp trên trực tiếp của x.
   
6. 
   Thêm một cột SoNV ( số nhân viên dưới quyền) trong bảng Nhanvien. Viết trigger điền giá trị cho trường số nhân viên dưới quyền
   

. Đề bài

Cho bảng dữ liệu Emp_Mg gồm các trường sau:

Emp_ID: mã nhân viên

Mg_ID: mã người quản lý nhân viên Emp

NoOfReports: số nhân viên cấp dưới của Emp

CREATE TABLE Emp_Mgr(Emp_ID nCHAR(10) PRIMARY KEY, Mg_ID nCHAR(10) NULL, NoOfReports INT DEFAULT 0, CONSTRAINT FK_Emp FOREIGN KEY (Mgr) REFERENCES Emp_Mgr (Emp_ID))

II. Yêu cầu về ràng buộc dữ liệu:

1. 
   Mỗi nhân viên chỉ có nhiều nhất một thủ trưởng cấp trên trực tiếp.
   
2. 
   Mỗi nhân viên không được làm thủ trưởng của chính mình.
   
3. 
   Kiểm tra xem thủ trưởng của nhân viên có tồn tại không.
   
4. 
   Chỉ có một người trong cơ quan là có chức vụ cao nhất (Mg_ID=null).
   
5. 
   Khi xóa một nhân viên x thì sẽ tự động thay nhân viên x trong trường Mg_ID bằng nhân viên cấp trên trực tiếp của x.
   
6. 
   Viết trigger điền giá trị cho trường NoOfReports (số nhân viên dưới quyền)
   

Emp_ID	Mg_ID	NoOfReports
A	Null	5
B	A	4
C	B	3
D	C	1
E	D	0
F	C	0

III. Cập nhật dữ liệu

INSERT INTO emp_mgr(empID,mg_ID) VALUES('a',NULL)

INSERT INTO emp_mgr(empID,mg_ID) VALUES('b','a')

INSERT INTO emp_mgr(empID,mg_ID) VALUES('c','a')

INSERT INTO emp_mgr(empID,mg_ID) VALUES('d','a')

INSERT INTO emp_mgr(empID,mg_ID) VALUES('e','b')

INSERT INTO emp_mgr(empID,mg_ID) VALUES('f','b')

INSERT INTO emp_mgr(empID,mg_ID) VALUES('g','b')

INSERT INTO emp_mgr(empID,mg_ID) VALUES('i','c')

INSERT INTO emp_mgr(empID,mg_ID) VALUES('k','d')

IV. Giải đáp

1. Mỗi nhân viên chỉ có nhiều nhất một thủ trưởng cấp trên trực tiếp (được PK đảm bảo)
2. Mỗi nhân viên không được làm thủ trưởng của chính mình

Ta phải thêm trigger sau đây cho insert, update

Create Trigger Trigger_KT1 on Emp_MG for Insert, Update

as

begin

IF EXISTS (SELECT * FROM inserted

WHERE Mg_ID=Emp_ID)

BEGIN

ROLLBACK TRAN

RAISERROR(‘Mgr <> Emp !!!’,16,1)

RETURN

END

end

3. Kiểm tra xem thủ trưởng của nhân viên có tồn tại không

- Sử dụng khóa ngoài

IF EXISTS(SELECT * FROM inserted WHERE Mg_ID IS NOT NULL) AND

NOT EXISTS(SELECT * FROM inserted, Emp_Mg

WHERE Emp_Mg.Emp_ID=inserted.Mg_ID)

BEGIN

ROLLBACK TRAN

RAISERROR('Khong co mgr',16,1)

RETURN

END
Hoặc

IF NOT EXISTS(SELECT * FROM Emp_Mg, inserted

WHERE Emp_Mg.Emp_ID=inserted.Mg_ID OR inserted.Mg_ID IS NULL)

BEGIN

ROLLBACK TRAN

RAISERROR('Khong co mgr',16,1)

RETURN

END
4. Chỉ có một người trong cơ quan là có chức vụ cao nhất (mgr=null)

Khi thêm mới:

CREATE TRIGGER TRIGGER4 ON [dbo].[Emp_Mg]

FOR INSERT, UPDATE

AS

IF EXISTS (SELECT * FROM inserted WHERE Mg IS NULL)

AND ( (SELECT count(*) FROM Emp_Mg WHERE Emp_Mg.Mg_ID IS NULL) = 2 )

BEGIN

ROLLBACK TRANSACTION

RAISERROR('Exit Header',16,1)

RETURN

END
Bài 2: Thực hành theo đề tài nhóm. Sử dụng trigger kiểm tra toàn vẹn dữ liệu và thao tác với cơ sở dữ liệu trong đề tài nhóm.

Каталог: files -> DanhSach
files -> ĐẠi học quốc gia hà NỘi trưỜng đẠi học khoa học tự nhiên nguyễn Thị Hương XÂy dựng quy trình quản lý CÁc công trìNH
files -> PHỤ LỤC 2 TỔng hợp danh mục tài liệu tham khảO
files -> BÁo cáo quy hoạch vùng sản xuất vải an toàn tỉnh bắc giang đẾn năM 2020 (Thuộc dự án nâng cao chất lượng, an toàn sản phẩm nông nghiệp và phát triển chương trình khí sinh học ) Cơ quan chủ trì
files -> BỘ TÀi nguyên và MÔi trưỜng
files -> 1. Mục tiêu đào tạo: Mục tiêu chung
DanhSach -> PHẦN 1 LƯU ĐỒ thuật toáN (flowchart)
DanhSach -> Yêu cầu: Với dữ liệu sinh viên gồm các thông tin

tải về 46.07 Kb.

Chia sẻ với bạn bè của bạn: