《SQL Server 二零一零从入门到精晓》–20180717

发布时间:2019-09-11  栏目:NoSQL  评论:0 Comments

目录

约束

主关键字约束(Primary Key Constraint)

用来钦命表中的一列或几列组合的值在表中具备独一性。建立主键的指标是让外键来引用。

1.触发器

触发器是一种特殊的仓库储存进度,与表紧凑关系。

Primary Key的创制格局

在创制表时开创Primary Key

CREATE TABLE table1(
    t_id VARCHAR(12) ,
    t_name VARCHAR(20),
    t_phone VARCHAR(20),
    CONSTRAINT t_idss PRIMARY KEY(t_id)
);

对t_id列创立主键,约束名称叫t_idss。

1.1.DDL触发器

当服务器或数据库中爆发多少定义语言(DDL)事件时将被调用。如CREATE,ALTERDROP等操作。假诺要实行以下操作,能够采取DDL触发器:

  • 严防对数据库架构举行改变
  • 可望数据库中产生一些处境以响应数据库架构中的更换
  • 要记录数据库架构中的改换或事件
删除Primary Key
ALTER TABLE table1
DROP CONSTRAINT t_idss;

自律名与列名不平等,此处填写约束名

1.2.DML触发器

当数据库服务器中产生多少操作语言(DML)事件时将被调用。如INSERT,DELETE,UPDATE等操作。将DML触发器和触发语句作为可在触发器内回滚的单个事务对待,假设检查评定到不当,则整个工作回滚。DML触发器在瞬间地点非常平价:

  • 可实现数据库相关表之间的级联退换
  • 能够堤防恶意或不当的DML讲话事件,并强制实行比CHECK自律越来越复杂的其余限制
  • 能够评估数据修改前后表的图景,并依据该出入选拔措施

三个表中的七个同类DML触发器,允许用多个例外的操作来响应同贰个改动语句
SQL Server
2008
为各种触发器创制了2个特其余表:INSERTED表和DELETED表。那是三个逻辑表,由系统来创建和保证,顾客无法对她们开展改变。它们贮存在内存中,实际不是在数据库中,並且组织与被DML触发器效用的表的构造同样。
INSERTED表中贮存了由进行INSERTUPDATE语句而插入的全部行,在推行INSERTUPDATE话语时,新的就要同不平日候被插入到触发器作用的表和INSERTED表中。INSERTED表中的行是触发器功能的表中央银行的别本。
DELETED表中存放了由实施DELETEUPDATE语句而除去的保有行,在施行DELETEUPDATE言语时,被删除的将要由触发器效率的表中被活动到DELETED表,八个表中不会有重复行。

向已有表中增添Primary Key
ALTER TABLE table1
ADD CONSTRAINT t_idss
PRIMARY KEY(t_id);

1.3.创立触发器

增进Primary Key的另一种示例
ALTER TABLE Products
ADD PRIMARY KEY(prod_id);

纵然上述代码运行没难题,查看表格设计也足以观望Primary
Key设置成功,可是在剔除Primary Key操作时会提醒:
消息3728,级别16,状态1,第1 行
‘prod_id’ 不是约束。
消息3727,级别16,状态0,第1 行
没能删除约束。请参阅前边的错误新闻。
案由是加多Primary
Key语句中并未用CONSTRAINT指明约束名,系统自动生成了主键名和平公约束名,要先查看主键名和平契约束名,删除时填写的也是约束名。
这种意况的科学删除方法

ALTER TABLE Products
DROP CONSTRAINT CK__Products__prod_p__1A14E395;
ALTER TABLE Products
DROP CONSTRAINT PK__Products__56958AB222AA2996;

1.3.1.创建DML触发器

多列组合加多主键约束
CREATE TABLE table1(
    t_id VARCHAR(12),
    s_id VARCHAR(20),
    score FLOAT,
    CONSTRAINT ts_id PRIMARY KEY(t_id,s_id)
);

1.3.1.1.INSERT触发器

示例1:创制叁个触发器Automatic_division,当在Student表中插入一条学生信息时,触发器遵照入学分数(stu_enter_score)对学生实行自动分班,并在class_student表中插入一条记下。
分班必要:
|Stu_enter_score |Class_id |Class_name|
|——————-|——————|————–|
|stu_enter_score>=700| 01| 创新A班|
|650<=Stu_enter_score<700| 02| 重点B班|
|600<=Stu_enter_score<650| 03| 提高C班|
|550<=Stu_enter_score<600| 04| 普通D班|
|500<=Stu_enter_score<550| 05| 普通E班|
|Stu_enter_score<500| 06| 普通F班|
推行下列语句

CREATE TRIGGER automatic_division
ON student--新建一个检测student表的触发器,命名automatic_division
FOR INSERT--检测到INSERT操作时触发器工作
AS
DECLARE @score INT,@stu_no VARCHAR(8),@class_id CHAR(2)
--声明三个变量
DECLARE stu_cursor CURSOR LOCAL FORWARD_ONLY--声明一个指向inserted表的局部游标stu_cursor
FOR SELECT stu_no,stu_enter_score FROM inserted
OPEN stu_cursor--打开游标
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--将游标指向inserted表的第一个数据并把游标指向的stu_no和stu_enter_score值分别赋值给@stu_no和@score
WHILE @@FETCH_STATUS=0--开始循环
BEGIN
BEGIN--先对@score的数值范围做判断,以确定该学生的班级编号
IF @score>=700
SET @class_id='01'
ELSE IF @score<700 AND @score>=650
SET @class_id='02'
ELSE IF @score<650 AND @score>=600
SET @class_id='03'
ELSE IF @score<600 AND @score>=550
SET @class_id='04'
ELSE IF @score<550 AND @score>=500
SET @class_id='05'
ELSE
SET @class_id='06'
END
--判断结束
INSERT INTO class_student(class_id,stu_no)
VALUES(@class_id,@stu_no)--将数据插入到class_student表中
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--将游标移向inserted表的下一个数据,重复这个循环
END--循环结束
CLOSE stu_cursor--关闭游标
DEALLOCATE stu_cursor--释放游标资源
GO

