触发器-数据库（SQL）

触发器-----------数据库（SQL）
格式：

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
   语句

一

alter trigger tri_inserton course for insertasdeclare @name nchar(5)select @name=coursename from insertedprint '名称'+@name+'已被加入表！' insert coursevalues ('007','计算机英语','004')

create trigger tri_delnameon course for deleteasdeclare @name nchar(10)select @name=coursename from deletedprint '名称'+@name+'已被删除出表！' delete from coursewhere course_id='006'

create trigger tri_upnameon course for updateasdeclare @oldname nchar(10)declare @newname nchar(10)select @oldname=coursename from deletedselect @newname=coursename from insertedprint '名称'+@oldname+'已被更新为'+@newname update courseset coursename='计算机管理'where course_id='007' 

alter trigger tri_innameon course instead of insertasprint '名称'+@name+'未被加入表！' insert into coursevalues('006','信息管理','002')

create trigger tri_upscon 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 insertedprint '在'+convert(nvarchar(30),getdate())+      @sid+'的'+@cid+'课程成绩由'+       convert(nvarchar(3),@oldscore)+      '改变为'+ convert(nvarchar(3),@newscore) update scset score=60where student_id='002' and course_id='003'

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

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

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

create trigger tri_upscon 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 insertedprint '在'+convert(nvarchar(30),getdate())+      @sid+'的'+@cid+'课程成绩由'+       convert(nvarchar(3),@oldscore)+      '改变为'+ convert(nvarchar(3),@newscore)

create trigger tri_insscon sc for insertas   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 scvalues ('004','004',100)update scset score=60where sid='002' and cid='003' update teacherset tname='叶问'where tid='004' insert into teachervalues ('005','李云松')

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

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

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

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

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

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_updatenameon student for updateas    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_updateon student instead of updateas   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=''