Question

SQL SERVER 2008 触发器中怎么将参数传递给存储过程中的输入参数@sno

存储过程如下：
create procedure add_record @sno char(9)
as
declare @total_num int,@cno char(4)
SET NOCOUNT on;
select @total_num= COUNT(*) from Student where Sno=@sno
if @total_num=0
begin
print'该学生不存在！'
return
end
else
begin
declare kecheng_cursor CURSOR FOR select cno from course
open kecheng_cousor
fetch next from kecheng_cursor into @cno
while @@FETCH_STATUS=0
begin
select @total_num=COUNT(*) from SC where Sno=@sno and Cno=@cno
if @total_num>0
begin
FETCH NEXT FROM kecheng_cousor into @cno
continue
end
else
begin
insert into SC(Sno,cno,grade)values(@sno,@cno,100*RAND())
fetch next from kecheng_cousor into @cno
end
close kecheng_cousor
deallocate kechang_cousor
end
end
GO
已有表student
要实现向student表中插入一条记录时触发存储过程，怎么获得插入的记录的sno传递给存储过程中@sno

Answer 1

你可以在触发器上调用存储过程，
触发器中有两个临时表inserted 和deleted ,这两个表的表结构和创建触发器的表是一样的，
inserted表存的是当前插入或更新後的值,
deleted表存的是当前删除或修改前的值.
你可以利用以上两个临时表的值来获得你要的参数值，然後调用时传入存储过程
另外你也可以直接在触发器中操作，不用存储过程。

Answer 2

建立下面的触发器：
CREATE TRIGGER [TRIG_student] ON [dbo].[student]
FOR INSERT
AS
begin

declare @sno char(9)
set @sno=(select sno from inserted)
exec add_record @sno
end

Answer 3

如下，sql server2008环境

USE [dm01]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[insert_student]
ON [dbo].[student]
AFTER INSERT
AS
BEGIN

SET NOCOUNT ON;
declare @sno integer
if exists(select 1 from inserted)
begin
select @sno = t.sno from inserted t
exec add_record @sno
end;

END