SQL Server触发器

时间：2010-08-31 来源：蔚蓝的大海

何为触发器：触发器是数据库服务器中发生事件时自动执行的特种存储过程，这是帮助文档上的定义，就认为是一种特殊的存储过程也行，经常用于强制执行业务规则和数据完整性。

在正式操作之前先把触发器原理给说说，理解好触发器的机制是对更好的运用触发器自己好处很明显。

1、原理：

　　在执行触发器的过程中会产生两张临时表INSERTED 和DELETED，这两张表的作用简单说就是拿来记录数据的，主我们可以在触发器执行过程中更好的对数据进行操作

　　对表执行INSERT和UPDATE操作时都会在临时表INSERTED中拷贝一份所增加的数据。

　　对表执行DELETE和UPDATE操作时都会在临时表DELETED中拷贝一份所删除的数据

　对表执行UPDATE操作时，首先把UPDATE前的数据DELETE到DELETED临时表中，然后再把所要更新的数据插入表中，最后把更新后的数据拷贝到INSERTED临时表中

触发器分为两种AFTER和INSTEAD OF

2、AFTER

　　字面意思就是在对表执行INSERT、UPDATE、DELETE操作后触发的触发器了，这里以INSERT结合触发器原理简单描述其过程

　　当有INSERT语句要执行时，首先直接执行INSERT语句，再就是AFTER执行触发器里面的操作（把数据拷贝到临时表INSERTED中，然后进行我们想要的操作，最后完成同样删除了临时　　　　　　　　　　　　　　　　　　　　表）

　　下面就开始进行实例演示了：

--学生
create table Student
(
StudentNo int primary key,
StudentName varchar(20) not null
)
--书本
create table Book
(
BookId int identity(1,1),
BookName varchar(30),
Owner int foreign key references Student(StudentNo)
)
insert into Student values(1,'ShepherlDeng')
insert into Student values(2,'Divi')
insert into Student values(3,'Lili')
insert into Book values('Book--01',1)
insert into Book values('Book--02',2)
insert into Book values('Book--01',3)
insert into Book values('Book--02',1)
insert into Book values('Book--03',2)
insert into Book values('Book--03',3)
insert into Book values('Book--04',1)
insert into Book values('Book--04',2)
insert into Book values('Book--01',3)

如果我们有这样一个业务需求，就是当有一个学生时，必须买Book--01这一本书

于是我们可以写个简单的AFTER触发器

CREATE TRIGGER itStudent
on Student
after Insert
as
begin
declare @studentNo int
select @studentNo=StudentNo from Inserted
insert into Book values('Book--01',@studentNo)
end

　当我们增加一条记录时就会自动在Book表中增加一条记录,这里其它操作也很相像所以不多说了只要知道是在操作守后再执行，重点放在INSTEAD OF触发器上。

3、INSTEAD OF

　　字面意思为取代,难道说当在一张表上定义了这样的触发器后，对表所做的INSERT、UPDATE、DELETE操作会被替换掉而不执行了？呵呵…这种触发器执行过程为：

　　当对表执行INSERT等操作时，并不直接执行这些操作而是转到触发器里面来执行触发器所定义的操作语句(应该说是一起执行的更合适)；

　　演示仍然为上面的所创建的表首先我们演示一个级联删除，当我们在Student表中删除一个被Book引用记录时因为有完整无缺约束我们无法删除这是INSTEAD OF就有作用了　

CREATE TRIGGER dtStudent
on Student
instead of Delete
as
begin
declare @studentNo int
select @studentNo=StudentNo from Deleted
delete Book where Owner=@studentNo
end

呵呵这是不是很爽呢……

　那我们再来演示一个UPDATE的INSTEAD OF触发器的操作

Create Trigger utStudent
on Student
instead of Update
as
begin
declare @studentNo int,
@studentNold int
if update(StudentNo)
begin
select @studentNold=StudentNo from Deleted
select @studentNo=StudentNo from Inserted
update Book set Owner=@studentNo where Owner=@studentNold
end
end

这会是什么结果呢？执行后就会发现当你更改StudentNo是它的子表Book中的Owner也会一起改变了…

