Web888.vn
  • Khám phá
  • Kiến thức công nghệ
    • Học lập trình
      • Lập trình C/C++
      • Lập trình HTML
      • Lập trình Javascript
        • ReactJS framework
        • AngularJS framework
      • Cơ sở dữ liệu
        • Micrsoft SQL Server
      • Lập trình PHP
        • Lập trình PHP cơ bản
        • Laravel Framework
    • WORDPRESS
      • WordPress cơ bản
      • WordPress nâng cao
      • Chia sẻ WordPress
    • Machine Learning
    • Marketing
      • Google Adwords
      • Facebook Ads
      • Kiến thức khác
    • Tin học văn phòng
      • Microsoft Word
      • Microsoft Excel
  • Kinh doanh online – MMO
    • Tiếp thị liên kết
    • Drop shipping
    • Google Adsense
    • Kiếm tiền youtube
    • Tiền điện tử
    • Thương mại điện tử
  • Chia sẻ IT
    • Chia sẻ phần mềm
    • Review công nghệ
    • Công cụ – tiện ích
      • Kiểm tra bàn phím online
      • Kiểm tra webcam online
  • Kỹ năng sống
Đăng nhập
  • Đăng nhập / Đăng ký

Please enter key search to display results.

Home
  • Micrsoft SQL Server
Trigger trong SQL Server

Trigger trong SQL Server

  • 07-06-2022
  • Toanngo92
  • 0 Comments

Trigger dịch nôm sang tiếng Việt có nghĩa là cò súng, ý muốn nói nó như một cây cò súng và sẽ kích hoạt khi có một hành động kéo cò tác động vào.

Trong SQL, Trigger là một kiểu stored procedure mà được thực thi khi cố gắng sửa đổi dữ liệu trong bảng mà trình kích hoạt được tạo. Không giống các system stored procedure thông thường, trigger không thể thực thi trực tiếp, cũng không truyền hoặc nhận vào tham số.

Mỗi table thường sẽ có 3 thao tác làm thay đổi dữ liệu đó là: UPDATE, INSERT, DELETE. Và đôi khi mỗi hành động như vậy ta sẽ có những ràng buộc trên bảng để giúp bảo toàn dữ liệu, lúc này sử dụng trigger là một giải pháp tốt

Mô tả trigger

Mục lục

  • Các kiểu Triggers
  • DML Triggers
    • Giới thiệu về Inserted và Deleted Tables
      • Inserted table
      • Deleted table
    • Insert Triggers
    • Update Triggers
    • Delete Trigger
    • AFTER Triggers
    • INSTEAD OF Triggers
    • Thứ tự thực thi của DML Triggers
    • Xem định nghĩa của DML Trigger
    • ALTER DML trigger
  • DROP DML Trigger
    • DDL Triggers
    • Phạm vi DDL Trigger
      • Database-scoped DDL triggers
      • Server-Scoped DDL triggers
    • Nested trigger
    • Hàm UPDATE()
    • Xử lý nhiều hàng(records) trong một phiên
    • LOGON Triggers
  • Hiệu suất trigger

Các kiểu Triggers

Triggers sẽ được tự động thực thi khi sự kiện bắt gặp trong bảng hoặc view. Ngôn ngữ sự kiện được chia ra 2 loại là DML và DDL events. Triggers liên kết với sự kiện DML được gọi là DML triggers, ngược lại gọi là DDL triggers.

DML Triggers

DML triggers được thực thi khi hoàn thành một sự kiện DML hoặc thay cho sự kiện DML. Những triggers này đảm bảo tính toàn vẹn tham chiếu bằng cách xếp tầng các thay đổi đối với các bảng có liên quan khi một hàng được sửa đổi.

DML triggers có 3 loại chính:

  • INSERT trigger
  • UPDATE trigger
  • DELETE trigger

Giới thiệu về Inserted và Deleted Tables

Câu lệnh SQL trong DML triggers sử dụng 2 loại bảng đặc biệt để sửa đổi database. Khi dữ liệu được thêm vào, cập nhậật hoặc xóa, SQL Server tạo và quản lý các bảng này tự động, bảng tạm lưu trữ các dữ liệu gốc cũng như dữ liệu sửa đổi như sau:

Inserted table

