USE pubs
GO
CREATE VIEW contact_list
AS
SELECT ID = au_id, name = au_fname + ' ' + au_lname,
city, state, country = 'USA'
FROM authors
UNION ALL
SELECT stor_id, stor_name, city, state, 'USA'
FROM stores
UNION ALL
SELECT pub_id, pub_name, city, state, country
FROM publishers
GO
-- To insert a new contact into this list,
-- we'll use an instead-of INSERT trigger.
-- The inserted table in the trigger will have
-- values only for the columns included in the view,
-- so all other columns in all three tables
-- will have to have default values or allow NULLS.
-- The only column not meeting this requirement
-- is the contract column of the authors table,
-- which is a bit column.
-- Alter the column to give it a default value:
ALTER TABLE authors
ADD CONSTRAINT contract_dflt DEFAULT 0 FOR contract
GO
CREATE TRIGGER Insert_Contact
ON contact_list
INSTEAD OF INSERT
AS
IF @@ROWCOUNT = 0 RETURN
IF (SELECT COUNT(*) FROM inserted) > 1 BEGIN
PRINT 'Only one row at a time can be inserted'
RETURN
END
-- check for a hyphen in the fourth position in the ID
IF (SELECT substring(ID,4,1) FROM inserted) = '-'
-- Inserting an author
INSERT into authors(au_id, au_fname, au_lname, city, state)
SELECT id, rtrim(substring(name, 1, charindex(' ',name) - 1)),
rtrim(substring(name, charindex(' ',name) + 1,
datalength(name) - charindex(' ',name))), city, state
FROM inserted
ELSE
-- Check for two nines at the beginning of the ID
IF (SELECT ID FROM inserted) like '99[0-9][0-9]'
-- Inserting a publisher
INSERT INTO publishers (pub_id, pub_name, city, state, country)
SELECT * FROM inserted
ELSE
-- Inserting a store
INSERT INTO stores(stor_id, stor_name, city, state)
SELECT id, name, city, state from inserted
RETURN