SQL Server触发器的创建

来源：互联网发布：ubuntu 14.04 双系统编辑：程序博客网时间：2024/05/16 03:44

参考http://blog.csdn.net/fredrickhu/article/details/4708906一

（一）先打开sql server数据库，创建数据库Stu ，创建两个表：

create database Stu

create table Student(StudentID int primary key,StudentName nvarchar(50),StudentAge int)create table BorrowRecord(BorrowRecord int identity(1,1),StudentID int,BorrowDate Datetime,ReturnDate Datetime)

注意这里不能添加外键，添加外键后有约束，就不能激发触发器了。下面的外键操作供自己复习：

--添加外键alter table BorrowRecordadd constraint fk_StudentID foreign key(StudentID) references Student(StudentID)--删除外键alter table BorrowRecord drop constraint fk_StudentID

向表 Student和表BorrowRecord插入数据：

insert into Student values(1,'王五',20)insert into Student values(2,'王六',21)insert into Student values(3,'王七',22)insert into Student values(1888,'古城',50)

insert into BorrowRecord values(1,'2014-05-1','2014-05-6')insert into BorrowRecord values(4,'2014-05-1','2014-05-10')insert into BorrowRecord values(3,'2014-05-1','2014-05-20')insert into BorrowRecord values(1888,'2014-06-1','2014-09-1')

select * from Studentselect * from BorrowRecord

如下图：

--如果改了学生的学号，我希望他的借书记录仍然与这个学生相关--也就是改了Student表StudentID，也同时修改 BorrowRecord表的StudentID--创建触发器create trigger triUpdStudentOn Studentfor updateasif update(StudentID)beginupdate BorrowRecordset StudentID=i.StudentIDFrom BorrowRecord br,Deleted d,Inserted i--Deleted Inserted是临时表where br.StudentID=d.StudentIDend--删除触发器    drop trigger triUpdStudent

update Student set StudentID=1000 where StudentID=1888;

在查看一下两个表：

同理创建一个Delete触发器：

--创建一个Delete触发器-- 如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。create trigger triDeStudenton Studentfor deleteasDelete BorrowRecordfrom BorrowRecord br,Deleted dwhere br.StudentID=d.StudentID--drop trigger triDeStudent  删除触发器语句delete from Student where StudentID=1;

看一下结果，两个表StudentID=1的学生被删除了。

0 0