--触发器的建立和使用
--创建触发器 并 起触发器名字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 触发器名字