Inserted table lưu trữ các bản coppy của những hàng được áp dụng thông qua câu lệnh INSERT và UPDATE. Trong quá trình insert hoặc update,, hàng mới sẽ đưojc thêm vào cả 2 bảng là inserted table và trigger table. Hàng trong inserted table là bản copy của hàng mới trong trigger table.

Deleted table

Deleted table lưu trữ bản copy của các hàng được áp dụng thông qua câu lệnh DELETE và UPDATE. Trong quá trình thực thi DELETE hoặc UPDATE, hàng sẽ đưỡ xóa khỏi trigger table và chuyển tới deleted table.

Lưu ý: 2 bảng inserted table và deleted table là tạm thời, để lưu trú bộ nhớ và kiểm tra ảnh hưởng của các sửa đổi dữ liệu nhất định và đặt điều kiện cho các hành động DML trigger. SQL Server 2019 không cho phép tham chiếu cột văn bản, nxtext hoặc hình ảnh trong các bảng được chèn và xóa cho các trigger AFTER.

Insert Triggers

Một INSERT trigger được thực thi khi một bảng ghi mới được insert vào bảng. INSERT trigger đảm bảo rằng giá trị nhập vào thỏa mãn nhưng ràng buộc định nghĩa ra trong bảng.

Khi người dùng nhập liệu một bản ghi vào trong bảng, INSERT trigger lưu copy của những record này vào trong inserted table. Nó sau đó kiểm tra liệu giá trị moiws có thỏa mãn các ràng buộc trong bảng hay không.

Nếu bản ghi hợp lệ, INSERT trigger sẽ thêm hàng vào trong bảng, nếu không nó sẽ hiển thị thông báo lỗi. Một INSERT trigger được tạo thông qua từ khóa INSERT trong câu lệnh CREATE TRIGGER và ALTER TRIGGER.

Cú pháp INSERT|UPDATE|DELETE trigger:

CREATE TRIGGER [schema_name.]trigger_name ON [schema_name.]table_name [WITH ENCRYPTION] [FOR INSERT] AS [IF UPDATE (column_name)] [{AND|OR} UPDATE (colum_name)] 
AS
BEGIN
 <sql_statement>
END;

Ví dụ:

Bước 1 – tạo 2 bảng theo thông tin sau:

CREATE TABLE Locations (LocationID int, LocName varchar(100));
CREATE TABLE LocationHistory (LocationID int, ModifedDate DATETIME);

Bước 2 – tạo INSERT trigger tên là TRIGGER_INSERT_Locations cho bảng Locations

CREATE TRIGGER TRIGGER_INSERT_Locations ON Locations 
FOR INSERT
NOT FOR REPLICATION
AS
BEGIN
INSERT INTO LocationHistory
SELECT LocationID , getdate()
FROM inserted
END;

Bước tiếp theo test trigger câu lệnh:

insert into dbo.Locations values(1,'Ha Noi');
Messages trả ra sau khi insert locations

Trong trigger trên, khi insert dữ liệu của locations, dữ liệu LocationHistory được thêm vào dựa theo dữ liệu của Locations.

Trong đây có một từ khóa mới Replication là công nghệ sao chép, phân phối dữ liệu và là một trong những giải pháp khôi phục dữ liệu sau thảm họa có sẵn trong SSMS rất hữu ích để duy trì bản sao thứ hai hoặc bản sao dự phòng của các đối tượng (Table, View, Stored Procedure) và CSDL. Ý nghĩa NOT FOR REPLICATION là không sử dụng cho tình huống replication.

Update Triggers

Câu lệnh UPDATE trigger copy bản ghi gốc vào trong bảng Deleted và copy bản ghi mới vào bảng inserted khi bản ghi được cập nhật. Sau đó nó sẽ đánh giá bản ghi mới để nhận diện nếu giá trị có thỏa mãn điều kiện ràng buộc trong bảng không.

Nếu giá trị mới hợp lệ, bản ghi từ inserted table sẽ được copy vào bảng trigger. Tuy nhiên, nếu giá trị mới không hợp lệ, một error message sẽ được in ra cho ngờời dùng. Vì vậy, các bản ghi gốc đưojc copy từ Deleted table lại vào trigger table.

Một UPDATE trigger có thể được tạo băng cách sử dụng từ khóa UPDATE trong lệnh CREATE TRIGGER hoặc ALTER TRIGGER.

Ví dụ:

CREATE TRIGGER TRIGGER_UPDATE_Locations ON Locations
FOR UPDATE
NOT FOR REPLICATION
AS
BEGIN
INSERT INTO LocationHistory
SELECT LocationID, getdate() FROM inserted
END;
GO
update Locations SET LocName = 'Ho Chi Minh' where [LocName] = 'Ha Noi';

Sau khi chạy lệnh tạo trigger trên và update dữ liệu vào bảng Locations, bảng LocationHistory được thêm mới dữ liệu khi đữ liệu bảng Locations được cập nhật.

Delete Trigger

Delete trigger được tạo để hạn chế người dùng khỏi việc xóa bản ghi cụ thể khỏi bảng

Tuần tự thực thi sau sẽ xảy ra khi người dùng cố gắng xóa bản ghi:

  • Bản ghi được xóa khỏi bảng trigger và thêm vào bảng Deleted.
  • Nó sẽ kiểm tra so khớp ràng buộc khi xóa dữ liệu
  • Nếu có ràng buộc ở bản ghi ngăn chặn việc xóa, DELETE trigger sẽ hiển thị thông báo lỗi
  • Bản ghi bị xóa lưu trữ trong bảng Deleted sẽ được copy lại vào bảng trigger.

Ví dụ:

CREATE TRIGGER TRIGGER_DELETE_Locations ON Locations
FOR DELETE
NOT FOR REPLICATION
AS
BEGIN
INSERT INTO LocationHistory
SELECT LocationID, getdate()
FROM deleted
END;
GO
DELETE FROM dbo.Locations WHERE LocName = 'Ho Chi minh';

Sau khi thực thi delete, trigger sẽ làm thêm việc thêm mới bản ghi vào bảng LocationsHistory.

AFTER Triggers

Một AFTER trigger được thực thi sau khi hoàn thành nghiệp vụ INSERT,UPDATE,DELETE. Một bảng có thể có nhiều trigger AFTER được định nghĩa cho mỗi hoạt động INSERT, UPDATE, DELETE. Nếu nhiều trigger được tạo trong cùng một bảng, ngời dùng phải định nghĩa thức tự thực thi trigger thực thi. Một AFTER trigger đưojc thực thi khi các ràng buộc kiểm tra hooàn thành, vì vậy, trigger sẽ được thực thi sau khi bảng Inserted và Deleted được tạo.

Tuần tự thực thi của TRIGGER AFTER

Cú pháp:

CREATE TRIGGER <trigger_name> ON <table_name>
[WITH ENCRYPTION]
[FOR | AFTER]
{ [INSERT] [,] [UPDATE] [,] [DELETE] }
AS BEGIN <sql_statement> END;

Ví dụ:

CREATE TRIGGER AFTER_INSERT_Locations ON Locations
AFTER INSERT 
AS
BEGIN
INSERT INTO LocationHistory
SELECT LocationID
,getdate()
FROM inserted
END;

Sau khi thực thi, nó sẽ insert vào bảng LocationHistory sau khi insert Locations thành công, sự khác biệt ở trigger after và trigger on chỉ nằm ở thời điểm thực thi trigger.

INSTEAD OF Triggers

INSTEAD OF trigger sẽ đưojc thực thi thay cho hoạt động INSERT,UPDATE,DELETE khi được sử dụng. Một bảng hoặc view chỉ có thể có duy nhất một INSTEAD OF trigger được định nghĩa cho mỗi hoạt động INSERT, UPDATE , DELETE.

INSTEAD OF trigger sẽ được thực thi trước khi kiểm tra ràng buộc được thực thi trên bảng, trigger này được thực thi sau khi bảng inserted và deleted được tạo.

Ví dụ:

CREATE TRIGGER INSTEAD_OF_DELETE_Locations ON Locations
INSTEAD OF DELETE
AS
BEGIN
SELECT 'example instead trigger' as Message
END;
GO
DELETE FROM dbo.Locations
WHERE LocName='Ha Noi'

Khi câu lệnh delete thực thi, INSTEAD OF trigger được kích hoạt và khối lệnh bên trong trigger được thực thi, và hành động xóa không xảy ra.

Thứ tự thực thi của DML Triggers

SQL Server cho phép người dùng xác định AFTER trigger nào sẽ được thực thi trước và cái nào sẽ thực thi sau. Tất cả các AFTER trigger khiđược gọi giữa các trình kích hoạt đầu tiên và cuối cùng không có thứ tự thực thi nhất định.

Sử dụng sp_settriggerorder stored procedure để định nghĩa thứ tự cửa DML Trigger,