我还是觉得触发器能解决的好像存储过程也可以，以前也用存储过程写级联删除感觉要比这个要繁琐点，还有人说触发器是高手用的，总之各有各的应用场景了看怎么用了。

USB A/F short body DIP 90° dual port (bend angle without bulge L=17.5mm) connector socket adapterHeart Shaped 1Gb USB 2.0 Flash Memory DriveMini 5P B 5M altrathin four-piece USB connector OEM adapter Mini USB plug connectorMini 8P Female SMT USB connector cellphone connector mini usb port socket adapterUSB 2.0 + Firewire 1394 2.5" IDE Hard Drive EnclosureLG 18P main body connector cellphone connector LG mobile phone connector3.5 DC head adapter connector stable performance3.5 IDE to 2.5 IDE adapter fashion design suitable for laptop transmission card2Gb USB 2.0 Flash Storage Drive512Mb USB 2.0 Flash Memory Drive15+7 22 Pin P Serial ATA Power Data Cable for SATA HDDUniversal laptop AC/DC adapter 100W Auto Car home AC DC power adapter charger laptop 761-100W.DGL7 Ports Cable USB 2.0 Hub for PC NotebookPCI-E card with parallel ATA interface PCI cardUSB A/F 90° DIP buckle outside connector RoHS compliant OEM adapter connectorNice Rotatable Wooden USB 2.0 Flash Memory DriveSwitching power supply adapter card Bi-directional high speed transmission cardUSB Female to 1394 Male adapter connector with long life time OEM fast speedBeautiful Color Fish Like 7 Ports USB 2.0 Hub for PC Laptop8DBi Radar direction antenna wireless lan card detachable antenna DSSS spread spectrumSilver and Black Vertical Type 4 Ports 7 Ports USB 2.O Hub for PC LaptopsUltra-thin power supply 90W chager adapter with 8 renewable DC plugs 124*56*45 mmUSB3.0 F 90° DIP connector RoHS compliant OEM adapter connectorPCI 54M WLAN wireless lan card external WPS button widely coverage area3.5 DC to AU cellphone charging adapter connector stable performanceSUPER VGA Male to Male MONITOR/LCD/PROJECTOR CABLE WhiteUSB A/M to IPOD,LG,DP,NIMI8P,USB B/M adapter connector stable performance2.5 DC to 2.5 adapter with long life time connector OEM standardized sizeMini 5P AB/F SMT USB connector cellphone connector mini usb adapter mini usb portBMW Shaped Rotatable 4 Ports USB Hub for Your PC LaptopSUPER VGA Male to Female MONITOR/LCD/PROJECTOR CABLE WhiteUSB A/F 90° DIP short body connector (right angle L=13.75mm) RoHS compliant OEM adapter connectorUSB A Male to Male D 6P M + DC 3.5 Adapter Cable for piano, electronic organ25 Pin DB25 Male to Female Extension Cable for Printer WhiteNew 90W chager adapter with 8 renewable DC plugs HP series 124*56*37 mmCircle Wooden 2Gb USB 2.0 Flash Storage DriveMini 5P B/F 90° DIP USB connector cellphone connector mini usb socket adapter connector4Gb USB 2.0 Flash Memory DriveIPHONE/IPOD adapter connector with long life time OEM originalSerial ATA SATA 7 Pin Female to Female Cable (Lots 20)Yellow Gold 8Gb USB 2.0 Flash Storage DriveDual-port IDE to USB adapter card high-speed fashion design transmission cardNew 90W charger with 8 renewable DC plugs ZM-50 series 124*56*40 mmKing of card wireless lan card external detachable antenna WLANUSB A/F short body DIP 90° dual port (bend angle with bulge L=17.5mm) connector socket adapterSmart cable,4-in-1 with USB transmission,card reader,USB charging,novelty hangs with LED instructionAll in one Multifunction USB Card ReaderUSB 2.0 to Parallel Printer Adapter Cable 8P8C network pass-through adapter extension connector stable performanceUSB 2.0 to DC Male Adapter Cable Black Replica lv Hangbags Replica Hangbags Hangbags replica handbags replica handbags replica handbags replica handbags usb cable usb cable usb cable usb cable usb flash drives