SQLServer创建触发器

建数据库

create database school

--建表 use school create table students (

 s_id int identity(1,1) primary key,--设主键，为自增i

d  s_name varchar(20) not null,  

s_classId int )

create table class (  c_id int identity(1,1) primary key,--设主键，为自增

id  c_className varchar(20) not null )

create table student_Score (  ss_id  int identity(1,1) primary key,--设主键，为自增

id  ss_score varchar(200) not null,  s_id int )

--设外键

use school alter table students add constraint FK_class_studets foreign key(s_classId)references class(c_id)

--设外键 use school alter table student_Score add constraint FK_students_student_Score foreign key(s_id)references students(s_id)

--插class数据 insert into class(c_className) values(‘08ACCP1班‘) insert into class(c_className) values(‘08ACCP2班‘)

insert into class(c_className) values(‘08ACCP3班‘) insert into class(c_className) values(‘08ACCP4班‘)

insert into class(c_className) values(‘08网编1班‘) insert into class(c_className) values(‘08网编2班‘) insert into class(c_className) values(‘08软开1班‘) insert into class(c_className) values(‘08软开2班‘) insert into class(c_className) values(‘08软开3班‘) insert into class(c_className) values(‘08信管1班‘)

--插studets数据 insert into students(s_name,s_classId) values(‘陈志锦‘,2) insert into students(s_name,s_classId) values(‘何金喜‘,4) insert into students(s_name,s_classId) values(‘黄瑞驰‘,5)

--单表查询

--查studets表 select * from students --查class表 select * from class

--多表查询

--内联接查询 --情况1： select * from students as s,class as c where s.s_classId=c.c_id --情况2： select * from students as s inner join class as c  on(s.s_classId=c.c_id)

--外联接查询 --左外联接 select * from students as s left outer join class as c on s.s_classId=c.c_id select * from class as c left outer join students as s on s.s_classId=c.c_id --右外联接 select * from students as s right outer join class as c on s.s_classId=c.c_id select * from class  as c right outer join students as s on s.s_classId=c.c_id --全联接 select * from students as s  full outer join  class as c  on s.s_classId=c.c_id select * from class as c  full  outer join  students as s on s.s_classId=c.c_id

--交叉查询 SELECT * FROM  students   CROSS   JOIN   class SELECT * FROM  class   CROSS   JOIN   students

--触发器 /*-------触发器------*/ /*触发器是一种特殊的存储过程， 触发器是在对表进行插入、更新或删除操作时自动执行的存储过程 触发器通常用于强制业务规则 触发器是一种高级约束，可以定义比用CHECK 约束更为复杂的约束 可执行复杂的SQL语句（if/while/case） 可引用其它表中的列 */

/* DELETE 触发器 INSERT 触发器 UPDATE 触发器 */

/*----------Insert 插入 触发器------------*/ Create Trigger Tri_insert on students /*针对某个表，触发器是建立在表关系上的*/ for insert /*采用的是哪种触发器*/ as  declare @stu_id int;  declare @stu_score varchar(200);  select @stu_id=s_id from inserted /*这里的Inserted 是在创建触发器时候 系统自动创建的内存表*/  insert into student_Score(s_id,ss_score)values(@stu_id,‘100‘)

go --就是插一条数据进入students中，Tri_insert触发器就会自动在student_Score插入相关的学生的一条分数的数据 insert into students(s_name,s_classId) values(‘黄驰‘,6)

http://blog.csdn.net/zklxuankai/article/details/7853550

SQLServer创建触发器,古老的榕树,5-wow.com