数据库知识(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_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'



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

微信赚钱一天赚100元,点击了解
版权声明

本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。

评论

Top