说西夏码是或不是准确
student表中插入数据,并查看class_student表中的数据是还是不是科学

INSERT INTO student(stu_no,stu_name,stu_sex,stu_enter_score)
VALUES('20180001','邹莉莉','女','389'),
('20180002','万兴','男','701'),
('20180003','孙伟','男','652'),
('20180004','温佳静','女','676'),
('20180005','姜立夫','男','542')

Class_student表中的数据如图所示
图片 1
游标示例2:对student表中还未分班的学生举办分班
Student表中的数据如图所示
图片 2
其中stu_no20180001~20180005的学生早就在示例1中分班,剩下的上学的小孩子全都未分班。
实行下列语句

ALTER TABLE student
ADD stu_division_state bit--为student表新建一列记录是否已分班,true表示已分班
GO
DECLARE stu_class_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no FROM class_student
--新建游标stu_class_cursor指向class_student表的所有数据
OPEN stu_class_cursor--打开游标
DECLARE @stu_no VARCHAR(8)
FETCH NEXT FROM stu_class_cursor INTO @stu_no
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE student
SET stu_division_state=1
WHERE stu_no=@stu_no
FETCH NEXT FROM stu_class_cursor INTO @stu_no
END
CLOSE stu_class_cursor--关闭游标
DEALLOCATE stu_class_cursor--释放游标资源
---所有学生是否分班已经全部记录在stu_division_state中
GO
DECLARE @stu_no VARCHAR(8),@score INT,@class_id CHAR(2)
DECLARE stu_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no,stu_enter_score FROM student WHERE stu_division_state IS NULL
--新建student表的游标stu_cursor,指向所有未分班学生
OPEN stu_cursor--打开游标
FETCH NEXT FROM stu_cursor INTO @stu_no,@score
WHILE @@FETCH_STATUS=0--循环开始
BEGIN
BEGIN--先对@score的数值范围做判断,以确定该学生的班级编号
IF @score>=700
SET @class_id='01'
ELSE IF @score<700 AND @score>=650
SET @class_id='02'
ELSE IF @score<650 AND @score>=600
SET @class_id='03'
ELSE IF @score<600 AND @score>=550
SET @class_id='04'
ELSE IF @score<550 AND @score>=500
SET @class_id='05'
ELSE
SET @class_id='06'
END
INSERT INTO class_student(class_id,stu_no)
VALUES(@class_id,@stu_no)--将数据插入到class_student表中
UPDATE student--将student表的stu_division_state改成已分班
SET stu_division_state=1
WHERE stu_no=@stu_no
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--将游标移向inserted表的下一个数据,重复这个循环
END--循环结束
CLOSE stu_cursor
DEALLOCATE stu_cursor
GO

结果如图所示
Student表的数据
图片 3
Class_student表的数量
图片 4
至此Student表中有着学员都已分班
为了现在有助于,能够将游标示例2中的代码稍作修改封装成叁个客户自定义存款和储蓄进程
积存进程示例3
修改后的代码如下

CREATE PROCEDURE student_division
AS
BEGIN
UPDATE student
SET stu_division_state=0--先将student表中所有学生的分班情况都标成未分班

DECLARE stu_class_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no FROM class_student
--新建游标stu_class_cursor指向class_student表的所有数据
OPEN stu_class_cursor--打开游标
DECLARE @stu_no VARCHAR(8)
FETCH NEXT FROM stu_class_cursor INTO @stu_no
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE student
SET stu_division_state=1
WHERE stu_no=@stu_no--利用游标找出student表中已分班的学生并标记分班状态
FETCH NEXT FROM stu_class_cursor INTO @stu_no
END
CLOSE stu_class_cursor--关闭游标
DEALLOCATE stu_class_cursor--释放游标资源
---所有学生是否分班已经全部记录在stu_division_state中

DECLARE @score INT,@class_id CHAR(2)
DECLARE stu_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no,stu_enter_score FROM student WHERE stu_division_state=0
--新建student表的游标stu_cursor,指向所有未分班学生
OPEN stu_cursor--打开游标
FETCH NEXT FROM stu_cursor INTO @stu_no,@score
WHILE @@FETCH_STATUS=0--循环开始
BEGIN
BEGIN--先对@score的数值范围做判断,以确定该学生的班级编号
IF @score>=700
SET @class_id='01'
ELSE IF @score<700 AND @score>=650
SET @class_id='02'
ELSE IF @score<650 AND @score>=600
SET @class_id='03'
ELSE IF @score<600 AND @score>=550
SET @class_id='04'
ELSE IF @score<550 AND @score>=500
SET @class_id='05'
ELSE
SET @class_id='06'
END
INSERT INTO class_student(class_id,stu_no)
VALUES(@class_id,@stu_no)--将数据插入到class_student表中
UPDATE student--将student表的stu_division_state改成已分班
SET stu_division_state=1
WHERE stu_no=@stu_no
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--将游标移向inserted表的下一个数据,重复这个循环
END--循环结束
CLOSE stu_cursor
DEALLOCATE stu_cursor
END
GO

