Transcript Document

Course ILT
Chương 13:
Transaction và Triggers
Course ILT
Mục tiêu
 Quản lý transaction và khoá
 Tạo và thử triggers để quản lý việc
chỉnh sửa dữ liệu
Course ILT
Khái quát về Transaction
 SQL Server sử dụng nhật ký giao dịch
(transaction log) trong mỗi database
để khôi phục lại các giao dịch
 Transaction là 1 chuỗi các thao tác
được thực thi như 1 đơn vị công việc
riêng lẻ (single logical unit of work).
Course ILT
Khái quát về Transaction
 Transaction phải bao hàm 4 thuộc tính cơ
bản (ACID) sau:
– Atomicity: một transaction phải là 1 đơn vị
công việc nguyên tử; hoặc tất cả các sửa đổi
dữ liệu đều được thực thi hoặc không 1 sửa đổi
nào được thực thi.
– Consistency: Khi hoàn tất, transaction phải
cho dữ liệu ở tình trạng ổn định.
– Isolation : Những chỉnh sửa được làm bởi
transaction hiện hành phải được cô lập khỏi
những chỉnh sửa được làm bởi các transaction
hiện hành khác.
– Durability: sau khi 1 transaction hoàn tất, ảnh
hưởng của nó sẽ cố định lâu dài trong hệ thống.
Course ILT
Transaction
 Để hoàn thành các yêu cầu của 4 tính chất
ACID trên, SQL Server cung cấp các chức
năng sau:
– Quản lý Transaction (Transaction
management)
– Khoá (Locking)
– Ghi nhật ký (Logging)
 Transaction log – là nhật ký được duy trì
bởi chính SQL Server để quản lý tất cả các
transaction
 Explicit transaction – là 1 transaction mà
việc khởi động và kết thúc transaction đó
đều được định nghĩa một cách tường
minh
Course ILT
Định nghĩa transaction
BEGIN TRAN[SACTION]
[transaction_name]
 Dùng để đánh dấu việc bắt đầu của 1
