SQL觸發器實例(上)

  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 GO
111 
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 GO
122 
123 SElect * from students
124 select * from borrows
125 
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 trStudent
147        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 trStudent
156 
157 
158 delete from students where stuID='1001'