注:和游标示例2的代码比较,示例3的代码增添了将具有学生疏班状态标志为0的过程,去掉了充裕stu_division_state列的历程,但对本来已有个别学员的分班状态赋值那一个手续未有删去,而是进行重复校验。並且删除了两段代码中的GO和第二段用于给学生疏班的代码中对@stu_no变量的再一次申明。

student表插入数据并运转student_division的积累进度

注:对student表插入数据前应先禁止使用示例1的触发器automatic_division

进行下列语句

ALTER TABLE student DISABLE TRIGGER automatic_division
--禁用automatic_division触发器
INSERT INTO student(stu_no,stu_name,stu_sex,stu_enter_score,stu_division_state)
VALUES('20180006','王洋','男','724',NULL),
('20180007','易阳','男','713',NULL),
('20180008','孙浩','男','584',NULL),
('20180009','张秋燕','女','420','False'),
('20180010','胡燕','女','527','True')

Student表的数量如图所示,红框内正是本身正要插入还未分班的数目,个中2018000920180010那七个学生的分班状态被笔者误标成FalseTrue
图片 5
实行存款和储蓄进程

EXEC dbo.student_division

结果如图所示
Student表的数码(分班状态都为true了)
图片 6
Class_student表的多少
图片 7

外关键字约束(Foreign Key Constraint)

概念了表之间的关系,用来维护多个表之间的一致性的涉嫌。
在创制表时创立Foreign Key Constraint

CREATE TABLE table2(
    s_id VARCHAR(20),
    s_name VARCHAR(12),
    s_tellphone VARCHAR(11),
    s_address VARCHAR(20),
    CONSTRAINT PK_s_id PRIMARY KEY(s_id),
);--首先新建table2,设置s_id为主键

CREATE TABLE table1(
    t_id VARCHAR(12),
    s_id VARCHAR(20),
    score FLOAT,
    CONSTRAINT pk_ts_id PRIMARY KEY(t_id,s_id),--新建table1,对t_id和s_id设置联合主键,键名pk_ts_id
    CONSTRAINT fk_s_id FOREIGN KEY(s_id)--对s_id设置外键fk_s_id
    REFERENCES table2(s_id)--外键fk_s_id外键关联table2的列s_id
    ON DELETE CASCADE--设置在table1的s_id删除时table2的s_id同时删除
    ON UPDATE CASCADE--设置在table1的s_id更新时table2的s_id同时更新
);

注:对table1设置外键关联table2,在插入数据时索要先插入table2的数码,才干成功插入table1的数码。改造table2.s_id数据,table1.s_id数据也会自动更动。不过退换table1.s_id数据,试行时报外键争辩。同理可得对table1设置外键关联table2后,table1的数额跟着table2走,不能反着来。

丰裕和删除了这一个之外键约束同主键。

1.3.1.2.DELETE触发器

当针对对象数据库运转DELETE言语时就可以激活DELETE触发器。客商一贯运维DELETE话语和行使DELETE触发器又有所不一致,当激活DELETE触发器后,从受触发器影响的表中删除的行会被停放在二个出色的一时表——DELETED表中。DELETED表还同意援引由早先化DELETE语句发生的日记数据。
DELETE触发器被激活时,必要考虑以下几点

  • 当某行被增加到DELETED表中时就不设有于数据库表,因而数据库表和DELETED表不容许存在一样行。
  • 系统自动成立DELETED表时,空间从内存中分红。DELETED表被积攒在高速缓存中。
  • DELETE操作定义的触发器并不进行TRUNCATE
    TABLE
    讲话,原因在于日志不记录TRUNCATE TABLE语句。

示例4:为student表定义一个DELETE触发器,当删除一条学生消息时,class_student表中该学员的分班新闻也会被去除
执行上边的话语

CREATE TRIGGER delete_student
ON student
FOR DELETE
AS
DECLARE @stu_no VARCHAR(8)
DECLARE stu_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no FROM deleted
OPEN stu_cursor
FETCH NEXT FROM stu_cursor INTO @stu_no
WHILE @@FETCH_STATUS=0
BEGIN
DELETE FROM class_student
WHERE stu_no=@stu_no
FETCH NEXT FROM stu_cursor INTO @stu_no
END
CLOSE stu_cursor
DEALLOCATE stu_cursor
GO

测试delete_student触发器的精确性
Student表的数量如图所示
图片 8
Class_student表的数码如图所示
图片 9
实践下列语句

DELETE FROM student
WHERE stu_enter_score<=351
--在student表中删除入学成绩小于分的学生

student表来看,唯有入学编号为2018001120180012的上学的小孩子战绩被删去。该操作激活了delete_student触发器
Class_student表的数额如图所示
图片 10
入学编号为2018001120180012的学面生班信息已经从class_student表中活动删除。

UNIQUE约束

除主键外另一种能够定义独一约束的档次,允许空值。增添UNIQUE的方式同上,这里只简轻巧单比方。

USE test
GO
ALTER TABLE table2
ADD CONSTRAINT uk_s_tellphone
UNIQUE(s_tellphone);

1.3.1.3.UPDATE触发器

当针对对象数据库运营UPDATE言语时就能够激活UPDATE触发器。对UPDATE触发器来讲,有时表INSERTEDDELETED依然有效。UPDATE触发器被激活时,原始行被移入DELETED表中,更新行被移入到INSERTED表中。触发器检查DELETED表和INSERTED表以及被更新的表,来规定是还是不是更新了多行和如何实行触发器动作。
Student表的多寡如图所示
图片 11
Class_student表的数据如图所示
图片 12
示例5:当student表中的stu_no字段更新时,同步更新class_student表中的stu_no字段
实行下列语句新建触发器update_stu_no_single

