Bài giảng Cơ sở dũ liệu - Chương 13: Transaction và Triggers

à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áSQL Server có thể tạo ra các loại khoá sau:RID (row identifier): khoá 1 hàng trong bảngKey: khoá 1 hàng trong bảng indexTable: khoá tất cả các hàng và chỉ mục của 1 bảngDatabase: được dùng khi lưu trữ cả database Page: khoá 1 trang dữ liệu hay trang chỉ mụcExtent: khoá 1 nhóm các trang trong lúc phân phối không gian lưu trữCác kiểu LockShared Locks: cho phép các transaction đồng thời cùng đọc chung 1 tài nguyênUpdate Locks: tránh khỏi bị deadlockExclusive Locks: hạn chế các transaction đồng thời khỏi truy xuất cùng một tài nguyênIntent 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ấpSchema 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ảngDeadlockA 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ácPhá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. Khái quát về triggerSQL 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. So sánh giữa trigger và ConstraintTrigger 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ấtCá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. Các tính chất của TriggerMộ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• 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ùngTạo triggerTrigger 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ảngINSTEAD OF: sẽ được thực thi thay cho lệnh làm cho trigger hoạt độngMỗi trigger sẽ được kết hợp với 1 bảng hay viewTạo triggerCú phápCREATE TRIGGER trigger_nameON table_nameFOR [INSERT | DELETE | UPDATE]AS sql_statementsThông tin liên quan đến trigger được viết vào các bảng hệ thống sysobjects và syscommentsCác bảng MagicBấ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.Bảng Inserted and deleted Tạo một trigger cho lệnh INSERTTrigger 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 triggerKhi 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.Ví dụ của insert triggerCREATE TRIGGER trgInsertRequisitionON Requisition FOR insert ASDECLARE @VacancyReported intDECLARE @ActualVacancy intSELECT @ActualVacancy = 	iBudgetedStrength - iCurrentStrengthFROM Position Join Inserted onPosition.cPositionCode = Inserted.cPositionCodeSELECT @VacancyReported = inserted.siNoOfVacancyFROM insertedIF(@VacancyReported > @Actualvacancy)BEGIN	PRINT 'The actual vacancies are less than the vacancies	reported. Hence, cannot insert.'	ROLLBACK TRANSACTIONENDRETURNVí dụ của insert triggerChạy thử triggerINSERT RequisitionVALUES('000003','0001',getdate(), getdate() + 7, '0001', 'North',20)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)Ví dụ DELETE triggerCREATE TRIGGER trgDeleteContractRecruiterON ContractRecruiter FOR deleteASPRINT 'Deletion of Contract Recruiters is not allowed'ROLLBACK TRANSACTIONRETURNTrigger 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 triggerVí dụCREATE TRIGGER trgUpdateContractRecruiterON ContractRecruiter FOR UPDATEASDECLARE @AvgPercentageCharge intSELECT @AvgPercentageCharge = avg(siPercentageCharge)FROM ContractRecruiterIF(@AvgPercentageCharge > 11)BEGIN	PRINT 'The average cannot be more than 11'	ROLLBACK TRANSACTIONENDRETURNHà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 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ảngVí dụ 2CREATE 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 Sử dụng nhiều triggerCó thể tạo ra nhiều trigger cho cùng 1 lệnh DML trên cùng 1 bảngViệ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 triggerCác trigger được thực thi theo thứ tự mà chúng được tạo ra.Các trigger AFTER và INSTEAD OFTrigger 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 thiTrigger 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à viewKhá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 viewCác ví dụVí dụ 1CREATE TRIGGER trgDeleteTitles ON Titles AFTER DELETE ASprint 'Deletion successful' Ví dụ 2CREATE TRIGGER trgPublisherDelete ON Publishers INSTEAD OF DELETE ASprint 'Master records cannot be deleted' Trigger và lệnh update viewHãy khảo sát view sau:CREATE VIEW vwEmployeeCandidate ASSELECT 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 viewTạo trigger INSTEAD OF sau:CREATE TRIGGER trgEmployeeCandidate ON vwEmployeeCandidate INSTEAD OF UPDATE ASDECLARE @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àoChỉnh sửa triggerALTER TRIGGER [owner.]trigger_nameON [owner.]table_name	FOR | AFTER | INSTEAD OF [DELETE] [[,] INSERT] [[,]UPDATE]	AS 	[IF UPDATE (column) | 	[[AND | OR] UPDATE (column] 	sql_statementsMộ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