觸發器的概念：它是由事件驅動的，就像java中的監聽，當某個事件發生了，就會做一些工作。

下面直接上干貨，創建insert觸發器、delete觸發器、DDL觸發器和如何查看觸發器定義

1.創建三個表學生表、班級表、課程表

--在數據庫中創建三個表學生表、班級表、課程表的結構create table student ( stu_id char(8) primary key,stu_name char(10),stu_sex char(2),stu_birthday smalldatetime,class_id char(6) ) go create table class ( class_id char(6) primary key,class_name varchar(30), class_num int, ) create table course ( course_id char(3) primary key,course_name varchar(30), ) go create table score ( stu_id char(8),course_id char(3),score int check(score>=0 and score<=100)primary key(stu_id,course_id) ) go View Code

2.插入數據

--往表中插入數據(student,course,score)insert into student values('0601001','李玉','女','1987-05-06', '0601')insert into student values('0601002','魯敏','女','1988-06-28', '0601')insert into student values('0601003','李小路','女','1987-01-08', '0601')insert into student values('0601004','魯斌','男','1988-04-21', '0601')insert into student values('0601005','王寧靜','女','1986-05-29', '0601')insert into student values('0601006','張明明','男','1987-02-24', '0601')insert into student values('0601007','劉曉玲','女','1988-12-21', '0601')insert into student values('0601008','周曉','男','1986-04-27', '0601')insert into student values('0601009','易國梁','男','1985-11-26', '0601')insert into student values('0601010','季風','男','1986-09-21', '0601')insert into class values('0501','計算機辦公應用', 40) insert into class values('0502','網絡構建', 43) insert into class values('0503','圖形圖像', 48) insert into class values('0601','可視化', 41) insert into class values('0602','數據庫', 38) insert into class values('0603','網絡管理', 45) insert into class values('0604','多媒體', 40) insert into class values('0701','計算機辦公應用', 39) insert into class values('0702','WEB應用', 38) insert into class values('0703','網絡構建', 40)insert into course values('001','計算機應用基礎') insert into course values('002','關系數據基礎') insert into course values('003','程序設計基礎') insert into course values('004','數據結構') insert into course values('005','網頁設計') insert into course values('006','網站設計') insert into course values('007','SQL Server 2000關系數據庫') insert into course values('008','SQL Server 2000程序設計') insert into course values('009','計算機網絡') insert into course values('010','Windows Server 配置')insert into score values('0601001','001',78) insert into score values('0601002','001',88) insert into score values('0601003','001',65) insert into score values('0601004','001',76) insert into score values('0601005','001',56) insert into score values('0601006','001',87) insert into score values('0601007','001',67) insert into score values('0601008','001',95) insert into score values('0601009','001',98) insert into score values('0601010','001',45)insert into score values('0601001','002',48) insert into score values('0601002','002',68) insert into score values('0601003','002',95) insert into score values('0601004','002',86) insert into score values('0601005','002',76) insert into score values('0601006','002',57) insert into score values('0601007','002',77) insert into score values('0601008','002',85) insert into score values('0601009','002',98) insert into score values('0601010','002',75)insert into score values('0601001','003',88) insert into score values('0601002','003',78) insert into score values('0601003','003',65) insert into score values('0601004','003',56) insert into score values('0601005','003',96) insert into score values('0601006','003',87) insert into score values('0601007','003',77) insert into score values('0601008','003',65) insert into score values('0601009','003',98) insert into score values('0601010','003',75)insert into score values('0601001','004',74) insert into score values('0601002','004',68) insert into score values('0601003','004',95) insert into score values('0601004','004',86) insert into score values('0601005','004',76) insert into score values('0601006','004',67) insert into score values('0601007','004',77) insert into score values('0601008','004',85) insert into score values('0601009','004',98) insert into score values('0601010','004',75)insert into score values('0601001','005',74) insert into score values('0601002','005',68) insert into score values('0601005','005',76) insert into score values('0601008','005',85) insert into score values('0601009','005',98) insert into score values('0601010','005',75)insert into score values('0601002','006',88) insert into score values('0601003','006',95) insert into score values('0601006','006',77) insert into score values('0601008','006',85) insert into score values('0601010','006',55)insert into score values('0601001','007',84) insert into score values('0601002','007',68) insert into score values('0601003','007',95)insert into score values('0601004','008',86) insert into score values('0601005','008',76) insert into score values('0601006','008',67)insert into score values('0601007','009',67) insert into score values('0601008','009',85)insert into score values('0601009','010',98) insert into score values('0601010','010',75) View Code

3.觸發器實例

--1）在student上創建<strong>INSERT觸發器</strong>stu_insert，要求在student表中插入記錄時(要求每次只能插入一條記錄)，這個觸發器都將更新class表中的class_nun列。并測試觸發器stu_insert。 create trigger stu_insert on student for insert as update class set class_num=class_num + 1 where class_id=(select class_id from inserted)select * from class --測試 insert into student values('0602011','文','女','1986-09-21', '0602')select * from class--2）在student上創建<strong>DELETE觸發器</strong>stu_delete，要求在student表中刪除記錄時，這個觸發器都將更新class表中的class_nun列。并測試觸發器stu_delete。 create trigger stu_delete on student for delete as update class set class_num=class_num - 1 where class_id = (select class_id from deleted)--測試 delete from student where stu_id='0601001'--3）查看觸發器相關信息:使用系統存儲過程<strong>sp_help，sp_helptext查看觸發器</strong>相關信息。 exec sp_help exec sp_help stu_insert exec sp_helptext stu_insert--4）對于下列觸發器： create trigger stu_update on student instead of update as print '修改學生表' drop trigger stu_update --執行語句 update student set stu_id='0601003' where stu_name='魯斌' --會怎么樣？ --消息 2627，級別 14，狀態 1，第 1 行 --違反了 PRIMARY KEY 約束 'PK__student__E53CAB217F60ED59'。不能在對象 'dbo.student' 中插入重復鍵。 --語句已終止。--5）創建<strong>DDL觸發器</strong>，在當前數據庫中不允許刪除或修改表 create trigger data on database for drop_table, alter_table as print '不允許刪除或修改表' rollback View Code

?

不要看他人高薪；且看閑時誰在拼

轉載于:https://www.cnblogs.com/BoKeYuan259/p/10911517.html

總結

以上是生活随笔為你收集整理的sqlserver的触发器练习实例的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。