触发器-----------数据库（SQL）

最新推荐文章于 2023-11-09 18:26:52 发布

Baple

最新推荐文章于 2023-11-09 18:26:52 发布

阅读量727

点赞数

分类专栏：数据库文章标签：触发器

本文链接：https://blog.csdn.net/Baple/article/details/39374271

版权

数据库专栏收录该内容

56 篇文章 0 订阅

订阅专栏

http://fatedgar.iteye.com/blog/1323998

格式：

create trigger tri_name
on table_name
[for （insert/update/delete）]
/[instead of （insert/update/delete）]
as
statement

create trigger 名称
on 表名for (insert update delete 其中之一)
as
语句

一

Sql代码

alter trigger tri_insert
on course for insert
as
declare @name nchar(5)
select @name=coursename from inserted
print '名称'+@name+'已被加入表！'
insert course
values ('007','计算机英语','004')

二

Sql代码

create trigger tri_delname
on course for delete
as
declare @name nchar(10)
select @name=coursename from deleted
print '名称'+@name+'已被删除出表！'
delete from course
where course_id='006'

三

Sql代码

create trigger tri_upname
on course for update
as
declare @oldname nchar(10)
declare @newname nchar(10)
select @oldname=coursename from deleted
select @newname=coursename from inserted
print '名称'+@oldname+'已被更新为'+@newname
update course
set coursename='计算机管理'
where course_id='007'

四

Sql代码

alter trigger tri_inname
on course instead of insert
as
print '名称'+@name+'未被加入表！'
insert into course
values('006','信息管理','002')

五

Sql代码

create trigger tri_upsc
on sc for update
as
declare @sid nchar(10)
declare @cid nchar(10)
declare @oldscore int
declare @newscore int
select @sid=student_id,@cid=course_id,@oldscore=score
from deleted
select @newscore=score from inserted
print '在'+convert(nvarchar(30),getdate())+
@sid+'的'+@cid+'课程成绩由'+
convert(nvarchar(3),@oldscore)+
'改变为'+ convert(nvarchar(3),@newscore)
update sc
set score=60
where student_id='002' and course_id='003'

六

Sql代码

create trigger tri_insname
on teacher for insert
as
declare @name nchar(10)
select @name=tname from inserted
print '教师'+@name+'插入到教师表中！'

七

Sql代码

create trigger tri_delname
on teacher for delete
as
declare @name nchar(10)
select @name=tname from deleted
print '教师'+@name+'从教师表中删除了！'

八

Sql代码

create trigger tri_upname
on teacher for update
as
declare @oldname nchar(10),@newname nchar(10)
select @oldname=tname from deleted
select @newname=tname from inserted
print '教师'+@oldname+'改名为'+@newname

九

Sql代码

create trigger tri_upsc
on sc for update
as
declare @sid nchar(10)
declare @cid nchar(10)
declare @oldscore int
declare @newscore int
select @sid=sid,@cid=cid,@oldscore=score
from deleted
select @newscore=score from inserted
print '在'+convert(nvarchar(30),getdate())+
@sid+'的'+@cid+'课程成绩由'+
convert(nvarchar(3),@oldscore)+
'改变为'+ convert(nvarchar(3),@newscore)

十

Sql代码

create trigger tri_inssc
on sc for insert
as
declare @sid nchar(10)
declare @cid nchar(10)
declare @s int
select @sid=sid,@cid=cid,@s=score
from inserted
print @sid+'的'+@cid+'课程成绩'+convert(nvarchar(3),@s)
+'插入到成绩表中'
insert into sc
values ('004','004',100)
update sc
set score=60
where sid='002' and cid='003'
update teacher
set tname='叶问'
where tid='004'
insert into teacher
values ('005','李云松')

十一

Sql代码

create trigger tri_inscourse
on course for insert
as
declare @name nchar(10)
select @name=cname from inserted
print '名称为：'+@name+'的课程插入到课程表中！'
insert into course
values('006','数据库程序设计','001')

十二

Sql代码

create trigger tri_upcname
on course for update
as
declare @oldname nchar(10)
declare @newname nchar(10)
select @oldname=cname from deleted
select @newname=cname from inserted
print '课程名：'+@oldname+'改名为'+@newname
update course
set cname='数据库管理'
where cid='006'

十三

Sql代码

create trigger tri_delname1
on course for delete
as
declare @name nchar(10)
select @name=cname from deleted
print '课程'+@name+'删除了！'
delete from course
where cid='006'

十四

Sql代码

create trigger tri_insofname
on course instead of insert
as
print '当前用户无权插入数据！'
insert into course
values('006','数据库管理','001')

十五

Sql代码

create trigger tri_insofname1
on course instead of update
as
print '当前用户无权更新数据！'
update course
set cname='专业英语'
where cid='005'

Sql代码

create trigger tri_insertname
on student for insert
as
declare @name nchar(10)
select @name=sname from inserted
print '学生:'+@name+'插入到学生表中！'
--执行
insert into .....
create trigger tri_deletename
on student for delete
as
declare @name nchar(10)
select @name=sname from deleted
print '学生:'+@name+'从学生表中删除了！'
--执行
delete from student
where ....
create trigger tri_updatename
on student for update
as
declare @oldname nchar(10)
declare @newname nchar(10)
select @oldname=sname from deleted
select @newname=sname from inserted
print '学生：'+@oldname+ '更改为'+@newname
--执行
update student
set sname='' --新名
where sname='' --旧名
create trigger insof_update
on student instead of update
as
declare @sex nchar(10),@sid nchar(10)
select @sex=ssex from inserted
select @sid=sid from inserted
if @sex not in ('男','女')
print '插入值错误，请重做！'
else
update student
set ssex='男'
where sid=@sid
--执行
update student
set ssex='va'
where sid=''
update student
set ssex='男'
where sid=''

Baple

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
触发器-----------数据库（SQL）

格式：create trigger tri_nameon table_name[for （insert/update/delete）]/[instead of （insert/update/delete）]as statementcreate trigger 名称on 表名for (insert update delete 其中之一)as
复制链接

扫一扫

专栏目录