CREATE TRIGGER update_stu_no_single
ON student
FOR UPDATE
AS
IF UPDATE(stu_no)
BEGIN
UPDATE class_student
SET stu_no=(SELECT stu_no FROM inserted)
WHERE stu_no=(SELECT stu_no FROM deleted)
END
GO

验证update_stu_no_single触发器是还是不是精确,在Student表中实行下列语句,将student表中stu_no为“20180101”的上学的儿童的stu_no改成00000000

UPDATE student
SET stu_no='00000000'
WHERE stu_no='20180101'

举行成功后,update_stu_no_single触发器被激活,class_student表的数量如图所示
图片 13

注:update_stu_no_single触发器只可以对单行记录的UPDATE操作起效,假诺批量UPDATE
stu_no
,实行语句时会提醒子查询再次回到的值持续1个。上边包车型大巴示例6将提供批量UPDATE
stu_no
的触发器

示例6:实现当student表的stu_no字段批量更新时,class_student表的stu_no也五头批量创新
首先将student表和class_student表的数码修改成原来的规范,何况删除update_stu_no_single触发器
Student表的多寡如图所示
图片 14
Class_student表的数据如图所示
图片 15
施行下列语句新建触发器update_stu_no_batch

CREATE TRIGGER update_stu_no_batch
ON student
FOR UPDATE
AS
DECLARE @stu_no_insert VARCHAR(8),@stu_no_delete VARCHAR(8)
DECLARE stu_cursor_insert CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no FROM inserted
OPEN stu_cursor_insert
DECLARE stu_cursor_delete CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no FROM deleted
OPEN stu_cursor_delete
FETCH NEXT FROM stu_cursor_insert INTO @stu_no_insert
FETCH NEXT FROM stu_cursor_delete INTO @stu_no_delete
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE class_student
SET stu_no=@stu_no_insert
WHERE stu_no=@stu_no_delete
FETCH NEXT FROM stu_cursor_insert INTO @stu_no_insert
FETCH NEXT FROM stu_cursor_delete INTO @stu_no_delete
END
CLOSE stu_cursor_insert
CLOSE stu_cursor_delete
DEALLOCATE stu_cursor_insert
DEALLOCATE stu_cursor_delete
GO

验证update_stu_no_batch触发器的准头,对student表实行下列语句,完结批量修改操作

UPDATE student
SET stu_no='00000000'
WHERE stu_no LIKE '201801%'
GO

Student表的数目如图所示
图片 16
Class_student表的数码如图所示
图片 17
咱俩再来验证update_stu_no_batch触发器对立异单行stu_no多少是还是不是行得通。将student表class_student表的数额改回原本的轨范,然后实施下列语句

UPDATE student
SET stu_no='00000000'
WHERE stu_no='20180101'

Class_student表的数据如图所示
图片 18

注:在将表数据改成原本的样板时,直接在编排前200行中操作还是用T-SQL话语操作,对student表数据操作,不成事的话要缅怀受键和束缚的影响,对class_student表数据操作,不成事的话要想念受触发器影响。

CHECK约束

分为表约束和列约束,用于限制字段值在有些范围。

1.3.1.4.INSTEAD OF触发器

INSTEAD
OF
触发器能够钦点奉行触发器,实际不是实行触发SQL话语,进而屏蔽原本的SQL说话,而转向实践触发器内部的讲话。各个表也许视图只好有1个INSTEAD
OF
触发器。INSTEAD
OF
触发器的性状是,能够使作为触发条件的SQL语句不实施。
Membership表的数额如图所示
图片 19
Call_slip表的多寡如图所示
图片 20
示例7:对LibraryManagement数据Curry的membership表写多个防删除触发器,尚有借书未还的读者不恐怕被删去
实践下列语句创立member_delete_single触发器

CREATE TRIGGER member_delete_single
ON membership
INSTEAD OF DELETE
AS
BEGIN
IF NOT EXISTS(SELECT * FROM call_slip WHERE member_id=(SELECT member_id FROM deleted) AND borrow_state='未归还')
DELETE FROM membership WHERE member_id=(SELECT member_id FROM deleted)
ELSE
BEGIN
SELECT '该用户尚有图书未还,无法删除'
SELECT * FROM call_slip WHERE member_id=(SELECT member_id FROM deleted) AND borrow_state='未归还'
END
END
GO

申明触发器的正确,实行下列语句

DELETE FROM membership
WHERE member_id='20060128'

结果如图所示
图片 21
该触发器只针对DELETE一条数据有效
示例8:对LibraryManagement数据Curry的membership表写一个防批量删除触发器,尚有借书未还的读者不恐怕被去除
Membership表的数据如图所示
图片 22
Call_slip表的数量如图所示
图片 23
推行下列语句新建触发器(将示例7中的member_delete_single触发器先删除)

CREATE TRIGGER member_delete_batch
ON membership
INSTEAD OF DELETE
AS
BEGIN
DECLARE member_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT member_id FROM deleted
OPEN member_cursor
DECLARE @member_id VARCHAR(8)
FETCH NEXT FROM member_cursor INTO @member_id
WHILE @@FETCH_STATUS=0
BEGIN
BEGIN
IF NOT EXISTS(SELECT* FROM call_slip WHERE member_id=@member_id AND borrow_state='未归还')
DELETE FROM membership WHERE member_id=@member_id
ELSE
PRINT '用户'+@member_id+'无法删除'
END
FETCH NEXT FROM member_cursor INTO @member_id
END
CLOSE member_cursor
DEALLOCATE member_cursor
END
GO