Thứ tự thực thi DML trigger

Cú pháp xác định thứ tự thực thi AFTER DML trigger:

sp_settriggerorder [@triggername=] '[triggerschema.]triggername', [@order=]'value', [@stmmttype=]'statement_type'

Ví dụ:

EXEC sp_settriggerorder @triggername = 'TRIGGER_DELETE_Locations', @order='FIRST', @stmttype='DELETE'

Xem định nghĩa của DML Trigger

Để xem định nghĩa trigger thông qua stored procedure sp_hepltext
Cú pháp:

sp_helptext '<trigger_name>';

Ví dụ:

sp_helptext TRIGGER_DELETE_Locations

ALTER DML trigger

Có 2 cách để sửa trigger:

  • DROP và RECREATE lại trigger với parameter mới
  • Thay đổi trigger với cú pháp ALTER TRIGGER

Cú pháp:

ALTER TRIGGER <trigger_name>
ON {<table_name>|<view_name>}
[WITH ENCRYPTION]

{FOR|ALTER|INSTEAD OF}
{ [INSERT] [,] [UPDATE] [,] [DELETE] }
AS <sql_statement>

Ví dụ:

ALTER TRIGGER TRIGGER_UPDATE_Locations ON Locations
WITH ENCRYPTION FOR INSERT
AS
IF 'Ho Chi Minh' IN (SELECT LocName FROM inserted)
BEGIN 
PRINT 'Location can not be updated'
ROLLBACK TRANSACTION
END;

DROP DML Trigger

Cú pháp:

DROP TRIGGER <DML_trigger_name> [,...n]

Ví dụ:

DROP TRIGGER TRIGGER_UPDATE_Locations

DDL Triggers

Một Data Definition Language (DDL) trigger thực thi stored procedure khi sự kiện DDL như CREATE,ALTER,DROP xuất hiện trong database hoặc server. DDL triggers chỉ có thể vận hành khi sự kiện DDL hoàn thành.
DDL trigger có thể sử dụng để ngăn chặn chỉnh sửa trong database schema.
DDL trigger có thể gọi một sự kiện hoặc hiển thị một tin nhắn dựa trên thông báo của schema. DDL trigger được định nghĩa hoặc ở tầng database hoặc ở tầng server.

Cú pháp:

CREATE TRIGGER <trigger_name> ON {ALL SERVER | DATABASE} [WITH ENCRYPTON] {FOR | AFTER} <event_type> } AS <sql_statement>

Ví dụ:

CREATE TRIGGER Secure ON DATABASE
FOR DROP_TABLE, ALTER_TABLE AS
PRINT 'You must disable trigger scure to drop or alter table'
ROLLBACK;

Với đoạn code này, DDL trigger được tạo cho câu lệnh DROP TABLE hoặc ALTER TABLE.

Phạm vi DDL Trigger

DDDL trigger được gọi bởi câu lệnh sql được thực thi trong database hoặc server hiện tại. Ví dụ, một DDL trigger được tạo cho câu lệnh CREATE TABLE thực thi trên sự kiện CREATE TABLE trên database.
DDL trigger tạo cho câu lệnh CREATE LOGIN thực thi trên sự kiện LOGIN trên server.

Phạm vi của DDL trigger phụ thuộc vào liệu trigger thực thi cho sự kiện database hay sự kiện server, trigger được chia thành 2 loại như sau:

Database-scoped DDL triggers

Database-scoped DDL trigger được gọi bởi sự kiện chỉnh sửa database schema. Những trigger này được lưu trữ trong database và thực thi ở sự kiện DDL, ngoại trừ những thứ liên kết với bảng tạm.

Server-Scoped DDL triggers

Server scoped DDL trigger được gọi bởi sự kiện DDL ở tầng server. Những trigger này được lưu trữ trong database master.

Nested trigger

Cả DDL và DML trigger đều có khái niệm nà khi trigger triển khai một hành động mà nó khởi tạo một trigger khác. DDL và DML trigger có thể được lồng nhau tới 32 tầng. Giả sử nếu trigger chỉnh sửa một bảng trên đó có mộ trigger khác, trigger thứ 2 được khởi tạo, sau đó sẽ gọi trigger thứ 3 và tiếp tục …

Nếu trigger lồng nhau đươc cho phép chạy, các trigger sẽ tuần tự bắt đầu như một vòng lặp vô hạn, nếu nó đạt tới tầng lông cuối cùng, trigger sẽ chấm dứt.

