USE AdventureWorks2008R2; GO DECLARE vend_cursor CURSOR FOR SELECT BusinessEntityID, Name, CreditRating FROM Purchasing.Vendor OPEN vend_cursor FETCH NEXT FROM vend_cursor; 这是联机丛书的例子,你看着改吧,爱锅那个的CURSOR写错了。不知道你会不会直接复制粘贴
那个我加了for以后那是报错呢
感觉和奇怪呢~~以前敲代码的时候还会有智能提示的~~哎哎真心不懂~~%>_<%纠结好久了
抱歉~~~第一次在这儿发帖~~create trigger insert_course on Course For Insert asdeclare @Cno int --@Cno用于接收插入的课程号 declare @Cflag bit --@flag用于接收课程的是否选修 declare @Sno int --@Sno用于接收学生学号select @Cno=Cno from inserted select @Cflag=Cflag from inserteddeclare my_cursor cursor for select Sno from Student --声明游标 open my_cursor --打开游标 fetch next from my_cursor into @Sno --通过游标逐个读取学生学号赋给变量if(@Cflag=1) begin while(@@fetch_status=0) begin insert into Greade values(@Sno,@cno) fetch next from my_cursor into @Sno end endclose my_cursor --关闭游标 deallocate my_cursor --释放资源 go
应该不会,因为你那个只是insert,最好把你的表结构语句贴出来,你贴图给我没用》。
大虾~~我用powerdesigner直接生成脚本的好长。要全部贴出来吗。。
右键你的表,【编写表语句】→create
USE [GreadeSys] GO/****** Object: Table [dbo].[Greade] Script Date: 11/28/2012 15:05:27 ******/ SET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOCREATE TABLE [dbo].[Greade]( [Sno] [int] NOT NULL, [Cno] [int] NOT NULL, [Greade] [float] NULL, CONSTRAINT [PK_GREADE] PRIMARY KEY CLUSTERED ( [Sno] ASC, [Cno] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]GOALTER TABLE [dbo].[Greade] WITH CHECK ADD CONSTRAINT [FK_GREADE_GREADE_STUDENT] FOREIGN KEY([Sno]) REFERENCES [dbo].[Student] ([Sno]) GOALTER TABLE [dbo].[Greade] CHECK CONSTRAINT [FK_GREADE_GREADE_STUDENT] GOALTER TABLE [dbo].[Greade] WITH CHECK ADD CONSTRAINT [FK_GREADE_GREADE2_COURSE] FOREIGN KEY([Cno]) REFERENCES [dbo].[Course] ([Cno]) GOALTER TABLE [dbo].[Greade] CHECK CONSTRAINT [FK_GREADE_GREADE2_COURSE] GO USE [GreadeSys] GO/****** Object: Table [dbo].[SelectedCourse] Script Date: 11/28/2012 15:05:56 ******/ SET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOCREATE TABLE [dbo].[SelectedCourse]( [Tno] [int] NOT NULL, [Cno] [int] NOT NULL, CONSTRAINT [PK_SELECTEDCOURSE] PRIMARY KEY CLUSTERED ( [Tno] ASC, [Cno] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]GOALTER TABLE [dbo].[SelectedCourse] WITH CHECK ADD CONSTRAINT [FK_SELECTED_SELECTEDC_COURSE] FOREIGN KEY([Cno]) REFERENCES [dbo].[Course] ([Cno]) GOALTER TABLE [dbo].[SelectedCourse] CHECK CONSTRAINT [FK_SELECTED_SELECTEDC_COURSE] GOALTER TABLE [dbo].[SelectedCourse] WITH CHECK ADD CONSTRAINT [FK_SELECTED_SELECTEDC_TEACHER] FOREIGN KEY([Tno]) REFERENCES [dbo].[Teacher] ([Tno]) GOALTER TABLE [dbo].[SelectedCourse] CHECK CONSTRAINT [FK_SELECTED_SELECTEDC_TEACHER] GO USE [GreadeSys] GO/****** Object: Table [dbo].[Course] Script Date: 11/28/2012 15:04:48 ******/ SET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_PADDING ON GOCREATE TABLE [dbo].[Course]( [Cno] [int] IDENTITY(100,1) NOT NULL, [Cname] [varchar](20) NOT NULL, [Ccredit] [smallint] NULL, [Cflag] [bit] NULL, CONSTRAINT [PK_COURSE] PRIMARY KEY NONCLUSTERED ( [Cno] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]GOSET ANSI_PADDING OFF GOALTER TABLE [dbo].[Course] WITH CHECK ADD CONSTRAINT [CKC_CNO_COURSE] CHECK (([Cno]>=(100) AND [Cno]<=(1000))) GOALTER TABLE [dbo].[Course] CHECK CONSTRAINT [CKC_CNO_COURSE] GOALTER TABLE [dbo].[Course] ADD DEFAULT ((1)) FOR [Cflag] GO 感觉还是很长呢~~~这个这么?? 大虾你比我们老师厉害些呢~~~我上次问老师也不可以在数据库里面逆向生成脚本文件。。老师说不可以呢。。哎哎断网了。。拜托了。。%>_<%
触发器用这个试试,你还有student表没给我,不过算了。你自己给东西慢而已CREATE TRIGGER insert_course ON Course FOR INSERT AS DECLARE @Cno INT --@Cno用于接收插入的课程号 DECLARE @Cflag BIT --@flag用于接收课程的是否选修 DECLARE @Sno INT --@Sno用于接收学生学号
SELECT @Cno = Cno FROM inserted SELECT @Cflag = Cflag FROM inserted
DECLARE my_cursor CURSOR FOR SELECT Sno FROM Student --声明游标 OPEN my_cursor --打开游标 FETCH NEXT FROM my_cursor INTO @Sno --通过游标逐个读取学生学号赋给变量
IF ( @Cflag = 1 ) BEGIN WHILE ( @@fetch_status = 0 ) BEGIN INSERT INTO Greade ( sno, cno ) VALUES ( @Sno, @cno ) FETCH NEXT FROM my_cursor INTO @Sno END END
CLOSE my_cursor --关闭游标 DEALLOCATE my_cursor --释放资源 go
消息 102,级别 15,状态 1,过程 insert_course,第 6 行
'my_cursor' 附近有语法错误。
GO
DECLARE vend_cursor CURSOR
FOR SELECT BusinessEntityID, Name, CreditRating FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursor;
这是联机丛书的例子,你看着改吧,爱锅那个的CURSOR写错了。不知道你会不会直接复制粘贴
抱歉~~~第一次在这儿发帖~~create trigger insert_course
on Course
For Insert
asdeclare @Cno int --@Cno用于接收插入的课程号
declare @Cflag bit --@flag用于接收课程的是否选修
declare @Sno int --@Sno用于接收学生学号select @Cno=Cno from inserted
select @Cflag=Cflag from inserteddeclare my_cursor cursor for select Sno from Student --声明游标
open my_cursor --打开游标
fetch next from my_cursor into @Sno --通过游标逐个读取学生学号赋给变量if(@Cflag=1)
begin
while(@@fetch_status=0)
begin
insert into Greade values(@Sno,@cno)
fetch next from my_cursor into @Sno
end
endclose my_cursor --关闭游标
deallocate my_cursor --释放资源
go
大虾~~我用powerdesigner直接生成脚本的好长。要全部贴出来吗。。
GO/****** Object: Table [dbo].[Greade] Script Date: 11/28/2012 15:05:27 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[Greade](
[Sno] [int] NOT NULL,
[Cno] [int] NOT NULL,
[Greade] [float] NULL,
CONSTRAINT [PK_GREADE] PRIMARY KEY CLUSTERED
(
[Sno] ASC,
[Cno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GOALTER TABLE [dbo].[Greade] WITH CHECK ADD CONSTRAINT [FK_GREADE_GREADE_STUDENT] FOREIGN KEY([Sno])
REFERENCES [dbo].[Student] ([Sno])
GOALTER TABLE [dbo].[Greade] CHECK CONSTRAINT [FK_GREADE_GREADE_STUDENT]
GOALTER TABLE [dbo].[Greade] WITH CHECK ADD CONSTRAINT [FK_GREADE_GREADE2_COURSE] FOREIGN KEY([Cno])
REFERENCES [dbo].[Course] ([Cno])
GOALTER TABLE [dbo].[Greade] CHECK CONSTRAINT [FK_GREADE_GREADE2_COURSE]
GO
USE [GreadeSys]
GO/****** Object: Table [dbo].[SelectedCourse] Script Date: 11/28/2012 15:05:56 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[SelectedCourse](
[Tno] [int] NOT NULL,
[Cno] [int] NOT NULL,
CONSTRAINT [PK_SELECTEDCOURSE] PRIMARY KEY CLUSTERED
(
[Tno] ASC,
[Cno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GOALTER TABLE [dbo].[SelectedCourse] WITH CHECK ADD CONSTRAINT [FK_SELECTED_SELECTEDC_COURSE] FOREIGN KEY([Cno])
REFERENCES [dbo].[Course] ([Cno])
GOALTER TABLE [dbo].[SelectedCourse] CHECK CONSTRAINT [FK_SELECTED_SELECTEDC_COURSE]
GOALTER TABLE [dbo].[SelectedCourse] WITH CHECK ADD CONSTRAINT [FK_SELECTED_SELECTEDC_TEACHER] FOREIGN KEY([Tno])
REFERENCES [dbo].[Teacher] ([Tno])
GOALTER TABLE [dbo].[SelectedCourse] CHECK CONSTRAINT [FK_SELECTED_SELECTEDC_TEACHER]
GO
USE [GreadeSys]
GO/****** Object: Table [dbo].[Course] Script Date: 11/28/2012 15:04:48 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOSET ANSI_PADDING ON
GOCREATE TABLE [dbo].[Course](
[Cno] [int] IDENTITY(100,1) NOT NULL,
[Cname] [varchar](20) NOT NULL,
[Ccredit] [smallint] NULL,
[Cflag] [bit] NULL,
CONSTRAINT [PK_COURSE] PRIMARY KEY NONCLUSTERED
(
[Cno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GOSET ANSI_PADDING OFF
GOALTER TABLE [dbo].[Course] WITH CHECK ADD CONSTRAINT [CKC_CNO_COURSE] CHECK (([Cno]>=(100) AND [Cno]<=(1000)))
GOALTER TABLE [dbo].[Course] CHECK CONSTRAINT [CKC_CNO_COURSE]
GOALTER TABLE [dbo].[Course] ADD DEFAULT ((1)) FOR [Cflag]
GO
感觉还是很长呢~~~这个这么??
大虾你比我们老师厉害些呢~~~我上次问老师也不可以在数据库里面逆向生成脚本文件。。老师说不可以呢。。哎哎断网了。。拜托了。。%>_<%
FOR INSERT
AS
DECLARE @Cno INT --@Cno用于接收插入的课程号
DECLARE @Cflag BIT --@flag用于接收课程的是否选修
DECLARE @Sno INT --@Sno用于接收学生学号
SELECT @Cno = Cno
FROM inserted
SELECT @Cflag = Cflag
FROM inserted
DECLARE my_cursor CURSOR
FOR
SELECT Sno
FROM Student --声明游标
OPEN my_cursor --打开游标
FETCH NEXT FROM my_cursor INTO @Sno --通过游标逐个读取学生学号赋给变量
IF ( @Cflag = 1 )
BEGIN
WHILE ( @@fetch_status = 0 )
BEGIN
INSERT INTO Greade
( sno, cno )
VALUES ( @Sno, @cno )
FETCH NEXT FROM my_cursor INTO @Sno
END
END
CLOSE my_cursor --关闭游标
DEALLOCATE my_cursor --释放资源
go
你的可以。。肿么感觉一样的~~~~你就多加了 INSERT INTO Greade( sno, cno )VALUES ( @Sno, @cno )?成功了。。%>_<%可是我的字段是这么排列的~~~
不过还是谢谢大虾~~~