Database Reference
In-Depth Information
Quantity decimal(9,3) not null,
Price smallmoney not null,
constraint PK_OrderLineItems
primary key clustered(OrderId, OrderLineItemId),
constraint CHK_OrderLineItems_PositiveQuantity
check (Quantity > 0),
constraint FK_OrderLineItems_Orders
foreign key(OrderId)
references dbo.Orders(OrderId),
constraint FK_OrderLineItems_Products
foreign key(ProductId)
references dbo.Products(ProductId)
)
go
create trigger trg_OrderLineItems_InsteadOfInsert on dbo.OrderLineItems
instead of insert
as
begin
if @@rowcount = 0
return
set nocount on
if not exists(select * from inserted)
return
insert into dbo.OrderLineItems(OrderId, ProductId, ProductName, Quantity, Price)
select i.OrderId, i.ProductId, p.ProductName, i.Quantity, i.Price
from inserted i join dbo.Products p on
i.ProductId = p.ProductId
end
go
create trigger trg_OrderLineItems_AfterInsert on dbo.OrderLineItems
after insert
as
begin
if @@rowcount = 0
return
set nocount on
if not exists(select * from inserted)
return
if exists
(
select *
from inserted i join dbo.Orders o on
i.OrderId = o.OrderId
where o.Status = 'CLOSED'
)
Search WWH ::




Custom Search