Trigger lồng nhau có thể sử dụng để xử lý các chức năng như lưu trữ hoặc backup các bản ghi bị ảnh hưởng bởi hành động trước đó.

Người dùng có thể disable nested trigger, bằng cách cấu hình nested triggers option thông qua stored procedure sp_configure thành 0 hoặc OFF. Mặc định cấu hình sẽ cho phép nested trigger.

Ví dụ:

GO
CREATE TRIGGER Employee_Deletion ON HumanResources.Employee
AFTER DELETE
AS
BEGIN
PRINT 'Deletion will affect EmployeePayHistory table'
DELETE FROM EmployeePayHistory WHERE BusinessEntityID IN (SELECT BusinessEntityID FROM deleted)
END;
GO
CREATE TRIGGER Deletion_Comnfirmation
ON HumanResources.EmployeePayHistory AFTER DELETE
AS
BEGIN
PRINT 'Employe details successfully deleted from EmployeePayHistory table'
END;
DELETE FROM HumanResources.EmployeePayHistory WHERE BusinessEntityID=1

Hàm UPDATE()

Hàm UPDATE() trả về giá trị boolean xác định liệu UPDATE hay INSERT đã thực thi trên view hoặc cột của table chưa.

Hàm UPDATE() có thể sử dụng ở bất kỳ đâu bên trong thân của trigger UPDATE hoặc INSERT để kiểm tra kiểm tra xem trình kích hoạt có nên thực hiện một số hành động hay không.

Cú pháp:

UPDATE (column);

Ví dụ:

CREATE TRIGGER Accounting ON Production.TransactionHistory AFTER UPDATE
AS
IF (UPDATE(TransactionID) OR UPDATE(ProductID)) 
BEGIN
RAISEERROR(5009,16,10)
END;
GO

Xử lý nhiều hàng(records) trong một phiên

Khi một người dùng viết code cho DML trigger, các câu lệnh sẽ dựa vào trigger để kích hoạt các câu lệnh đơn. Câu lệnh đơn sẽ cập nhật nhiều bản ghi trong dữ liệu. Đaya là hành vi phổ biến trong trigger DELETE và UPDATE, và thường các câu lệnh sẽ ảnh hưởng tới nhiều bản ghi. Hành vi cho trigger INSERT sẽ ít phổ biến hơn vì câu lệnh insert thông thường chỉ thêm vào 1 bản ghi.

Khi một tính năng của DML trigger gọi tự động và tính toán lại tóm tắt các giá trị của bảng và lưu trữ kết quả vào bảng khác, cân nhắc multirow là quan trọng.

Ví dụ xét tình huống lưu trữ một kết quả tổng của single row insert:

USE AdventureWorks2019;
GO
CREATE TRIGGER PODetails
ON Purchasing.PurchaseOrderDetail AFTER INSERT AS
UPDATE PurchaseOrderHeader
SET SubTotal = Subtotal + LineTotal FROM INSERTED
WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID;

Trong đoạn code dưới, subtotal được được tính toán và lưu trữ cho mỗi hoạt động lệnh insert single row.
Đoạn code dứ sẽ chạy cho cả tình huống multirow hay single row:

USE AdventureWorks2019;
GO
CREATE TRIGGER PODetailMultiple
ON Purchasing.PurchaseOrderDetail AFTER INSERT AS
UPDATE Purchasing.PurchaseOrderHeader SET Subtotal = SubTotal + (SELECT SUM(LineTotal) FROM inserted
WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID)
WHERE PurchaseOrderHeader.PurchaseOrderID IN (SELECT PurchaseOrderID FROM inserted);

LOGON Triggers

Những trigger này kích hoạt khi có phản hồi từ sự kiện LOGON trong SQL Server. Logon triggers kích hoạt stored procedure trong sự kiện LOGON. Sự kiện này bắt gặp khi phiên người dùng được thành lập với instance của SQL Server. Logon trigger xảy ra sau khi quá trình xác thực đăng nhập kết thúc, nhưng trước đó phiên người dùng thực sự đã được thành lập. Loggon trigger không chạy khi xác thực thất bại.

Loggon trigger được tạo trên tầng server và hữu dụng trong các trường hợp:

  • Kiểm tra các hoạt động đăng nhập
  • Điều khiển các hành động đăng nhập

