Введение в базы данных. Часть 8. Средства проектирования данных

CREATE TABLE Customer (
	CustomerID		char(5) NOT NULL,
	CompanyName		nvarchar(40) NOT NULL,
	ContactName		nvarchar(30) NULL,
	ContactTitle		nvarchar(30) NULL,
	Address			nvarchar(60) NULL,
	City			nvarchar(15) NULL,
	Region			nvarchar(15) NULL,
	PostalCode		nvarchar(10) NULL,
	Country			nvarchar(15) NULL,
	Phone			nvarchar(24) NULL,
	Fax			nvarchar(24) NULL
)

ALTER TABLE Customer
	ADD PRIMARY KEY (CustomerID)

CREATE TABLE Order (
	OrderID			int IDENTITY,
	CustomerID		char(5) NULL,
	EmployeeID		int NULL,
	OrderDate		datetime NULL,
	RequiredDate		datetime NULL,
	ShippedDate		datetime NULL,
	ShipVia			int NULL,
	Freight			money NULL,
	ShipName		nvarchar(40) NULL,
	ShipAddress		nvarchar(60) NULL,
	ShipCity		nvarchar(15) NULL,
	ShipRegion		nvarchar(15) NULL,
	ShipPostalCode		nvarchar(10) NULL,
	ShipCountry		nvarchar(15) NULL, 
	FOREIGN KEY (CustomerID)
								REFERENCES Customer
)

CREATE INDEX XIF1Order ON Order ( CustomerID)

ALTER TABLE Order   ADD PRIMARY KEY (OrderID)

  
create trigger tU_Customer on Customer for UPDATE as
begin
  declare @numrows int, @nullcnt int,
 @validcnt int, @insCustomerID char(5),
 @errno int, @errmsg varchar(255)
 
  select @numrows = @@rowcount
  if update(CustomerID)
  begin
if exists ( select * from deleted,Order where
Order.CustomerID = deleted.CustomerID )
begin
select @errno = 30005,
@errmsg = ‘Cannot UPDATE Customer because Order exists.’
goto error
end
  end
  return
error:
raiserror @errno @errmsg
rollback transaction
end