结果如图所示
图片 24
Membership表的数目如图所示
图片 25
示例9:对LibraryManagement数据库里的call_slip表写一个防超借触发器,几个读者的未还图书最两只可以有5本,超过不可能再借(这里如故针对批量处理数据创造触发器)
Call_slip表的多少如图所示
图片 26
施行下列语句创设provent_overborrowing_batch触发器

CREATE TRIGGER provent_overborrowing_batch
ON call_slip
INSTEAD OF INSERT
AS
BEGIN
DECLARE @member_id VARCHAR(8)
DECLARE borrow_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT DISTINCT member_id FROM inserted
OPEN borrow_cursor
FETCH NEXT FROM borrow_cursor INTO @member_id
WHILE @@FETCH_STATUS=0
BEGIN
BEGIN
IF (SELECT COUNT(*) FROM call_slip WHERE member_id=@member_id AND borrow_state='未归还')<5
INSERT INTO call_slip SELECT * FROM inserted WHERE member_id=@member_id
ELSE
PRINT '用户'+@member_id+'已借阅且未还的图书超过5本,无法再借'
END
FETCH NEXT FROM borrow_cursor INTO @member_id
END
END
GO

进行下列语句测量试验provent_overborrowing_batch触发器的科学,当中member_id为“20060128”的顾客借书未还超越5本,应该是心余力绌再借的。

--测试数据
INSERT INTO call_slip(book_id,member_id,loan_period,borrow_state)
VALUES('20130002','20060128','30','未归还'),
('20130001','20060128','20','未归还'),
('20130003','20060128','30','未归还'),
('20130004','20062919','30','未归还'),
('20130005','20150821','45','未归还')

结果如图所示
图片 27
Call_slip表的数码如图所示,红框里是新插入的数额
图片 28

添加CHECK约束
ALTER TABLE table2
ADD sex CHAR(2);--在table表中添加sex,数据类型为CHAR,用来存放性别
GO
ALTER TABLE table2
ADD CONSTRAINT ck_sex CHECK(sex in('男','女'));

注:此时sex列数据类型无法是bit,假使填写bit,只可以存款和储蓄0和1,用CHECK约束限制结果为男和女就能够报错。

1.3.2.创建DDL触发器

DDL触发器只为了响应CREATEDROPALTER事件而激活,它的作用域是整套数据库恐怕服务器,并非效果与利益域某张表或盘算。它能够有效调整哪位客户可以修改数据库结构以及哪些修改。
示例10:创设一个DDL触发器,调整上班时间(8:00-18:00)不能对LibraryManagement数量库表和总计结构进行新建,修改和删除操作。
实践下列语句创建触发器deny_DDL_table

CREATE TRIGGER deny_DDL_table
ON DATABASE
WITH ENCRYPTION
FOR CREATE_TABLE,DROP_TABLE,ALTER_TABLE
AS
DECLARE @eventdata XML
SET @eventdata=EVENTDATA()
IF(DATEPART(HOUR,GETDATE()) BETWEEN 8 AND 17)
BEGIN
SELECT '触发器deny_DDL_table已禁止工作时间8:00-18:00对LibraryManagement数据库的CREATE,ALTER,DROP操作'
SELECT @eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') AS EventType,--事件类型
@eventdata.value('(/EVENT_INSTANCE/PostTime)[1]','nvarchar(max)') AS PostTime,--时间触发的时间
@eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)') AS DatabaseName,--数据库名字
@eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)') AS ObjectName,--操作的对象名称
@eventdata.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)') AS ObjectType,--操作的对象类型
@eventdata.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') AS CommandText--操作命令文本
ROLLBACK---对操作进行回滚,也可以不回滚
END
GO

实践以下代码以测验DDL触发器deny_DDL_table的不利

USE LibraryManagement
CREATE TABLE test(
t_id VARCHAR(2),
t_name VARCHAR(20)
)

结果如图所示
图片 29
图片 30

注:EVENTDATA()可在触发器内部使用,重临有关数据库和服务器事件的新闻,以XML格式重临。独有一向在DDL或登入触发器内部引用EVENTDATA时,EVENTDATA才会回到数据。即便EVENTDATA由其它例程调用(即便这一个例程由DDL或登入触发器进行调用),将重临
NULL

在创造表的时候增添CHECK 约束
CREATE TABLE table3(
    t3_id VARCHAR(12),
    t3_name VARCHAR(20),
    t3_class VARCHAR(12),
    t3_type VARCHAR(12),
    CONSTRAINT ck_t3_type CHECK(t3_type in('类型1','类型2','类型3')) 
)

加多了约束后假诺插入不吻合约束的数额

INSERT INTO table3(
    t3_id,
    t3_name,
    t3_class,
    t3_type
)VALUES(
'2018038219',
'李建',
'社会与科学',
'任何数据'
);

消息547,级别16,状态0,第1 行
INSERT 语句与CHECK
约束”ck_t3_type”抵触。该顶牛爆发于数据库”test”,表”dbo.table3″, column
‘t3_type’。

言辞已告一段落。

1.3.3.嵌套触发器

删除CHECK约束
ALTER TABLE table3
DROP CONSTRAINT ck_t3_type;

1.3.3.1.嵌套触发器

若果三个触发器在实行操作时引发了另叁个触发器,而以此触发器又抓住了下八个触发器,那么那么些触发器正是嵌套触发器。嵌套触发器在装置时就被启用,不过足以应用sp_configure存款和储蓄进度禁用和再度启用嵌套。
DML触发器和DDL触发器最多能够嵌套32层,可以通过nested
triggers
来配置是或不是能够嵌套AFTER触发器,可是不论此设置什么样都足以嵌套INSTEAD
OF
触发器。假使嵌套触发器步入了最佳循环,该触发器将被甘休,並且回滚整个业务。嵌套触发器械有多样用场,比如保留前一个触发器所影响的行的副本。
动用嵌套触发器时应该注意以下几点:

  • 暗许意况下,嵌套触发器配置选项开启。
  • 在同四个触发器事务中,一个触发器不会被触发三遍,触发器不会调用他和煦来响应触发器中对同一个表的第一回创新
  • 出于触发器是二个事务,一旦嵌套中其余一层的触发器出现错误,将回滚整个业务。