Ví dụ tạo bảng LoginActivity cùng các cột LOGONEvent và Logintime được tạo trong adventureworks như sau:

CREATE TABLE LoginActivity (LOGONEvent XML, Logintime datetime)

Tạo trigger để khi ngời dùng login sẽ insert lịch sử login vào bảng LoginActivity:

CREATE TRIGGER [track_login] ON ALL SERVER
FOR LOGON AS
BEGIN
INSERT INTO LoginActivity
SELECT EVENTDATA(),
GETDATE()
END;

Lưu ý: thật thận trọng khi sử dụng LOGON Trigger, có thể gây ra tình huống lỗi logic TRIGGER không đăng nhập được phải mở DAC để kết nối, nên test thật kĩ trước khi thực thi.

Hiệu suất trigger

Trong thực tế, trigger không mang theo chi phí, thay vào đó hiệu năng chúng khá ổn. Tuy nhiên, nhiều vấn đề về hiệu suất có thể xảy ra do logic hiện diện bên trong trình kích hoạt. Giả sử một trình kích hoạt tạo một con trỏ và lặp qua nhiều hàng chúng sẽ có sự chậm lại trong quá trình thực thi.

Tương tự, xét tình huống trigger thực thi nhiều câu lệnh SQL ngăn chặn ở bảng các bảng riêng biệt trong bảng inserted và deleted . Nó sẽ trả kết quả thêm một lần nữa và làm chậm câu truy vấn hơn việc không sử dụng trigger.

Vì vậy, tốt nhất nên giữ cho logic trigger đơn giản và tránh sử dụng con trỏ khi thực thi các câu lệnh trong các bảng và các tác vụ khác nhau làm giảm hiệu năng hệ thống.

Bài tập

Bài 1:

Galaxy Airline là một dịch vụ hoạt động trong ngành hàng không và quản lý các chuyến bay thị trường Châu Âu. công ty duy trì dữ liệu giao dịch chuyến bay mỗi ngày trên SQL server. Để hiệu hoạt động hiệu quả, họ quyết định sử dụng triggers trong ứng dụng database. Chi tiết xử lý như sau:

1. Tạo database có tên GalaxyAirlines.

Tạo bảng Flight:

Field name Data type Key Field Description
Aircraft_code varchar(10) Primary key Mã má bay
Type varchar(6)   Mô tả kiểu má bay
Source varchar(20)   Lưu trữ tên thành phố khởi hành
Destination varchar(20)   Lưu trữ tên thành phố xác định điểm đến
Dep_time datetime   Thời gian khỏi hành
Journey_hrs int  

Lưu trữ số giờ bay cho hành trình

Tạo bảng Flight_Details:

Field name Data type Key Field Description
Class_code varchar(10) Primary key Lưu trữ hạng vé, giá trị cót hể là first,business,economy
Aircraft_code varchar(10) Foreign key Mã má bay
Fare money   Giá vé
Seats     Số lượng ghế trên chuyến bay

2. Viết câu lệnh tạo trigger tên trgCheckSeats sẽ được kích hoạt khi một bản ghi được thêm vào bảng Flight_Details. Giới hạn tối đa của ghế trong chuyến bay là 150. Trigger sẽ kiểm tra giá trị ghế khi dữ liệu insert, nếu lớn hơn 150, không cho insert và thông báo lỗi "Seats number can not higher than 150"

3. Thêm ít nhất 5 bản ghi mỗi bảng

4. Viết câu lệnh tạo trigger UpdateValude sẽ kích hoạt khi một bản ghi được cập nhật trong bảng Flight_Details. Trigger nhận diện được nếu cột Seats đưojc cập nhật, nếu có hành vi UPDATE sẽ không được thực thi vì cột Seats được định nghĩa như một hằng số và không thể thay đổi.

5.  Viết câu lệnh tạo DDL trigger ProhibitDelete sẽ kích hoạt khi người dùng cố gắng xóa bảng trong database GalaxyAirline. Trigger không cho phép người dùng xóa và hiển thị thông báo 'You are not allowed to delete tables in this database'.

Bài 2:

Dựa vào bài tập số 1 của link: https://web888.vn/cau-lenh-select-nang-cao-ket-hop-cung-cac-ham-tong-hop-du-lieu/#content_baitap

Làm các yêu cầu sau:

