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;