SQL语句3——触发器

--触发器的建立和使用
--创建触发器 并 起触发器名字stri_stu
create trigger stri_stu
--on 加表名 对那个表添加触发器（如：on stu 对表stu创建触发器）
--after 加（增删改 insert  delete update ）
--as  是触发器要执行的操作
--begin 
--declare 声明变量（ 如：declare @temp int    声明int型变量@temp）
--sql语句
--end 结束

-------------------------------触发器（删除操作）
create trigger tri_stu
on stu
after delete
as
 begin
 declare @temp int
 select @temp = id from deleted
 delete from course where id = @temp
 /*if(delete  from stu  where id='1')
 then(delete  from  course  where id='1')*/
 end
select * from stu

select * from course
delete from stu where id = 1

-------------------------------触发器（插入操作）

--drop trigger tri_student
create trigger tri_student
on stu
after insert
as
 begin
 declare @temp int
 select @temp = id from inserted
 insert into course (id,cno) values (@temp,'003')  --where id = @temp
 /*if(delete  from stu  where id='1')
 then(delete  from  course  where id='1')*/
 end

select * from stu
select * from course

delete from stu where id = 1
insert into stu (id,name) values (1,'qwe')--where id =1

-------------------------------触发器（修改操作） 修改操作先删后插
--drop trigger student
create trigger student
on stu
after update
as
begin
 declare @temp int               --声明int变量
 declare @temp2 int              --声明int变量 

select @temp=id from deleted     --修改前数据
select @temp2=id from inserted   --修改后数据

 update course set id=@temp2 where id = @temp --修改
end

update stu set id=10 where id=1
-----------------------------------查询数据库中已经有的触发器
use tt
go 
select * from  sysobjects where xtype='TR'
-----------------------------------查询单个触发器
exec sp_helptext '触发器名' 
exec sp_helptext 'student' 


-----------------------------------删除触发器
drop trigger 触发器名字