常用SQL语句收藏 <三>

视图的创建删除与使用

创建:
create view view_1 as SELECT     dbo.class.class, dbo.class.studentnumber, dbo.class.teacher, dbo.student.id, dbo.student.name, dbo.student.age, dbo.student.english, 
                      dbo.student.born, dbo.student.class AS Expr1 
FROM         dbo.class INNER JOIN
                      dbo.student ON dbo.student.class = dbo.class.class
使用: select * from view_1
删除:drop view view_1

触发器创建

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE  TRIGGER [trigger_1] 
   ON  [dbo].[class]
   AFTER DELETE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    delete from dbo.student where dbo.student.class not in (select class from dbo.class)
    -- Insert statements for trigger here
END

此触发器的功能是:当class表有删除操作时,同步student表中的记录,将不存在的班级的记录全部删除.

使用触发器：

—altertable student drop CONSTRAINT  FK_CLASS
—select* From student where class='chuyi'
—select* From class where class='chuyi'
—deletefrom class where class='chuyi'
—select* From student where class='chuyi'
—select* From class where class='chuyi‘
—可以看到student表中的记录也被更新了.

修改触发器

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER  [trigger_1] 
   ON  [dbo].[class]
   AFTER DELETE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    delete from dbo.student where dbo.student.class not in (select class from dbo.class)
    -- Insert statements for trigger here

END

删除触发器:Drop trigger trigger_1