示例11:有teacher_course表(教授所教学程表),course表(课程表)和course_selection表(学生选课表),写二个嵌套触发器,达成课程撤消后,删除教授所教师程表中有关该科目标笔录,而老师所教学程表中该课程的记录被撤销,导致该学科的学习者选课记录也做相应撤消。
实行下列语句

--创建course表上的触发器,删除course表中的课程,teacher_course表中的记录做对应删除
CREATE TRIGGER course_delete_batch
ON course
FOR DELETE
AS
DECLARE @course_id CHAR(4)
DECLARE course_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT course_id FROM deleted
OPEN course_cursor
FETCH NEXT FROM course_cursor INTO @course_id
WHILE @@FETCH_STATUS=0
BEGIN
DELETE FROM teacher_course WHERE course_id=@course_id
FETCH NEXT FROM course_cursor INTO @course_id
END
GO
--创建teacher_course表上的触发器,删除教师课程表的记录,学生选课表的记录也做对应删除
CREATE TRIGGER teacher_course_delete_batch
ON teacher_course
FOR DELETE
AS
DECLARE @course_id CHAR(4)
DECLARE teacher_course_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT DISTINCT course_id FROM deleted
OPEN teacher_course_cursor
FETCH NEXT FROM teacher_course_cursor INTO @course_id
WHILE @@FETCH_STATUS=0
BEGIN
IF (SELECT COUNT(*) FROM teacher_course WHERE course_id=@course_id)=0
DELETE FROM course_selection WHERE course_id=@course_id
ELSE
PRINT 'course_id为'+@course_id+'的课程依然正常开课,该课程的学生选课情况不予删除'
FETCH NEXT FROM teacher_course_cursor INTO @course_id
END
GO

course_delete_batch和**
teacher_course_delete_batch就产生了三个嵌套触发器,下边来证实嵌套触发器的不错。 Course表中的数据如图所示
图片 31
Teacher_course表中的数据如图所示
图片 32
Course_selection**表中的数据如图所示
图片 33
以课程0013为例,施行下列语句

DELETE FROM course WHERE course_id='0013'

Course表的数额如图所示
图片 34
Teacher_course表的多寡如图所示
图片 35
Course_selection表的数量如图所示
图片 36
具有关于0013学科的数码都被删除。嵌套触发器有效。

注:在触发器teacher_course_delete_batch中,笔者额外加入了二个确定,当teacher_course表中还会有老师在执教那门科目时,全数关于那门课程的学习者选课新闻都不以为然删除。那样做在嵌套触发器里是剩下的,删除一门科目,必然会去除teacher_course表中享有与那门科目有关的笔录,也料定删除course_selection表中有所与那门课程有关的笔录,不过,那样做能够保证该触发器能够单独于嵌套触发器被单独激活。Teacher_course_delete_batch触发器仍是能够用来另外嵌套触发器中,看示例12

示例12:有teacher表(教师新闻表),teacher_course(教授所教授程表),和course_selection表(学生选课记录表),写三个嵌套触发器,完结当二个老师离职时,在剔除该老师所教课程音讯,若无教师教那门学科,再删除该学科选课记录。
其中teacher_course表的触发器teacher_course_delete_batch已经在示例11中写完,只需成立teacher表的teacher_delete_batch触发器就可以
进行下列代码

CREATE TRIGGER teacher_delete_batch
ON teacher
FOR DELETE
AS
DECLARE @teacher_id CHAR(4)
DECLARE teacher_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT teacher_id FROM deleted
OPEN teacher_cursor
FETCH NEXT FROM teacher_cursor INTO @teacher_id
WHILE @@FETCH_STATUS=0
BEGIN
DELETE FROM teacher_course WHERE teacher_id=@teacher_id
FETCH NEXT FROM teacher_cursor INTO @teacher_id
END
GO

测量试验嵌套触发器的没有错
Teacher表的数目如图所示
图片 37
Teacher_course表的数码如图所示
图片 38
Course_selection表的多少如图所示
图片 39
以删除0012号教授路易为例,0012号教师教学0013号课程,且teacher_course表中并无任何教授授课0013号课程,根据逻辑要去除teacher_course表中0012号教师的所教课程记录和course_selection表中有所0013号课程的选课记录。试行下列语句

DELETE FROM teacher WHERE teacher_id='0012'

Teacher表的多少如图所示
图片 40
Teacher_course表的数据如图所示
图片 41
Course_selection表的数量如图所示
图片 42
测量试验结果正确
参谋下面的多寡,继续测验另一种景况,以删除0011号教授卢含笑为例,0011号教授执教0012号课程,在teacher_course表中还会有别的教授教学该学科,因而嵌套触发器会去除teacher_course表中有关0011号教授授课课程记录,但不会删除course_selection表中关于0012号课程的选课记录。推行下列语句

DELETE FROM teacher WHERE teacher_id='0011'
GO

结果如图所示
图片 43
Teacher表的数量如图所示
图片 44
Teacher_course表的数目如图所示
图片 45
Course_selection表的数额如图所示
图片 46

DEFAULT约束

透过定义列的私下认可值或行使数据库的暗中同意值对象绑定表列,来钦点列的暗中同意值。

