--客户
INSERT INTO ZX_client VALUES
('C001','孙虎','男',21,'新','152322199510213522','18347256701'), ('C002','小美','女',21,'新','152322199510223522','18347256702'), ('C003','李玉','女',21,'新','152322199510233522','18347256703'), ('C004','李强','男',21,'老','152322199510243522','18347256704'), ('C005','张建','男',21,'新','152322199510253522','18347256705'), ('C006','黎勇','男',21,'新','152322199510263522','18347256706'), ('C007','钱悠','男',21,'老','152322199510273522','18347256707'), ('C008','黄玲','女',21,'新','152322199510283522','18347256708'), ('C009','张伟','男',21,'老','152322199510293522','18347256709'), ('C010','黄树','男',21,'新','152322199510303522','18347256710');
select * from ZX_client
图2.4.5 客户信息表
--票务
INSERT INTO ZX_ticket VALUES
('H0001',123,'k1509','火车票','成都起点','成都终点','2015-12-28 07:00:00','2015-12-28 08:00:00'),
('H0002',122,'k1508','火车票','包头起点','包头终点','2015-12-28 07:00:00','2015-12-28 08:00:00'),
('H0003',109,'k1507','火车票','天津起点','天津终点','2015-12-28 07:00:00','2015-12-28 08:00:00'),
21
('H0004',121,'k1503','火车票','大连起点','大连终点','2015-12-28 08:00:00'),
('H0005',155,'k1511','火车票','杭州起点','杭州终点','2015-12-28 08:00:00'),
('H0006',101,'k1534','火车票','北京起点','北京终点','2015-12-28 08:00:00'),
('H0007',102,'k1512','火车票','湖南起点','湖南终点','2015-12-28 08:00:00'),
('H0008',111,'k1566','火车票','青岛起点','青岛终点','2015-12-28 08:00:00'),
('H0009',134,'k1111','火车票','银川起点','银川终点','2015-12-28 08:00:00'),
('H0010',112,'k1245','火车票','太原起点','太原终点','2015-12-28 08:00:00');
select * from ZX_ticket
07:00:00','2015-12-28 07:00:00','2015-12-28 07:00:00','2015-12-28 07:00:00','2015-12-28 07:00:00','2015-12-28 07:00:00','2015-12-28 07:00:00','2015-12-28
图2.4.6 票务信息表
--决定 DECIDE
INSERT INTO ZX_decide VALUES
('G001','T001'), ('G002','T002'), ('G003','T003'), ('G004','T004'), ('G005','T005'), ('G006','T006'), ('G007','T007'), ('G008','T008'), ('G009','T009'), ('G010','T010');
select * from ZX_decide
22
--陪同 accompany
INSERT INTO ZX_accompany Values
('G001','D001','20151228'), ('G002','D002','20151228'), ('G003','D003','20151228'), ('G004','D004','20151228'), ('G005','D005','20151228'), ('G006','Q001','20151228'), ('G007','Q002','20151228'), ('G008','Q003','20151228'), ('G009','Q004','20151228'), ('G010','Q005','20151228');
select * from ZX_accompany
--安排 arrange
INSERT INTO ZX_arrange VALUES
('G001','J001','20151228'), ('G002','J002','20151228'), ('G003','J003','20151228'), ('G004','J004','20151228'), ('G005','J005','20151228'), ('G006','J006','20151228'), ('G007','J007','20151228'), ('G008','J008','20151228'), ('G009','J009','20151228'), ('G010','J010','20151228');
select * from ZX_arrange
--选择 option
INSERT INTO ZX_option VALUES
('G001','H0001'), ('G002','H0002'), ('G003','H0003'), ('G004','H0004'), ('G005','H0005'), ('G006','H0006'), ('G007','H0007'),
23
('G008','H0008'), ('G009','H0009'), ('G010','H0010');
select * from ZX_option
--报名 apply
INSERT INTO ZX_apply VALUES
('G001','C001','20151226',200), ('G002','C002','20151226',440), ('G003','C003','20151226',370), ('G004','C004','20151226',660), ('G005','C005','20151226',860), ('G006','C006','20151226',290), ('G007','C007','20151226',480), ('G008','C008','20151226',550), ('G009','C009','20151226',500), ('G010','C010','20151226',700);
2.5 建立触发器
2.5.1 不允许向ZX_zx旅行社表中插入数据
代码：
CREATE TRIGGER trigger_1 on ZX_zx
INSTEAD OF INSERT AS BEGIN
SELECT '不允许向旅行社表中插入数据' AS '失败原因' END GO
25.2 建立触发器,不允许开始时间大于结束时间
代码：
--drop trigger trigger_2 create trigger trigger_2 on ZX_group
24
after insert as begin
select G_STIME ,G_ETIME from inserted
if 'G_STIME'>='G_ETIME' begin
select '时间输入错误,请检查' as '失败原因' rollback transaction end end go
2.5.3 建立staff 员工触发器,不允许员工年龄大于50
代码
--drop trigger trigger_3 create trigger trigger_3 on ZX_staff after insert as begin
select S_AGE from inserted
if 'S_AGE' not between '18' and '50' begin
select '年龄输入错误,请检查' as '失败原因' rollback transaction end end go
2.5.4 在ZX_decide 决定表上建立触发器,确保数据的正确关联
代码：
--drop trigger trigger_4 create trigger trigger_4 on ZX_decide after insert
25