1 / 50

Chương 13: Transaction và Triggers

Chương 13: Transaction và Triggers. 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. 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

elan
Download Presentation

Chương 13: Transaction và Triggers

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chương 13: Transaction và Triggers

  2. 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

  3. 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).

  4. 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.

  5. 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

  6. Đị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

  7. 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.

  8. 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

  9. 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

  10. 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

  11. 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 }

  12. 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‘

  13. 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

  14. 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.

  15. 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

  16. Ví dụ về sử dụng khoá • 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'

  17. 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

  18. 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.

  19. Các bài toán đồng thời- Concurrency 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)

  20. 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á.

  21. 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ý.

  22. Các loại khoá • 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ữ

  23. 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

  24. 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

  25. 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.

  26. 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.

  27. 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.

  28. 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.

  29. Các tính chất của Trigger • 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

  30. 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

  31. Tạo trigger • 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

  32. 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.

  33. Bảng Inserted and deleted

  34. Tạo một trigger cho lệnh INSERT • 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.

  35. 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

  36. Ví dụ của insert trigger • Chạy thử trigger INSERT Requisition VALUES('000003','0001',getdate(), getdate() + 7, '0001', 'North',20)

  37. 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)

  38. Ví dụ DELETE trigger CREATE TRIGGER trgDeleteContractRecruiter ON ContractRecruiter FOR delete AS PRINT 'Deletion of Contract Recruiters is not allowed' ROLLBACK TRANSACTION RETURN

  39. 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

  40. 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

  41. Trigger và bảo toàn dữ liệu • 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

  42. 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

  43. 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.

  44. 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

  45. 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'

  46. Trigger và lệnh update view • 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????

  47. Trigger và lệnh update view • 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

  48. 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

  49. Một số lệnh khác về triggers • Để 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

More Related