1.3.3.2.翻看触发器嵌套的层数

能够选拔@@NESTLEVEL全局变量来查看当前触发器嵌套的层数
示例13:在示例11teacher_course_delete_batch触发器中接纳@@NESTLEVEL全局变量查看当前触发器嵌套的层数
实践下列语句修改teacher_course_delete_batch触发器

ALTER TRIGGER teacher_course_delete_batch
ON teacher_course
FOR DELETE
AS
DECLARE @course_id CHAR(4)
DECLARE teacher_course_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT DISTINCT course_id FROM deleted
OPEN teacher_course_cursor
FETCH NEXT FROM teacher_course_cursor INTO @course_id
WHILE @@FETCH_STATUS=0
BEGIN
IF (SELECT COUNT(*) FROM teacher_course WHERE course_id=@course_id)=0
DELETE FROM course_selection WHERE course_id=@course_id
ELSE
PRINT 'course_id为'+@course_id+'的课程依然正常开课,该课程的学生选课情况不予删除'
FETCH NEXT FROM teacher_course_cursor INTO @course_id
SELECT @@NESTLEVEL AS NESTLEVEL
END
GO

测试teacher_course_delete_batch触发器(数据就不看了,未影响触发器原本的功用)
奉行下列语句

DELETE FROM teacher_course WHERE teacher_id='0009'
--直接在teacher_course表中删除,激活teacher_course_delete_batch触发器

结果如图所示
图片 47
施行下列语句

DELETE FROM teacher WHERE teacher_id='0009'
--在teacher表中删除,触发teacher_delete_batch触发器,进而触发teacher_course_delete_batch触发器

结果如图所示
图片 48

在建表时加多DEFAULT约束
CREATE TABLE table3(
    t3_id VARCHAR(12),
    t3_name VARCHAR(20),
    t3_class VARCHAR(12),
    t3_type VARCHAR(12) DEFAULT '类型1' 
)

1.3.3.3.禁止使用和启用嵌套触发器

EXEC sp_configure 'nested triggers',0;
GO
--禁用嵌套触发器
EXEC sp_configure 'nested triggers',1;
GO
--启用嵌套触发器
删除DEFAULT约束
ALTER TABLE table3
DROP CONSTRAINT DF__table3__t3_type__3D5E1FD2;
--DF__table3__t3_type__3D5E1FD2是DEFAULT约束的约束名

1.3.4.递归触发器

加上封锁不点名约束名
ALTER TABLE table3
ADD DEFAULT '类型2' FOR t3_type;
GO

1.3.4.1.递归触发器

触发器被激活,改变了表中多少,这种变动又激活了它谐和,这种触发器被称呼递归触发器。数据库创制时私下认可递归触发器禁用。但可以使用ALTER
DATABASE
慎选来启用它。递归触发器启用的先决条件是嵌套触发器必需是启用情形,假诺嵌套触发器禁止使用,不管递归触发器的安插是何许都将被剥夺。而在递归触发器中,inserted表和deleted表都只包括被上一遍触发器影响的行数据。
递归触发器有以下二种不一样品类(那边未有确切的选择示范可举,先不举例了)

累加封锁内定约束名
ALTER TABLE table3
ADD CONSTRAINT df_t3_type
DEFAULT '类型2' FOR t3_type;
GO

1.3.4.2.直接递归

直白递归触发器是指任何递归进程唯有它自身多少个触发器的涉企。自己激活了团结。

NOT NULL约束

自律字段值不为空。

1.3.4.3.直接递归

直接递归触发器是指任何递归进程有多个触发器到场,比方A激活B,B激活C,C激活A。能够当作是递归和嵌套的结缘。
运用递归触发器时索要留心以下几点:
递归触发器很复杂,需求通过有系统的布署性和周全测量检验
在自便点的数码修改都会激活递归触发器。只好按触发器被激活的特定顺序更新表。
享有触发器一齐组成一个大事务,率性触发器的自由地方上的ROLLBACK言辞都将打消全体数据的输入,全数数据均被擦除。
触发器最八只可以递归16层,一旦有第14个触发器参预进来,结果与ROLLBACK指令同样,全体数据都将被擦除

建表时设置NOT NULL约束
CREATE TABLE table3(
    t3_id VARCHAR(12) NOT NULL,
    t3_name VARCHAR(20) NOT NULL,
    t3_class VARCHAR(12) NOT NULL,
    t3_type VARCHAR(12) NOT NULL 
)

1.3.4.4.启用递归触发器

能够应用SQL Server 2008的管理器工具来启用递归触发器。
图片 49

为已存在的列增多NOT NULL约束
ALTER TABLE table3
ALTER COLUMN t3_type VARCHAR(12) NOT NULL;

1.4.管制触发器

剥夺和启用触发器
施行下列语句禁止使用和启用触发器

ALTER TABLE student DISABLE TRIGGER update_stu_no_single
--禁用update_stu_no_single触发器
GO
ALTER TABLE student ENABLE TRIGGER update_stu_no_single
--启用update_stu_no_single触发器
GO

实践下列语句禁止使用和启用数据库等级触发器

DISABLE TRIGGER deny_DDL_table ON DATABASE
--禁用数据库级别触发器deny_DDL_table
GO
ENABLE TRIGGER deny_DDL_table ON DATABASE
--启用数据库级别触发器deny_DDL_table
GO
删除NOT NULL约束
ALTER TABLE table3
ALTER COLUMN t3_type VARCHAR(12) NULL;
GO

自定义私下认可值对象有限支撑数据完整性

