menglanmiao
menglanmiao
2015-07-07 07:15
采纳率: 100%
浏览 2.3k
已采纳

在选课信息表中建立选课触发器 简单些 用if else 明天就要交了急急急!!!

建立学生信息表
create table student
(
Stu_No BIGINT(80) PRIMARY KEY,
Stu_Name VARCHAR(25) NOT NULL,
Stu_Sex VARCHAR(2) NOT NULL,
College VARCHAR(40) NOT NULL,
Profession VARCHAR(40) NOT NULL,
Class INT(10) NOT NULL
);

建立课程表
create table course
(
Year VARCHAR(25),

Term INT(10),
Cou_No BIGINT(80) PRIMARY KEY,
Cou_Name VARCHAR(80) NOT NULL,
Cou_Type VARCHAR(25),
Tea_No BIGINT(80) ,
Teachar VARCHAR(25) NOT NULL,
yixuan int(3) default 0
);

建立教师表
create table teacher
(
Tea_No BIGINT(80) PRIMARY KEY,
Tea_Name VARCHAR(25) NOT NULL,
College VARCHAR(40) NOT NULL
);

建立成绩表 sc
create table sc
(
Stu_No BIGINT(80),
Cou_No BIGINT(80),
Tea_No BIGINT(80),
Year VARCHAR(25),

Term INT(10),
Grade INT(10),
CONSTRAINT ab FOREIGN KEY(Stu_No) REFERENCES student(Stu_No),
CONSTRAINT ba FOREIGN KEY(Cou_No) REFERENCES course(Cou_No),
CONSTRAINT ac FOREIGN KEY(Tea_No) REFERENCES teacher(Tea_No)
);

建立选课信息表:
create table elective
(
Stu_No BIGINT(80) PRIMARY KEY,
Ele_No INT(10) NOT NULL,
Ele_Name VARCHAR(25) NOT NULL,
Ele_Type VARCHAR(25) NOT NULL,
Teachar VARCHAR(25) NOT NULL,
CONSTRAINT bc FOREIGN KEY(Stu_No) REFERENCES student(Stu_No)
);

插入数据到elective表:
insert into elective(Stu_No,Ele_No,Ele_Name,Ele_Type,Teachar)

values
(201415040401,01,'电影赏析','选修课','贺锡玉'),
(201415040402,01,'电影赏析','选修课','贺锡玉'),
(201415040403,01,'电影赏析','选修课','贺锡玉'),
(201415040404,01,'电影赏析','选修课','贺锡玉'),
(201415040405,01,'电影赏析','选修课','贺锡玉'),
(201415040406,01,'电影赏析','选修课','贺锡玉'),
(201415040407,01,'电影赏析','选修课','贺锡玉'),
(201415040408,01,'电影赏析','选修课','贺锡玉'),
(201415040409,01,'电影赏析','选修课','贺锡玉'),
(201415040410,01,'电影赏析','选修课','贺锡玉');

insert into elective(Stu_No,Ele_No,Ele_Name,Ele_Type,Teachar,xianxuan)

values
(201415040411,01,'电影赏析','选修课','贺锡玉',10);

**以上为学生管理数据库的所有表,
要求在上述的选课信息表中建立选课触发器(简单些),
要求:要有触发器,进行选课,有限选人数10人,怎么用触发器来实现显示
有多少人选,或还剩几个名额;当<=10时,可继续选,>10结束,怎么用触发器用if else实现
**
ps:若所建立的选课信息表等不行,请帮忙重新建立,然后实现触发器(麻烦简单些)

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • u012203715
    觉非遗风 2015-07-07 09:10
    已采纳

    需要再建立一张表格,存储已选人数和未选人数。
    CREATE TABLE [dbo].1 NOT NULL,
    [subject_name_var] varchar NULL,
    [subjet_num_int] [int] NULL,
    CONSTRAINT [PK_1] PRIMARY KEY CLUSTERED
    (
    [int] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'科目' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'1', @level2type=N'COLUMN',@level2name=N'subject_name_var'
    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'人数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'1', @level2type=N'COLUMN',@level2name=N'subjet_num_int'
    GO

    新增 触发器 选择Instead of
    检索表格 获取行数。赋值X
    IF x<'10'
    begin
    insert into 选课表
    update 人数表
    end

    else
    begin
    不动作,或根据需要
    end

    点赞 评论
  • u012216727
    大河行脚 2015-07-07 07:31

    你这是在压榨劳动人民,而且还是铁公鸡的那种

    点赞 2 评论

相关推荐