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

 

create trigger tri_name

on table_name

[for （ insert/update/delete ） ]

/ [instead of （ insert/update/delete ） ]

 as

    语句

 

一

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' )

 

二

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'

 

三

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'

 

四

alter trigger tri_inname

on course instead of insert

as

print ' 名称 ' + @name+' 未被加入表！ '

 

insert into course

values ( '006' , ' 信息管理 ' , '002' )

 

五

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'

六

 

create trigger tri_insname

on teacher for insert

as

 declare @name nchar(10)

 select @name=tname from inserted

 print ' 教师 ' + @name+' 插入到教师表中！ '

七

create trigger tri_delname

on teacher for delete

as

   declare @name nchar(10)

 select @name=tname from deleted

 print ' 教师 ' + @name+' 从教师表中删除了！ '

八

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

 

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)

 

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',' 李云松 ' )

 

 

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' )

 

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'

 

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'

 

create trigger tri_insofname

on course instead of insert

 as

   print ' 当前用户无权插入数据！ '

 

insert into course

values ( '006' , ' 数据库管理 ' , '001' )

 

create trigger tri_insofname1

on course instead of update

 as

   print ' 当前用户无权更新数据！ '

 

update course

set cname=' 专业英语 '

where cid='005'

 

drop trigger ...

create trigger 名称

 on 表名 for (insert update delete 其中之一 )

as

   语句

 

 

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=''