Tạo trigger có tên [TG_NhaTrenPho_Update] để kiểm tra giá trị cột [SoNhanKhau]

  • Nếu giá trị nhỏ hơn 0, rollback lại và không thực hiện cập nhâật.
  • Nếu giá trị lớn hơn 0, cho phép cập nhật.
Trigger Name For Event Table Fired Condition Error Message
TG_NhaTrenPho_Update UPDATE NhaTrenPho SoNhanKhau < 0 SoNhanKhau phai lon hon 0

Tạo trigger có tên [TG_DuongPho_Instead_Of_Delete] cho bảng [DuongPho]

Trigger Name Event Table Error Message
TG_DuongPho_Instead_Of_Delete INSTEAD OF DELETE DuongPho Khong xoa duoc duong pho

Bài 3:

Dựa vào database studentManagement thực hiện các yêu cầu sau:

Tạo trigger tên TG_Result_Insert sẽ thực thi khi người dùng thêm bản ghi mới vào bảng Result. Trigger kiểm tra nếu Mark < 0 sẽ hiển thị lỗi 'Cannot insert mark <0', nếu không thì cho phép thực thi.

Trigger Name Event Table Data Anomalies Error Message
TG_Result_Insert INSERT Result Mark < 0 Can not insert mark less than 0

Test trigger với dữ liệu (StudentId,SubjectId,Mark) = (1,3,-2)

Tạo trigger cho sự kiện UPDATE với tên TG_Subject_Update, khi xảy ra sự kiện update trong cột SubjectName của bảng Subject, không cho update cột vàn ra thôg báo "Can not update SubjectName"

Trigger_Name Event Table Data Anomalies Error Message
TG_Subject_Update UPDATE Subject column SubjectName update Can not update SubjectName

Link bài studentManagement: https://web888.vn/constraint-rang-buoc-table-strong-sql-server/#content_baitap

Bài 4 (ôn luyện):

1. Tạo một file có tên dạng:  lab7.sql.

2. Tạo một Cơ sở dữ liệu tên trùng với tên file, ví dụ: Lab7.

3. Tạo ba bảng và chèn dữ liệu như sau:

Customer (lưu trữ Khách hàng)

CustomerID

(int)

Name

(varchar (30))

Birth

(date)

Gender

(bit)

1

Jonny Owen

10/10/1980

1

2

Christina Tiny

10/03/1989

0

3

Garry Kelley

16/03/1990

Null

4

Tammy Beckham

17/05/1980

0

5

David Phantom

30/12/1987

1

 Product (Lưu trữ Sản phẩm)

ProductID

(int)

Name

(varchar (30))

Pdesc

(text)

Pimage

(varchar(200))

PStatus

(bit)

1

Nokia N90

Mobile Nokia

image1.jpg

1

2

HP DV6000

Laptop

image2.jpg

NULL

3

HP DV2000

Laptop

image3.jpg

1

4

SamSung G488

Mobile SamSung

image4.jpg

0

5

LCD Plasma

TV LCD

image5.jpg

0

Comment (lưu trữ bình luận của Khách đối với Sản phẩm). Ví dụ: bản ghi đầu tiên của bảng dưới thể hiện rằng ‘Jonny Owen’ (mã là 1 ở bảng Customer) đã bình luận cho sản phẩm ‘Nokia N90’ (mã là 1 ở bảng Product)   vào ngày ‘15/03/09’).

ComID(int

identity(1,1))

ProductID

(int)

CustomerID

(int)

Date

(datetime)

Title

