Database Reference
In-Depth Information
begin
raiserror('Cannot change the closed order',16,1)
rollback tran
return
end
end
go
The table has primary and foreign keys as well as default and check constraints. INSTEAD OF and AFTER
triggers are also defined. Let's take a look what happens when we run an insert statement against the table, as shown
in Listing 8-2.
Listing 8-2.
Inserting data to the table: Insert statement
insert into dbo.OrderLineItems(OrderId, ProductId, ProductName, Quantity, Price)
values(@OrderId, @ProductId, @ProductName, @Quantity, @Price)
In the first step, SQL Server creates and populates
inserted
and
deleted
virtual tables that contain the
information about the new and old versions of the rows affected by the DML statement. Those tables will be
accessible in the INSTEAD OF trigger. In our case, the
inserted
table would have one row with the values that we
provided in the insert statement and the
deleted
table would be empty because there is no “old” version of the row
when we insert it. We will talk about those tables later in this chapter, but for now let us remember one very important
thing:
DML triggers have the statement scope and would be fired just once regardless of how many rows are affected
.
The virtual tables could have more than one row, and the implementation needs to handle that correctly.
In the next step, SQL Server fires the
trg_OrderLineItems_InsteadOfInsert
INSTEAD OF trigger. In the
trigger, we are implementing the business logic and executing an insert statement against the actual table. Our
implementation of the trigger ignores the
ProductName
value provided by original insert statement and replaces it
with the actual product name from
Products
table. Inner join also filters out the rows that do not have corresponding
products in the system. Even if we enforce the same rule by foreign key constraint, such an implementation behaves
differently. Violation of the foreign key constraint terminates the entire batch without inserting any rows, while a join
in the trigger just filters out incorrect rows and inserts the correct ones.
■
Whether you should use such an approach, ignoring the incorrect rows rather than terminating the batch,
depends on the business requirements. although it can help in some cases, it complicates system troubleshooting. at a
bare minimum, i suggest that you log information about the skipped rows somewhere in the system.
Tip
As a next step, when the INSTEAD OF trigger runs the insert statement, SQL Server performs the following tasks
in the sequence:
It assigns the default constraint value to
CreationDate
column.
1.
2.
It validates the not null, primary key, unique, check constraints, and unique indexes in the
table, and it terminates the statement in the case of constraint or uniqueness violations.
3.
It checks foreign key constraints, and terminates the statement in case of violations.
Otherwise, it inserts the new rows into the table.
