4> CREATE TABLE employee
5> (
6> emp_id varchar(20),
7> fname varchar(20) NOT NULL,
8> minit char(1) NULL,
9> lname varchar(30) NOT NULL,
10> job_id smallint NOT NULL DEFAULT 1,
11> job_lvl tinyint DEFAULT 10,
12> pub_id char(4) NOT NULL DEFAULT ('9952'),
13> hire_date datetime NOT NULL DEFAULT (getdate())
14> )
15> GO
1>
2> insert employee values ('1', 'Jack', 'T', 'Lee', 2, 215, '9952', '11/11/89')
3> insert employee values ('2', 'Jode', 'M', 'Devon', 3, 200, '9952', '07/16/91')
4> insert employee values ('3', 'Frac', 'F', 'Chang', 4, 227, '9952', '11/03/90')
5> insert employee values ('4', 'Like', 'A', 'Lebihan', 5, 175, '0736', '06/03/90')
6> insert employee values ('5', 'Paul', 'X', 'Henriot', 5, 159, '0877', '08/19/93')
7> insert employee values ('6', 'Sick', 'K', 'Ottlieb', 5, 150, '1389', '04/05/91')
8> insert employee values ('7', 'Rita', 'B', 'Muller', 5, 198, '1622', '10/09/93')
9> insert employee values ('8', 'Mary', 'J', 'Pontes', 5, 246, '1756', '03/01/89')
10> insert employee values ('9', 'Jane', 'Y', 'Labrune', 5, 172, '9901', '05/26/91')
11> insert employee values ('10','Carl', 'F', 'Hernadez',5, 211, '9999', '04/21/89')
12> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> CREATE TABLE jobs(
3> job_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED,
4> job_desc varchar(50) NOT NULL DEFAULT 'New Position - title not formalized yet',
5> min_lvl tinyint NOT NULL CHECK (min_lvl >= 10),
6> max_lvl tinyint NOT NULL CHECK (max_lvl <= 250)
7> )
8> GO
1>
2>
3> insert jobs values ('Coder', 10, 10)
4> insert jobs values ('Tester', 200, 250)
5> insert jobs values ('Programmer', 175, 225)
6> insert jobs values ('Painter', 175, 250)
7> insert jobs values ('Drawer', 150, 250)
8> insert jobs values ('Editor', 140, 225)
9> insert jobs values ('Manager', 120, 200)
10> insert jobs values ('Manager', 100, 175)
11> insert jobs values ('Representative', 25, 100)
12> insert jobs values ('Designer', 25, 100)
13>
14> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> CREATE TRIGGER employee_insupd
3> ON employee
4> FOR insert, UPDATE
5> AS
6>
7> declare @min_lvl tinyint, @max_lvl tinyint, @emp_lvl tinyint, @job_id smallint
8> select @min_lvl = min_lvl, @max_lvl = max_lvl, @emp_lvl = i.job_lvl, @job_id = i.job_id
9> from employee e, jobs j, inserted i
10> where e.emp_id = i.emp_id AND i.job_id = j.job_id
11> IF (@job_id = 1) and (@emp_lvl <> 10)
12> begin
13> raiserror ('Job id 1 expects the default level of 10.',16,1)
14> ROLLBACK TRANSACTION
15> end
16> ELSE
17> IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
18> begin
19> raiserror ('The level for job_id:%d should be between %d and %d.',
20> 16, 1, @job_id, @min_lvl, @max_lvl)
21> ROLLBACK TRANSACTION
22> end
23>
24> GO
1>
2> drop table employee;
3> drop table jobs;
4> GO
1>
2>