(varchar(200)

Content

(text)

Status

(bit)

1

1

1

15/03/09

Hot product

null

1

2

2

2

14/03/09

Hot price

Very much

0

3

3

2

20/03/09

Cheapest

Unlimited

0

4

4

2

16/04/09

Sale off

50%

1

Các ràng buộc phải tạo:

- Ràng buộc Default cho cột Date của bảng Comment với giá trị mặc định là ngày hiện tại.

- Ràng buộc khóa chính trên cột: CustomerID của bảng Customer, ProductID của bảng Product và ComID của bảng Comment.

- Ràng buộc khóa ngoại trên cột: ProductID của bảng Comment tham chiếu đến bảng Product và CustomerID cũng của bảng Comment tham chiếu đến bảng Customer.

- Ràng buộc Unique cho cột Pimage trên bảng Product.

4. Hiển thị những sản phẩm có PStatus là null hoặc 0.

5. Hiển thị những sản phẩm không có bình luận nào.

6. Hiển thị những Khách có nhiều bình luận nhất.

7. Tạo View có tên ‘vwFull_Information’ để xem tất cả các bình luận gồm các cột sau:

Mã bình luận, tên Khách, tên Sản phẩm, ngày bình luận, tiêu đề bình luận, nội dung bình luận và trạng thái bình luận, trong đó trạng thái bình luận hiển thị là ‘Accept’ thay cho 1 và ‘Not Accept’ thay cho 0.

8. Tạo View có tên ‘vwCustomerList’ để liệt kê thông tin của tất cả các Khách hàng gồm tất cả các cột của bảng Customer và cột Status, trong đó cột Gender hiển thị là ‘Male’ thay cho 1, ‘Female’ thay cho 0 và ‘Unknow’ thay cho Null, cột Status hiển thị là ‘Old’ nếu tuổi của khách>=30 và ‘Young’ nếu tuổi của khách<30.

9. Sửa View ‘vwCustomerList’ để nó chỉ chứa các cột CustomerID, Customer Name, Birth, Gender của bảng Customer và tạo chỉ mục (index) có tên ixCustomerName trên cột [Customer Name] của view này.

10. Tạo thủ tục lưu trữ có tên ‘spStudent’ có một tham số tên @Name.

- Nếu tìm thấy @Name trong cột Name của bảng Product thì sẽ liệt kê tất cả những bình luận cho những Sản phẩm có tên tương tự (like) @Name.

- Nếu không thì kiểm tra @Name nếu tìm thấy trong Name của bảng Customer thì sẽ liệt kệ tất cả những bình luận của những Khách có tên tương tự (like) @Name

- Còn nếu @Name nhận giá trị ‘*’ thì sẽ liệt kê tất cả các bình luận đang có.

11. Tạo Trigger có tên ‘tgUpdateProduct’ trên bảng Product để khi cập nhật giá trị trên cột ProductID của bảng Product thì trigger sẽ tự cập nhật giá trị tương ứng lên trên cột ProductID của bảng Comment.

12. Tạo thủ tục lưu trữ có tên ‘spDropOut’ có một  tham số là tên của Khách hàng, nếu tìm thấy tên này trong cột Name của bảng Customer thì sẽ xóa tất cả những thông tin của tất cả những Khách hàng có tên tương ứng đó trên tất cả các bảng liên quan của Cơ sở dữ liệu.

Bài viết liên quan:

PolyBase, Query Store, và Stretch Database trong SQL Server
Dữ liệu JSON trong SQL Server
Các tính năng nâng cao SQL trong SQL Server 2019
Giới thiệu Azure SQL
Xử lý lỗi và TRY CATCH trong SQL Server
Transaction trong SQL Server
FUNCTION (hàm) trong SQL Server
Lập trình và control of flow trong Transact SQL
Index trong SQL Server
Truy vấn metadata và Dynamyic Management Object trong SQL Server
Stored Procedure trong SQL Server
VIEW trong SQL Server

THÊM BÌNH LUẬN Cancel reply

Dịch vụ thiết kế Wesbite

NỘI DUNG MỚI CẬP NHẬT

FUNCTION (hàm) trong SQL Server

Vòng lặp và kiểm soát luồng chương trình trong PHP

Biểu thức chính quy (Regular Expression) trong PHP

Biến (Variable) trong PHP

Các kiểu dữ liệu trong PHP

Giới thiệu

web888.vn là chuyên trang chia sẻ và cập nhật tin tức công nghệ, chia sẻ kiến thức, kỹ năng. Chúng tôi rất cảm ơn và mong muốn nhận được nhiều phản hồi để có thể phục vụ quý bạn đọc tốt hơn !

Liên hệ quảng cáo: [email protected]

Kết nối với web888

© web888.vn - Tech888 Co .Ltd since 2019

Đăng nhập

Trở thành một phần của cộng đồng của chúng tôi!
Registration complete. Please check your email.
Đăng kýBạn quên mật khẩu?

Create an account

Welcome! Register for an account
The user name or email address is not correct.

Your personal data will be used to support your experience throughout this website, to manage access to your account, and for other purposes described in our privacy policy.

Registration confirmation will be emailed to you.
Log in Lost your password?

Reset password

Recover your password
Password reset email has been sent.
The email could not be sent. Possible reason: your host may have disabled the mail function.
A password will be e-mailed to you.
Log in Register
×