触发器、存储过程、函数 基本操作（一）

一、触发器

1.         创建

基本语句如下:

create trigger trigger_name

on {table_name | view_name}

{for | After | Instead of }

[ insert, update,delete ]

as

sql_statement

eg:

createtrigger TeacherInsert

on Teacher

after insert

as 

if(select count(*) from Teacher,inserted where Teacher.TCode=inserted.TCode)>1

begin 

print'The Tcode is existed'

rollback transaction

   end

 

insert into teacher (TCode,TName,TSpell)values('0004','路飞',dbo.fun_getPY('路飞'))

                    

2.         修改

基本语句如下:

alter trigger trigger_name

on {table_name | view_name}

{for | After | Instead of }

[ insert, update,delete ]

as

sql_statement

eg:

    alter trigger   TeacherInsert 

           on  Teacher

           after   insert

           as 

if(select count(*) from  Teacher,inserted where Teacher.TCode=inserted.TCode)>1

           begin 

           print'The Tcode is existed'

           print'alter trigger'

           rollback transaction

 end

3.         删除

     基本语句如下:

        drop trigger trigger_name

eg:

drop trigger TeacherInsert

4.         查看

查看数据库已有触发器

use jxcSoftware

go

select * from sysobjects where xtype='TR'

 查看单个触发器

exec sp_helptext '触发器名'