27,579
社区成员
发帖
与我相关
我的任务
分享
--导入表如下结构
DROP TABLE
IF EXISTS dbo.TCT
CREATE TABLE TCT (
ID int IDENTITY(1,1) PRIMARY KEY,
tname nvarchar (16),
cname nvarchar (32)
)
--记录一旦插入 TCT 表的时候,需要将教师姓名tname插入 teacher 表,课程名称cname插入 course 表,对应的关系插入 TC表
--示例数据如下:
insert into dbo.TCT select N'叶平',N'语文' union all
select N'贺高',N'数学' union all
select N'杨艳',N'英语' union all
select N'周磊',N'物理'
DROP TABLE
IF EXISTS dbo.teacher
CREATE TABLE teacher (t# INT, tname nvarchar(16)) --教师姓名表
DROP TABLE
IF EXISTS dbo.course
CREATE TABLE course (c# INT, cname nvarchar(32),) --课程表
DROP TABLE
IF EXISTS dbo.TC
CREATE TABLE TC (t# INT, c# INT,) --教师课程表
CREATE TABLE TCT (
ID int IDENTITY(1,1) PRIMARY KEY,
tname nvarchar (16),
cname nvarchar (32)
)
CREATE TABLE teacher (t# INT IDENTITY, tname nvarchar(16)) --教师姓名表
CREATE TABLE course (c# INT IDENTITY, cname nvarchar(32),) --课程表
CREATE TABLE TC (t# INT, c# INT,) --教师课程表
CREATE TRIGGER dbo.TCT_tri ON [dbo].TCT
FOR INSERT
AS
BEGIN
INSERT INTO dbo.teacher
( tname
)
SELECT DISTINCT tname
FROM inserted
WHERE tname NOT IN (SELECT tname FROM dbo.teacher)
INSERT INTO dbo.course
( cname
)
SELECT DISTINCT cname
FROM inserted
WHERE cname NOT IN (SELECT cname FROM dbo.course)
INSERT INTO dbo.TC
( t# ,
c#
)
SELECT dbo.teacher.t# ,
dbo.course.c#
FROM teacher
JOIN inserted ON teacher.tname = inserted.tname
JOIN dbo.course ON course.cname = inserted.cname
END
GO
insert into dbo.TCT
SELECT N'叶平',N'语文' union ALL
SELECT N'叶平',N'数学' union ALL
SELECT N'叶平',N'地理' union all
select N'贺高',N'数学' union all
select N'杨艳',N'英语' union all
select N'周磊',N'物理'
SELECT * FROM TCT
SELECT * FROM dbo.teacher
SELECT * FROM dbo.course
SELECT * FROM dbo.TC
insert into dbo.Teacher select 1,N'叶平' union all
select 2,N'贺高' union all
select 3,N'杨艳' union all
select 4,N'周磊'
insert into Course select 1,N'语文' union all
select 2,N'数学' union all
select 3,N'英语' union all
select 4,N'物理'
INSERT into dbo.TCT values(1,1 ),(2,2),(3,3),(4,4)
--CREATE TABLE TCT (
-- ID int IDENTITY(1,1) PRIMARY KEY,
-- tname nvarchar (16),
-- cname nvarchar (32)
--)
--CREATE TABLE teacher (t# INT IDENTITY, tname nvarchar(16)) --教师姓名表
--CREATE TABLE course (c# INT IDENTITY, cname nvarchar(32),) --课程表
CREATE TRIGGER dbo.TCT_tri ON [dbo].TCT
FOR INSERT
AS
BEGIN
INSERT INTO dbo.teacher
( tname
)
SELECT tname
FROM inserted
INSERT INTO dbo.course
( cname
)
SELECT cname
FROM inserted
INSERT INTO dbo.TC
( t# ,
c#
)
SELECT dbo.teacher.t# ,
dbo.course.c#
FROM teacher
JOIN inserted ON teacher.tname = inserted.tname
JOIN dbo.course ON course.cname = inserted.cname
END
GO
insert into dbo.TCT select N'叶平',N'语文' union all
select N'贺高',N'数学' union all
select N'杨艳',N'英语' union all
select N'周磊',N'物理'
SELECT * FROM TCT
SELECT * FROM dbo.teacher
SELECT * FROM dbo.course
SELECT * FROM dbo.TC
ALTER TRIGGER dbo.TCT_tri ON [dbo].TCT
FOR INSERT
AS
BEGIN
INSERT INTO dbo.teacher
( tname
)
SELECT DISTINCT tname
FROM inserted
WHERE tname NOT IN (SELECT tname FROM dbo.teacher)
INSERT INTO dbo.course
( cname
)
SELECT DISTINCT cname
FROM inserted
WHERE cname NOT IN (SELECT cname FROM dbo.course)
INSERT INTO dbo.TC
( t# ,
c#
)
SELECT dbo.teacher.t# ,
dbo.course.c#
FROM teacher
JOIN inserted ON teacher.tname = inserted.tname
JOIN dbo.course ON course.cname = inserted.cname
WHERE NOT EXISTS(SELECT 1 FROM TC WHERE t#=dbo.teacher.t# AND c#=dbo.course.c#)
END
GO