T-sql

use master
if exists(select * from sysdatabases where name='chenhangbing' )
begin
raiserror('delete database chenhangbing please wait ......',0,1)
drop database chenhangbing
raiserror ('database chenhangbing is deleted',0,1)
end  
go

create database  chenhangbing
go


/****************创建数据表*****************************/

use chenhangbing

if exists(select * from sysobjects where name='student' and type='t')
drop table student
go

create table student
(
stu_id int identity(980900,1) primary key ,
stu_name varchar(15) not null check(stu_name like '[a-z]'  
or stu_name like '[a-z][a-z]'
or stu_name like '[a-z][a-z][a-z]'
or stu_name like '[a-z][a-z][a-z][a-z]'
or stu_name like '[a-z][a-z][a-z][a-z][a-z]'
or stu_name like '[a-z][a-z][a-z][a-z][a-z][a-z]'
or stu_name like '[a-z][a-z][a-z][a-z][a-z][a-z][a-z]'  
or stu_name like '[a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z]'
or stu_name like '[a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z]'
or stu_name like '[a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z]'  
or stu_name like '[a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z]'
or stu_name like '[a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z]'
or stu_name like '[a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z]'
or stu_name like '[a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z]'
or stu_name like '[a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z][a-z]' and len(stu_name)<15  and len(stu_name)>0),
stu_gender char(2) not null check(stu_gender like '[f]' or stu_gender like '[m]' and len(stu_gender)=1) default 'F' ,
stu_age tinyint check(stu_age>0 and stu_age<100) ,
stu_add varchar(60) ,
)

go

select * from student
go

create table score
(
stu_id int primary key not null ,
stu_name varchar(15) not null ,
chinese int default 0 ,
math int default 0 ,
english int default 0 ,
avg_score as (chinese+math+english)/3  ,
tot_score as (chinese+math+english)  ,

)
go



/*****************为student表创建触发器,保证chenhangbing的记录不重复********************/
use chenhangbing  
go

if exists(select * from sysobjects where name='stu_insrt' and type='tr')
drop trigger stu_insrt
go

create trigger stu_insrt on student for insert  
as  
if (select count(*) from student where stu_name like 'chenhangbing')>1
begin
print 'error: insert the count of stu_name is double !! insert error '
rollback transaction
end  
go

/*****************保证不再插入相同的记录********************/

if exists(select * from sysobjects where name='stu_insert' and type='tr')
drop trigger stu_insert  
go


create trigger stu_insert on student for insert  
as  
if (select count(*)  from student where stu_name in (select stu_name from inserted ))>1
begin
raiserror( 'error notes:this studentname i* **ists cannot insert again!!! insert  data error',0,1)
rollback tran  
end
go

/***********************保证chenhangbing的记录不被删除*************************/
用途:假设一网站,chenhangbing的用户是超级用户不能删除

if exists(select * from sysobjects where name='stu_del' and type='tr')
drop trigger stu_del
go

create trigger stu_del on student for delete
as  

if exists(select * from deleted where stu_name like 'chenhangbing' )
begin  
raiserror( 'note:cannot delete the count of administrator ,delete error ',0,1)
rollback transaction  
end
go

/*******************保证chenhangbing的记录不被改动***************************/
if exists(select * from sysobjects where name='stu_update' and type='tr')
drop trigger stu_update
go

create trigger stu_update on student for update
as  
if exists(select * from deleted where stu_name like 'chenhangbing')
begin
rollback tran  
raiserror( 'adminstrator cannot be changed !!',0,1)
end
go

/**********************************************************************/
insert into student (stu_name,stu_gender,stu_age,stu_add) values('chenhangbing','M',22,'湖南省长沙市')
insert into student (stu_name,stu_gender,stu_age,stu_add) values('liming','F',21,'湖南省长沙市')
insert into student (stu_name,stu_gender,stu_age,stu_add) values('chenze','M',23,'湖南省长沙市')
insert into student (stu_name,stu_gender,stu_age,stu_add) values('liuwen','F',23,'湖南省长沙市')
insert into student (stu_name,stu_gender,stu_age,stu_add) values('chenhangbing','M',20,'湖南省长沙市')

go

delete from student where stu_name like 'chenhangbing' 测试确发器，正确的情况应该是不允许删该记录
select * from student

go

select * from student
select  * from score


truncate table score

insert into score (stu_id,stu_name,chinese,math,english) values(980900,'chenhangbing',82,86,92)
insert into score (stu_id,stu_name,chinese,math,english) values(980901,'liming',84,85,90)
insert into score (stu_id,stu_name,chinese,math,english) values(980902,'chenze',88,88,97)
insert into score (stu_id,stu_name,chinese,math,english) values(980903,'liuwen',85,83,89)
insert into score (stu_id,stu_name,chinese,math,english) values(980904,'chenhangbing',66,80,72)

go

select * from score
select * from student

update score set chinese=60  


/***************************使用游标***************************************/

declare upde cursor
for select * from student  

open upde

fetch next from upde
close upde
deallocate upde

/*************************使用存储过程**********************************/
use chenhangbing
if exists(select * from sysobjects where name='disp_info' and type='p')
drop proc disp_info

go
create proc disp_info @stu_name as varchar(15)='chenhangbing'

as  
select sco.stu_name as 姓名,stu_age as 年龄,chinese as 语文 ,math as 数学,english as 英语 from student stu inner join score
sco on stu.stu_name=sco.stu_name
where  @stu_name=sco.stu_name
go

execute disp_info 'chenhangbing'
select * from student
select * from score

/*********************************************************************/


if exists(select * from sysobjects where name='score_insrt_update' and type='tr')
drop trigger score_insrt_update
go

create trigger score_insrt_update on score for insert ,update  
as  

if exists(select * from inserted)
begin

update score set tot_score=chinese+math+english ,avg_score=(chinese+math+english)/3 where stu_name in (select stu_name from inserted)
end