|
3> CREATE TABLE authors(
4> au_id varchar(11),
5> au_lname varchar(40) NOT NULL,
6> au_fname varchar(20) NOT NULL,
7> phone char(12) NOT NULL DEFAULT ('UNKNOWN'),
8> address varchar(40) NULL,
9> city varchar(20) NULL,
10> state char(2) NULL,
11> zip char(5) NULL,
12> contract bit NOT NULL
13> )
14> GO
1> insert authors values('1', 'Joe', 'Abra', '111 111-1111', '6 St.', 'Berkeley', 'CA', '11111', 1)
2> insert authors values('2', 'Jack', 'Majo', '222 222-2222', '3 St.', 'Oakland' , 'CA', '22222', 1)
3> insert authors values('3', 'Pink', 'Cherry', '333 333-3333', '5 Ln.', 'Vancouver', 'BC', '33333', 1)
4> insert authors values('4', 'Blue', 'Albert', '444 444-4444', '7 Av.', 'Vancouver', 'BC', '44444', 1)
5> insert authors values('5', 'Red', 'Anne', '555 555-5555', '6 Av.', 'Regina', 'SK', '55555', 1)
6> insert authors values('6', 'Black', 'Michel', '666 666-6666', '3 Pl.', 'Regina', 'SK', '66666', 1)
7> insert authors values('7', 'White', 'Sylvia', '777 777-7777', '1 Pl.', 'Rockville', 'MD', '77777', 1)
8> insert authors values('8', 'Yellow','Heather','888 888-8888', '3 Pu', 'Vacaville', 'CA', '88888', 0)
9> insert authors values('9', 'Gold', 'Dep', '999 999-9999', '5 Av.', 'Oakland', 'CA', '99999', 0)
10> insert authors values('10', 'Siler', 'Dean', '000 000-0000', '4 Av.', 'Oakland', 'CA', '00000', 1)
11> GO
1>
2>
3> CREATE TABLE publishers(
4> pub_id char(4) NOT NULL,
5> pub_name varchar(40) NULL,
6> city varchar(20) NULL,
7> state char(2) NULL,
8> country varchar(30) NULL DEFAULT('USA')
9> )
10> GO
1>
2>
3> insert publishers values('1', 'Publisher A', 'Vancouver', 'MA', 'USA')
4> insert publishers values('2', 'Publisher B', 'Washington', 'DC', 'USA')
5> insert publishers values('3', 'Publisher C', 'Berkeley', 'CA', 'USA')
6> insert publishers values('4', 'Publisher D', 'New York', 'NY', 'USA')
7> insert publishers values('5', 'Publisher E', 'Chicago', 'IL', 'USA')
8> insert publishers values('6', 'Publisher F', 'Dallas', 'TX', 'USA')
9> insert publishers values('7', 'Publisher G', 'Vancouver', 'BC', 'Canada')
10> insert publishers values('8', 'Publisher H', 'Paris', NULL, 'France')
11> GO
1> CREATE TABLE stores(
2> stor_id char(4) NOT NULL,
3> stor_name varchar(40) NULL,
4> stor_address varchar(40) NULL,
5> city varchar(20) NULL,
6> state char(2) NULL,
7> zip char(5) NULL
8> )
9> GO
1> insert stores values('1','B','567 Ave.','Tustin', 'CA','92789')
2> insert stores values('2','N','577 St.', 'Los Gatos','CA','96745')
3> insert stores values('3','T','679 St.', 'Portland', 'OR','89076')
4> insert stores values('4','F','89 St.', 'Fremont', 'CA','90019')
5> GO
1> CREATE VIEW contact_list
2> AS
3> SELECT ID = au_id, name = au_fname + ' ' + au_lname,
4> city, state, country = 'USA'
5> FROM authors
6> UNION ALL
7> SELECT stor_id, stor_name, city, state, 'USA'
8> FROM stores
9> UNION ALL
10> SELECT pub_id, pub_name, city, state, country
11> FROM publishers
12> GO
1>
2> CREATE TRIGGER Insert_Contact
3> ON contact_list
4> INSTEAD OF INSERT
5> AS
6> IF @@ROWCOUNT = 0 RETURN
7> IF (SELECT COUNT(*) FROM inserted) > 1 BEGIN
8> PRINT 'Only one row at a time can be inserted'
9> RETURN
10> END
11>
12> IF (SELECT substring(ID,4,1) FROM inserted) = '-'
13>
14> INSERT into authors(au_id, au_fname, au_lname, city, state)
15> SELECT id, rtrim(substring(name, 1, charindex(' ',name) - 1)),
16> rtrim(substring(name, charindex(' ',name) + 1,
17> datalength(name) - charindex(' ',name))), city, state
18> FROM inserted
19> ELSE
20>
21> IF (SELECT ID FROM inserted) like '99[0-9][0-9]'
22>
23> INSERT INTO publishers (pub_id, pub_name, city, state, country)
24> SELECT * FROM inserted
25> ELSE
26>
27> INSERT INTO stores(stor_id, stor_name, city, state)
28> SELECT id, name, city, state from inserted
29> RETURN
30>
31> drop TRIGGER Insert_Contact ;
32> GO
1>
2>
3> drop VIEW contact_list ;
4> GO
1>
2> drop table authors;
3> drop table publishers;
4> drop table stores;
5> GO
|