1 --1.) 创建测试用的表(testTable)  2 if exists (select * from sysobjects where name=‘testTable‘)  3 drop table testTable  4 GO  5 Create Table testTable  6 (  7 testField varchar(50)  8 )  9  10  select * from testTable 11  12  13  14 --2.) 创建基于表(testTable)的触发器(testTrigger) 15 IF EXISTS (Select name FROM sysobjects Where name = ‘testTrigger‘ AND type = ‘TR‘) 16 Drop TRIGGER testTrigger 17 GO 18 Create Trigger testTrigger  19 ON testTable  20 for Insert,Delete,Update 21 AS 22 if exists(select * from inserted) 23      if exists(select * from deleted) 24          print ‘...更新‘ 25      else 26          print ‘...插入‘ 27 else 28      if exists(select * from deleted) 29          print ‘...删除‘ 30 Go 31  32  33 --.) 操作testTable表,测试触发器testTrigger 34 --分别执行Insert Into语句,Update语句,Delete语句,看看效果 35 Insert Into testTable values (‘testContent!‘) 36  37 Update testTable Set testField = ‘UpdateContent‘ 38  39 Delete From testTable 40  41 select * from testTable 42  43  44 --用到的功能有:  45         --1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号);  46         --2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录，等等。 47  48  --这时候可以用到触发器。对于需求1，创建一个Update触发器：  49  50      Create Trigger truStudent  51  52        On student      --在Student表中创建触发器  53        instead of Update      --为什么事件触发  54      As           --事件触发后所要做的事情  55        if Update(stuID)             56        begin  57  58          Update borrow  59            Set stuID=i.stuID  60            From borrow as br , Deleted as d ,Inserted as i      --Deleted和Inserted临时表  61            Where br.stuID=d.stuID  62  63        end    64  65 drop trigger truStudent 66 UPDATE student set stuID=‘1006‘ WHERE stuID=‘1005‘ 67  68  69     Create trigger trdStudent 70        On Student  71        instead of Delete  72      As  73      Delete Borrow  74 　　　From Borrow as br , Deleted as d Where br.StuID=d.stuID  75  76 drop trigger trdStudent 77 delete FROM student WHERE stuID=‘1004‘ 78  79 disable trigger trdStudent on Student 80  81 select * from book 82 select * from borrow 83 select * from student 84  85  86 --创建触发器（对删除表的约束） 87 create trigger droptabel 88 on database 89 for drop_table 90 as 91 print‘删除表吗？‘ 92 print‘不能删除表‘ 93 rollback transaction 94 go 95  96 drop table students 97 disable trigger droptabel on database　　　　--关闭触发器 98 enable trigger droptabel on database 99 100 101 102 103 104 CREATE TABLE students  --学生信息表105 (106   stuID CHAR(10) primary key,  --学生编号107   stuName  CHAR(10) NOT NULL ,     --学生名称108   major  CHAR(50) NOT NULL    --专业109 )110 GO111 112 113 CREATE TABLE borrowS  --借书表114 (115  borrowID  CHAR(10) primary key,    --借书编号116 stuID CHAR(10) NOT NULL, --学生编号117 BID  CHAR(10) NOT NULL,--图书编号118  T_time  datetime NOT NULL,   --借出日期119  B_time  datetime    --归还日期120 )121 GO122 123 SElect * from students124 select * from borrows125 126 Create Trigger truStudents 127 128        On students      --在Student表中创建触发器 129        for Update      --为什么事件触发 130      As           --事件触发后所要做的事情 131        if Update(stuID)            132        begin 133 134          Update borrows 135            Set stuID=i.stuID 136            From borrows as br , Deleted as d ,Inserted as i      --Deleted和Inserted临时表 137            Where br.stuID=d.stuID 138 139        end   140 141 142 143 UPDATE studentS set stuID=‘1006‘ WHERE stuID=‘1005‘144 145 146     Create trigger trStudent147        On Students 148        for Delete 149      As 150        Delete borrows 151          From borrows AS br , Deleted AS d 152          Where br.stuID=d.stuID 153 154 155 drop trigger trStudent156 157 158 delete from students where stuID=‘1001‘