实验六：触发器设计实验(2)

create trigger trdstudent on student for delete as

delete SC

from SC s,deleted d where s.sno=d.sno

--验证:

delete

from Student

where Sno='200215122';

select * from SC;

select *

from Student;

--禁用触发器后再验证:

alter table Student disable trigger StudentTrigger

delete

from Student

where Sno='200215121';

select * from SC;

select *

from Student;

--(2)创建一个触发器，不允许删除Course1表中的任何数据。

create trigger Course1Trigger on Course1 for delete as

if(select count(*) from deleted)>0 begin

print('你不能删除该表中的任何记录')

rollback transaction end

--验证:

delete

from Course1 where Cno='2';

select *

from Course1;

--禁用触发器后再验证:

alter table Course1 disable trigger Course1Trigger;

delete

from Course1 where Cno='3';

select *

from Course1;

--2.利用实验五中的Worker表，分别建立以下触发器：

--创建表worker

create table worker (number char(5),

name char(8) constraint U1 unique, sex char(2),

sage int constraint U2 check(sage<=28), department char(20)

constraint PK_workr primary key (number) );

--(1)为worker表建立触发器T1，当插入或更新表中的数据时，保证所操作的记录的sage值大于0。

create trigger T1 on worker for insert,update AS

If (select sage from inserted )<1 begin

print '年龄sage必须大于0,插入或更新失败'

Rollback transaction End

--验证:

insert

into worker(number,name,sex,sage,department) values ('0001','张道','男',-2,'财务部')

--禁用触发器后再验证:

alter table worker disable trigger T1

insert

into worker(number,name,sex,sage,department) values ('0001','张道','男',-2,'财务部')

select *

from worker;

--(2)为worker表建立触发器T2，禁止删除编号为“0001”的职工。

create trigger T2 on worker for delete AS

--declare @number_id char(5)

If (select number from deleted )='0001' begin

print '不能删除编号0001的记录' Rollback transaction End

--验证:

delete

from worker

where number='0001'

--禁用触发器后再验证:

alter table worker disable trigger T2

delete

from worker

where number='0001'

select *

from worker;

--(3)worker表中的人员的编号是唯一且不可改变的，为worker表建立触发器T3实现更新中编号的不可改变性。

create trigger T3 on worker for update AS

if update(number) begin

print '不能更改编号' Rollback transaction End

--验证:

insert

into worker(number,name,sex,sage,department) values ('0001','张道','男',-2,'财务部');

update worker set number='0002' where number='0001';

--禁用触发器后再验证:

alter table worker disable trigger T3

update worker set number='0002' where number='0001';

select *

from worker;

--(4)为worker表建立触发器T4，要求插入记录的sage值必须比表中已记录的最大sage值大。

Create trigger T4 on worker For insert AS

IF(SELECT sage from inserted)<=(select max(sage) from worker) Begin

print'插入记录的sage值必须比表中已记录的最大sage值大' Rollback Transaction End

--验证:

insert

into worker(number,name,sex,sage,department) values ('0007','王旺','男',-9,'人事部');

--禁用触发器后再验证:

alter table worker disable trigger T4;

insert

into worker(number,name,sex,sage,department) values ('0007','王旺','男',-9,'人事部');

select *

from worker;

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库实验六：触发器设计实验(2)在线全文阅读。