触发器是一种特殊的存储过程。单从使用上来区别的话,触发器不用exec来触发,而是通过一些增删改等操作来触发。
存储过程:
if (object_id('proc_getStudentRecord', 'P') is not null) --判断存储过程是否存在
drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
@id int, --默认输入参数
@name varchar(20) out, --输出参数
@age varchar(20) output--输入输出参数
)
as
select @name = name, @age = age from student where id = @id and sex = @age;
go
-- declare 局部定义变量
declare @id int,
@name varchar(20),
@temp varchar(20);
set @id = 7;
set @temp = 1;
--运行存储过程
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;
触发器:
if (object_id('tgr_classes_inteadOf', 'TR') is not null) --判断触发器是否存在
drop trigger tgr_classes_inteadOf
go
create trigger tgr_classes_inteadOf
on classes
instead of delete/*, update, insert*/ --for表示之后触发,instead of表示之前触发
as
declare @id int, @name varchar(20);
--查询被删除的信息,并赋值
select @id = id, @name = name from deleted;
print 'id: ' + convert(varchar, @id) + ', name: ' + @name;
--先删除student的信息
delete student where cid = @id;
--再删除classes的信息
delete classes where id = @id;
print '删除[ id: ' + convert(varchar, @id) + ', name: ' + @name + ' ] 的信息成功!';
go
--test
select * from student order by id;
select * from classes;
delete classes where id = 7;
各列一个例子来理解存储过程和和触发器,如果项目中确实有更深的需求,当然还是去详细学一下比较好,当然大多数情况下是和游标等一些知识点结合使用的