transaction
COMMIT [TRAN[SACTION]
[transaction_name]
Hay
COMMIT WORK
 Dùng để đánh dấu việc kết thúc của 1
transaction tường minh
Course ILT
Chuyển giao tự động các transaction –
Autocommit Transactions
 Mode chuyển giao tự động (Autocommit mode) là
mode quản lý transaction mặc định của SQL
Server.
 Một lệnh (statement) được chuyển giao
(committed) nếu nó thực hiện thành công hay sẽ
trả ngược về lại ban đầu (roll back) nếu nó gặp lỗi.
 Lệnh BEGIN TRANSACTION vượt quyền mode tự
động chuyển giao (autocommit) mặc định.
 SQL Server trở về lại mode autocommit khi
transaction tường minh đã được chuyển giao
(commit) hay trả ngược về đầu (roll back), hay khi
mode transaction ngầm định bị tắt.
Course ILT
Ví dụ
BEGIN TRANSACTION trnUpdatePosition
UPDATE Employee
SET cCurrentPosition = '0001'
WHERE cEmployeeCode= '000002'
UPDATE Position
SET iCurrentStrength = iCurrentStrength + 1
WHERE cPositionCode = '0001'
COMMIT TRANSACTION trnUpdatePosition
Course ILT
Làm thế nào để quay về lại trước
những thay đổi
ROLLBACK [TRAN[SACTION]
[transaction_name |savepoint_name ]
Dùng
để quay ngược một transaction
tường minh hay ngầm định về lại
điểm bắt đầu, hay về điểm dừng
(save-point) bên trong 1 transaction
Course ILT
Ví dụ
BEGIN TRANSACTION
USE Pubs
UPDATE Titles
SET Royalty = Royalty + 20
WHERE type LIKE 'busin%'
IF (SELECT MAX(Royalty) FROM Titles WHERE
Type LIKE 'busin%') >$25
BEGIN
ROLLBACK TRANSACTION
PRINT 'Transaction Rolled back'
END
ELSE
BEGIN
COMMIT TRANSACTION
PRINT 'Transaction Committed'
END
Course ILT
Tạo điểm dừng cho 1 TRANSACTION
 Lệnh SAVE TRANSACTION dùng để
đặt 1 điểm dừng (save point) bên
trong 1 transaction. Điểm dừng chia
transaction thành 1 các phần khác
nhau sao cho transaction có thể
quay về lại điểm dừng này nếu 1
phần của transaction bị loại bỏ có
điều kiện.
 Cú pháp
SAVE TRAN[SACTION]
{savepoint_name }
Course ILT
Thực thi một transaction với điểm dừng
BEGIN TRANSACTION
UPDATE Employee
SET cCurrentPosition = '0015'
WHERE cEmployeeCode = '000002'
UPDATE Position
SET iCurrentStrength = iCurrentStrength + 1
WHERE cPositionCode = '0015'
SAVE TRANSACTION trnTransaction1
UPDATE Requisition
SET siNoOfVacancy=siNoOfVacancy - 10
WHERE cRequisitionCode='000004'
UPDATE Position
SET iCurrentStrength=iCurrentStrength+10
WHERE cPositionCode='0015‘
Course ILT
Thực thi một transaction với điểm dừng
IF (SELECT iBudgetedStrength-iCurrentStrength
FROM Position WHERE cPositionCode = '0015') <0
BEGIN
PRINT 'Transaction 1 has been committed
but transaction 2 has not been committed.'
ROLLBACK TRANSACTION trnTransaction1
END
ELSE
BEGIN
PRINT 'Both the transactions have been
committed.'
COMMIT TRANSACTION
END
Course ILT
Sử dụng các transactions
 Việc nhóm 1 số lớn các lệnh hay
batch vào trong cùng 1 transaction có
thể cản trở việc thực thi hệ thống.
 Nếu COMMIT và BEGIN không nằm
trong cùng 1 batch, khi lỗi xảy ra, một
số batch sẽ vẫn tiếp tục thực thi. Điều
này có thể làm cho dữ liệu không nhất
quán (inconsistency).
 Các tài nguyên được dùng trong
transaction sẽ được giải phóng chỉ khi
transaction được hoàn tất.
Course ILT
Các lệnh không hợp lệ
 Rollback (quay về) phải có khả năng
“undo”, vì vậy các lệnh sau không được
dùng:
– CREATE DATABASE, ALTER DATABASE
– CREATE TABLE, ALTER TABLE, TRUNCATE
TABLE
– CREATE INDEX
– Tấr cả lệnh DROP
– SELECT...INTO
– GRANT or REVOKE
– DISK INIT, RECONFIGURE, LOAD DATABASE,
LOAD TRANSACTION
Ví dụ về sử dụng khoá
Course ILT
 User1 đang thực hiện các lệnh sau
để cập nhật điểm và ngày thi cho
ứng viên có mã là ‘000002’ trong bảng
ExternalCandidate.
BEGIN TRANSACTION
UPDATE ExternalCandidate
SET siTestScore = 90
WHERE cCandidateCode='000002'
UPDATE ExternalCandidate
SET dTestDate = getdate()
WHERE cCandidateCode = '000002'
Course ILT
Ví dụ về sử dụng khoá
 Trong khi transaction trên đang thực
hiện, User2 muốn lập lịch phỏng vấn cho
các ứng viên, nhưng không thể xem chi
tiết của các ứng viên có điểm thi trên 80.
User2 đang sử dụng các lệnh sau :
BEGIN TRANSACTION
SELECT * from ExternalCandidate
WHERE siTestScore > 80
UPDATE ExternalCandidate
SET dInterviewDate = getdate()+ 2
WHERE siTestScore > 80
Hãy xác định tại sao user2 không thể thực
thi transaction
Course ILT
Lock
 Các bảng sẽ bị khoá khi transaction
trên máy 1 đang thực hiện.
 Khi transaction trên máy 1 kết thúc
bằng cách dùng lệnh sau:
COMMIT TRANSACTION
Thì transaction trên máy 2 mới được
thực hiện.
Course ILT
Các bài toán đồng thờiConcurrency Problems
 Nếu không dùng khoá và nhiều user
cùng truy xuất vào 1 database, các rắc
rối có thể xảy ra nếu các transaction
sử dụng cùng lúc cùng một dữ liệu.
Các bài toán đồng thời bao gồm:
– Mất cập nhật (Lost updates).
– Phụ thuộc chưa được chuyển giao
(Uncommitted dependency).
– Phân tích không nhất quán (Inconsistent
analysis).
– Đọc ảo (Phantom reads)
Course ILT
Locking – Cơ chế khoá
 SQL Server sử dụng cơ chế khoá để bảo
đảm các giao dịch và tính nhất quán của
database.
 Locking để tránh cho người dùng khỏi đọc
dữ liệu đang bị thay đổi bởi các người dùng
khác, và tránh cho nhiều người dùng khỏi
thay đổi dữ liệu cùng lúc.
 Mặc dù SQL Server thực hiện cơ chế khoá
tự động, người dùng vẫn có thể thiêt kế các
ứng dụng hiệu quả hơn bằng cách thực
hiện các tùy biến về khoá.
Course ILT
Locking – Cơ chế khoá
 SQL Server có nhiều mức khóa khác nhau cho
phép các loại tài nguyên khác nhau được khoá bởi
transaction.
 Để giảm việc hao tổn khi thực hiện khóa, SQL
Server khoá tài nguyên một cách tự động ở mức
phù hợp với nhiệm vụ cần thực hiện.
 Việc khoá ở mức càng nhỏ, ví dụ ở mức các hàng
của bảng, làm tăng tính đồng thời, nhưng có phí
tổn cao bởi vì nhiếu khoá được tạo ra nếu nhiều
hàng được khoá.
 Việc khoá ở mức càng lớn, chẳng hạn mức bảng,
sẽ gây ra lãng phí khi xét đến tính đồng thời vì việc
khoá cả bảng sẽ hạn chế việc truy xuất đến bất kỳ
phần nào của bảng đó, nhưng chi phí sẽ giảm bởi
vì chỉ có 1 ít khoá cần được quản lý.
Các loại khoá
Course ILT
 SQL Server có thể tạo ra các loại khoá sau:
– RID (row identifier): khoá 1 hàng trong bảng
– Key: khoá 1 hàng trong bảng index
– Table: khoá tất cả các hàng và chỉ mục của 1
bảng
– Database: được dùng khi lưu trữ cả database
– Page: khoá 1 trang dữ liệu hay trang chỉ mục
– Extent: khoá 1 nhóm các trang trong lúc phân
phối không gian lưu trữ
Course ILT
Các kiểu Lock
 Shared Locks: cho phép các transaction
đồng thời cùng đọc chung 1 tài nguyên
 Update Locks: tránh khỏi bị deadlock
 Exclusive Locks: hạn chế các transaction
đồng thời khỏi truy xuất cùng một tài
nguyên
 Intent Locks: SQL Server muốn đạt đuợc
khoá loại shared hay exclusive trên 1 số
tài nguyên mức thấp hơn theo thứ tự
phân cấp
 Schema Locks: SQL Server xem xét các
khoá làm thay đổi chema khi bất kỳ lệnh
DDL (data definition language) được
thực thi trong bảng
Course ILT
Deadlock
 A deadlock là một hoàn cảnh mà trong
đó 2 user ( hay transaction) có các
khoá trên các đối tượng khác nhau, và
mỗi user đang chờ khoá trên đối
tượng của người dùng khác
Course ILT
Phát hiện và kết thúc Deadlocks
 Việc phát hiện deadlock được thực thi bởi 1 thread
riêng biệt để quản lý khoá.
 Thread quản lý khoá (lock monitor thread) quét qua
các phiên làm việc đang đợi khoá. Trong lúc quét
lần đầu, SQL Server đánh dấu cho tất cả các
phiên làm việc đang đợi tài nguyên. Khi SQL
Server quét qua các phiên làm việc ở lần thứ hai,
việc dò tìm deadlock đệ quy bắt đầu. Nếu phát hiện
ra có 1 chuỗi các yêu cầu khoá, SQL Server loại bỏ
transaction nào mà ít tốn kém nhất và đánh dấu
transaction đó như 1 nạn nhân của deadlock
(deadlock victim).
 Nhờ vào cơ chế quét các session để phát hiện
deadlock, SQL Server kết thúc deadlock nhờ chọn
một cách tự động 1 user nào đó làm nạn nhân của
deadlock.
Course ILT
Khái quát về trigger
 SQL Server cung cấp hai cơ chế cơ
bản để bảo đảm các quy tắc nghiệp vụ
(business rules) và bảo toàn dữ liệu (
data integrity):
– Các ràng buộc (Constraint)
– Triggers.
 Trigger là một loại thủ tục đặc biệt
chạy tự động khi dữ liệu trong 1 bảng
đặc biệt nào đó bị chỉnh sửa hay 1 sự
kiện đặc biệt nào đó xảy ra.
Course ILT
So sánh giữa trigger và Constraint
 Trigger có thể thực hiện mọi chức năng của
constraint; tuy nhiên không phải lúc nào
dùng trigger cũng là cách tốt nhất
 Các trigger thường dùng nhất khi một số
quy tắc nghiệp vụ không thể thực hiện
được bởi các constraint. Xét ví dụ sau:
– Constraint loại CHECK có thể được dùng để xét
tính hợp lệ của 1 cột này hay cột khác trong
cùng bảng. Nhưng nếu ứng dụng cần xét tính
hợp lệ của 1 cột trong bảng với 1 cột trong bảng
khác thì không thể dùng constraint được mà
phải dùng trigger.
– Các constraint có thể thông báo lỗi thông qua
hệ thống thông báo lỗi tiêu chuẩn. Nếu ứng
dụng yêu cầu các thông báo tùy chọn và quản
lý lỗi phức tạp hơn, ta cần phải dùng trigger.
Course ILT
Các tính chất của Trigger
 Một trigger là một khối mã chứa 1 tập
hợp các lệnh T-SQL sẽ được kích hoạt
để đáp ứng một hành động ( action) nào
đó.
 Các trigger có thể lồng nhau lên đến 32
mức.
Các tính chất của Trigger
Course ILT
• Nó được kích hoạt tự động bởi
SQL server khi bất kỳ một lệnh
sửa đổi dữ liệu được thực hiện
• Nó giúp tránh những thay đổi dữ
liệu hông đúng, không nhất quán
• Nó không thể gọi một cách tường
minh như 1 thủ tục thông thường
(stored procedure)
• Nó không thể trả về dữ liệu cho
người dùng
Course ILT
Tạo trigger
 Trigger thực thi phụ thuộc vào hành
động mà trigger được tạo.
 Có 2 loại trigger:
– FOR/AFTER: sẽ kích hoạt sau khi lệnh
INSERT, UPDATE hay DELETE được
thực thi đối với bảng
– INSTEAD OF: sẽ được thực thi thay cho
lệnh làm cho trigger hoạt động
 Mỗi trigger sẽ được kết hợp với 1
bảng hay view
Tạo trigger
Course ILT
 Cú pháp
CREATE TRIGGER trigger_name
ON table_name
FOR [INSERT | DELETE | UPDATE]
AS sql_statements
 Thông tin liên quan đến trigger
được viết vào các bảng hệ thống
sysobjects và syscomments
Course ILT
Các bảng Magic
 Bất kỳ lúc nào một trigger hoạt động để
đáp ứng lệnh INSERT, DELETE, hay
UPDATE, hai bảng đặc biệt được tạo ra.
Đó là các bảng magic, có tên là inserted
và deleted
• Bảng inserted chứa 1 bản sao tất cả các bản
ghi được chèn vào bảng trigger.
• Bảng deleted chứa 1 bản sao tất cả c1c bản
ghi vừa được xoá khỏi bảng trigger
• Bất kể khi nào lệnh cập nhật được thực hiện,
trigger sẽ sử dụng cả hai bảng inserted và
deleted.
Course ILT
Bảng Inserted and deleted
Tạo một trigger cho lệnh INSERT
Course ILT
 Trigger dùng cho lệnh INSERT sẽ
được kích hoạt bất cứ lúc nào có 1
bản ghi mới được chèn vào bảng
trigger
 Khi lệnh INSERT được thực hiện,
một hàng mới sẽ được thêm vào cả
hai bảng: bảng trigger và bảng
inseerted.
Course ILT
Ví dụ của insert trigger
CREATE TRIGGER trgInsertRequisition
ON Requisition FOR insert AS
DECLARE @VacancyReported int
DECLARE @ActualVacancy int
SELECT @ActualVacancy =
iBudgetedStrength - iCurrentStrength
FROM Position Join Inserted on
Position.cPositionCode = Inserted.cPositionCode
SELECT @VacancyReported = inserted.siNoOfVacancy
FROM inserted
IF(@VacancyReported > @Actualvacancy)
BEGIN
PRINT 'The actual vacancies are less than the vacancies
reported. Hence, cannot insert.'
ROLLBACK TRANSACTION
END
RETURN
Ví dụ của insert trigger
Course ILT
 Chạy thử trigger
INSERT Requisition
VALUES('000003','0001',getdate(), getdate()
+ 7, '0001', 'North',20)
Course ILT
Trigger cho lệnh DELETE
 Trigger của lệnh DELETE sẽ được
kích hoạt bất kỳ lúc nào có lệnh xoá
các hàng khỏi bảng trigger
 Có 3 cách để thực thi việc bảo toàn
tham chiếu bằng cách dùng trigger
của lệnh DELETE
• Phương pháp xoá song song (Cascade)
• Phương pháp hạn chế (Restrict)
• Phương pháp gán null (Nullify)
Course ILT
Ví dụ DELETE trigger
CREATE TRIGGER trgDeleteContractRecruiter
ON ContractRecruiter FOR delete
AS
PRINT 'Deletion of Contract Recruiters is not
allowed'
ROLLBACK TRANSACTION
RETURN
Course ILT
Trigger của lệnh UPDATE
 Trigger này đựơc kích hoạt bất kể lúc nào có 1
sửa đổi nào đó trong bảng trigger
 Ví dụ
CREATE TRIGGER trgUpdateContractRecruiter
ON ContractRecruiter FOR UPDATE
AS
DECLARE @AvgPercentageCharge int
SELECT @AvgPercentageCharge =
avg(siPercentageCharge)
FROM ContractRecruiter
IF(@AvgPercentageCharge > 11)
BEGIN
PRINT 'The average cannot be more than 11'
ROLLBACK TRANSACTION
END
RETURN
Course ILT
Course ILT
Hàm Update
CREATE TRIGGER trgUpdatePub
ON Publishers FOR UPDATE
AS
IF UPDATE (Pub_Id)
BEGIN
PRINT 'Publisher ID cannot be modified'
ROLLBACK TRAN
END
Trigger và bảo toàn dữ liệu
Course ILT

Trigger có thể được dùng để thực hiện các quy tắc
nghiệp vụ và các quy luật bảo toàn dữ liệu.
• Nếu có bất kỳ thay đổi nào trong bảng chính (master table)
thì trigger sẽ làm cho những thay đổi đó cũng xảy ra song
song trong bảng phụ thuộc (dependent table)
Ví dụ 1:
CREATE TRIGGER trgDeleteTitle
ON Titles FOR DELETE
AS
DELETE TitleAuthor FROM TitleAuthor t JOIN Deleted d
ON t.Title_Id = d.Title_Id
• Nếu bất kỳ thay đổi nào vi phạm quy luật bảo toàn tham
chiếu,thì trigger sẽ làm cho tất cả các thay đổi này bị từ
chối, và loại trừ mọi cố gắng sửa đổi dữ liệu trong
database
• Trigger cho phép thực hiện các ràng buộc phức tạp
• Trigger có thể thực hiện 1 hành động đặc biệt phụ thuộc
vào các chỉnh sửa xảy ra trong bảng
Course ILT
Ví dụ 2
CREATE TRIGGER trgUpdateDelete
ON TitleAuthor
FOR INSERT, UPDATE
AS
/* Kiểm tra sự tồn tại của title ID trong bảng titles */
If (SELECT COUNT (*) FROM Titles t JOIN inserted i
ON t.Title_Id = i.Title_Id) = 0
BEGIN
PRINT 'Invalid title ID entered.'
ROLLBACK TRAN
END
/* Kiểm tra sự tồn tại của author ID trong bảng authors */
If (SELECT COUNT(*) FROM Authors t JOIN inserted i
ON t.Au_Id = i.Au_Id) = 0
BEGIN
PRINT 'Invalid author ID entered.'
ROLLBACK TRAN
END
Course ILT
Sử dụng nhiều trigger
 Có thể tạo ra nhiều trigger cho cùng 1
lệnh DML trên cùng 1 bảng
 Việc sử dụng nhiều trigger sẽ giúp
người dùng có thể thực thi nhiều quy
tắc nghiệp vụ, mỗi quy tắc được thực
thi bằng 1 trigger
 Các trigger được thực thi theo thứ tự
mà chúng được tạo ra.
Course ILT
Các trigger AFTER và INSTEAD OF
 Trigger AFTER có thể được tạo ra cho bất
kỳ bảng nào với các lệnh INSERT,
UPDATE, hay DELETE tương tự như các
trigger thông thường khác.
 Trigger AFTER sẽ kích hoạt sau khi lệnh
DML liên quan tới nó được thực thi
 Trigger INSTEAD OF có thể được dùng để
thực thi một hành động như lệnh DML trên
1 bảng hay view khác. Trigger này có thể
được tạo ra cho cả bảng và view
 Khác với trigger AFTER, không thể tạo
nhiều hơn 1 trigger INSTEAD OF cho 1
lệnh DML trên cùng 1 bảng hay view
Course ILT
Các ví dụ
 Ví dụ 1
CREATE TRIGGER trgDeleteTitles
ON Titles AFTER DELETE
AS
print 'Deletion successful'
 Ví dụ 2
CREATE TRIGGER trgPublisherDelete
ON Publishers INSTEAD OF DELETE
AS
print 'Master records cannot be deleted'
Trigger và lệnh update view
Course ILT
 Hãy khảo sát view sau:
CREATE VIEW vwEmployeeCandidate
AS
SELECT Employee.cCandidateCode, vFirstName,
vLastName, cPhone, siTestScore
FROM Employee JOIN InternalCandidate
ON Employee.cCandidateCode =
InternalCandidate.cCandidateCode
 Nếu muốn cập nhật view bằng lệnh sau thì
sẽ nhận được thông báo lỗi:
UPDATE vwEmployeeCandidate
SET cPhone = '(614)324-1111', siTestScore = 90
WHERE cCandidateCode = '000018'
Lý do????
Trigger và lệnh update view
Course ILT

Tạo trigger INSTEAD OF sau:
CREATE TRIGGER trgEmployeeCandidate
ON vwEmployeeCandidate INSTEAD OF UPDATE
AS
DECLARE @Phone char(15)
DECLARE @TestScore int
DECLARE @CandidateCode char(6)
SET @Phone= (SELECT cPhone FROM INSERTED)
SET @TestScore = (SELECT siTestScore FROM INSERTED)
SET @CandidateCode = (SELECT cCandidateCode FROM
INSERTED)
UPDATE Employee
SET cPhone=@Phone
WHERE cCandidateCode=@CandidateCode
UPDATE InternalCandidate
SET siTestScore = @TestScore
WHERE cCandidateCode=@CandidateCode
Lệnh update cho view trên sẽ được thực thi mà không có lỗi
nào
Course ILT
Chỉnh sửa trigger
ALTER TRIGGER
[owner.]trigger_name
ON [owner.]table_name
FOR | AFTER | INSTEAD OF
[DELETE] [[,] INSERT]
[[,]UPDATE]
AS
[IF UPDATE (column) |
[[AND | OR] UPDATE (column]
sql_statements
Một số lệnh khác về triggers
Course ILT
 Để cấm tất cả trigger của 1 bảng:
ALTER TABLE table_name DISABLE TRIGGER
ALL | trigger_name
 Để cho phép tất cả các trigger của 1
bảng:
ALTER TABLE table_name ENABLE TRIGGER
ALL | trigger_name
 Để xoá trigger:
DROP TRIGGER trigger_name