CREATE DEFAULT date_today AS GETDATE();
--新建默认值对象名date_today,默认值为getdate()函数,获取当前日期
GO
EXEC sp_addtype date_time,'date','NULL';
--利用存储过程新建自定义数据类型date_time,参照系统数据类型date
GO
EXEC sp_bindefault 'date_today','date_time';
--将默认值对象date_today绑定到自定义数据类型date_time上
GO
CREATE TABLE table3(--新建table3,设置字段t3_date的数据类型为date_time
    t3_id VARCHAR(12),
    t3_name VARCHAR(20),
    t3_class VARCHAR(12),
    t3_type VARCHAR(12),
    t3_date date_time
);
GO
INSERT INTO table3(--为新建表table3插入一条数据,不指定t3_date的值,看默认值是否有效
    t3_id,
    t3_name,
    t3_class,
    t3_type
)VALUES(
'2018038220',
'李建',
'社会与科学',
'类型1'
);
GO
SELECT * FROM table3;--查询table3数据,看t3_date是否有默认值为当前日期

查询结果如下
图片 50

ALTER TABLE table3
ADD t3_date1 DATE;--在table3表中新增一列t3_date1,数据类型为DATE
GO
EXEC sp_bindefault 'date_today','table3.t3_date1';
--直接将默认值对象date_today绑定到table3的t3_date1列
GO
INSERT INTO table3(--为新建表table3插入一条数据,不指定t3_date和t3_date1的值,看默认值是否有效
    t3_id,
    t3_name,
    t3_class,
    t3_type
)VALUES(
'2018038221',
'李建',
'社会与科学',
'类型'
);
GO
SELECT * FROM table3;
GO

查询结果如下
图片 51

仓库储存进度查询私下认可值对象的持有者
USE test
EXEC sp_help date_today;
GO

结果如图所示
图片 52

删去暗中同意值对象
DROP DEFAULT date_today;

剔除不成事,提醒以下音信:
消息3716,级别16,状态3,第1 行
没辙删除默许值’date_today’,因为它已绑定到一个或多少个列。
那么我们驾驭,当两个私下认可值对象绑定了列其后,就不能够删除它,假设想要删除,就非得先解绑。在下边包车型大巴操作中,大家的暗许值对象date_today绑定了test数据库table3表的t3_date1字段。

对列解绑暗许值对象
USE test
GO
EXEC sp_unbindefault 'table3.t3_date1';

那时大家再次尝试删除暗中同意值对象,开掘依然极其,此时要注意,在地点的操作中,私下认可值对象date_today不仅仅绑定了t3_date1列,还绑定了自定义数据类型date_time,并将该数据类型定义给了t3_date列,我们还必要对date_time解绑暗许值对象。

EXEC sp_unbindefault 'date_time';

那儿再次删除暗中同意值对象,就足以成功删除。删除暗中同意值对象后,原先绑定的字段不会再有暗中同意值。

自定义法则维护数据完整性

平整是对列或自定义数据类型的值的规定和限制。自定义准则的表达式必须要赶回布尔类型的值,并且表明式中不可能满含七个变量。

CREATE RULE score_rule AS @math_score>=0;
GO--新建规则score_rule,参数@math_score
EXEC sp_addtype 'score_type','float','NULL';
GO--新建自定义数据类型score_type
CREATE TABLE table_score(--新建表table_score,预设mt_score和at_score字段用于绑定规则
s_id VARCHAR(4),
s_name VARCHAR(10),
mt_score float,--该字段将用于规则score_rule绑定到列
at_score score_type--该字段将用于规则score_rule绑定到自定义数据类型
);
GO
EXEC sp_bindrule 'score_rule','score_type';
GO--将score_rule规则绑定到自定义数据类型score_type
EXEC sp_bindrule 'score_rule','table_score.mt_score';
GO--将score_rule规则绑定到table_score表的mt_score列

----以下进行规则测试
INSERT INTO table_score(
s_id,
s_name,
mt_score,
at_score
)VALUES(
'0001',
'张华',
'-1',
'-1'
);
GO

开展违反准则的插入后,数据库报错
消息513,级别16,状态0,第1 行
列的插入或更新与原先的CREATE RULE
语句所钦点的平整爆发冲突。该语句已偃旗息鼓。争辨发生于数据库’test’,表’dbo.table_score’,列’mt_score’。

言语已终止。
很明显,mt_score的插入值为-1,违反了总得超过等于0的平整,数据库报错。将mt_score的插入值改成符合准则的数据,再度运营插入语句,数据库依旧会报错,因为at_score字段的插入值也是违背法则的。将五个数据改成适合准绳的回到,推行成功。

注:新建准绳时表达式必须固然回来布尔类型的值,不然会报错

消息4145,级别15,状态1,过程sum_score,第1 行
在应选用条件的上下文(在’;’ 相近)中钦命了非布尔类型的表明式。

除去自定义法规

和自定义暗中认可值对象一样,删除自定义法则供给该法则先与字段和自定义数据类型解绑。在地点的操作中,score_rule准绳与自定义数据类型score_type以及列mt_score已绑定。由此实施以下语句:

EXEC sp_unbindrule 'score_type';
GO--解除规则score_rule与score_type之间的绑定
EXEC sp_unbindrule 'table_score.mt_score';
GO--解除规则score_rule与表table_score的mt_score列的绑定
DROP RULE score_rule;--删除score_rule规则

注:经过试验,贰个列只可以绑定1条法规,若是对八个列绑定2条准绳,前一条准绳会被后一条法规顶替。

翻开自定义法规
EXEC sp_help 'score_rule';

结果如图所示
图片 53

查看自定义法规的定义音信
EXEC sp_helptext 'score_rule';
GO

结果如图所示
图片 54

留下评论

网站地图xml地图