编写了一个触发器有错误请赐教？
0
悬赏园豆：10
[已解决问题]
解决于 2012-06-07 10:18
写了一个触发器如下：
ALTER TRIGGER [dbo].[UpdateOrInsert]
ON [dbo].[GPS_T_CARNOWPOS]
AFTER INSERT,UPDATE
AS
BEGIN
declare @sql nvarchar(max)
declare @MSG_MSISDN varchar(20),@MSG_LONG float,@MSG_LAT float,@MSG_ORIANT float,@MSG_TIME datetime,@MSG_GPSTIME datetime,@MSG_SPEED float,@MSG_State varchar(50),@MSG_AccState varchar(50);
declare @TableName varchar(20),@Carid varchar(20),@DepID int;
select @Carid=MSG_MSISDN FROM inserted;
select @DepID=Department.ID, @TableName=DepTable from dbo.Department inner join dbo.T_Card on Department.ID= T_Card.DepID where T_Card.CardSN=@Carid;
select @MSG_MSISDN=MSG_MSISDN,@MSG_LONG=MSG_LONG,@MSG_LAT=MSG_LAT,@MSG_ORIANT=MSG_ORIANT,@MSG_SPEED=MSG_SPEED,@MSG_GPSTIME=MSG_GPSTIME,@MSG_TIME=MSG_TIME,@MSG_State=MSG_State,@MSG_AccState=MSG_AccState from inserted;
SELECT @sql='insert into'+@TableName+'(MSISDN,LONG,LAT,ORIANT,SPEED,GPSTIME,MSG_TIME,MSG_STATE,ACCSTATE,DepID)values(@MSG_MSISDN,@MSG_LONG,@MSG_LAT,@MSG_ORIANT,@MSG_SPEED,@MSG_GPSTIME,@MSG_TIME,@MSG_State,@MSG_AccState,@DepID)';
exec sp_executesql @SQL
END
当执行如下语句触发，触发器提示错误，请问是怎么回事啊？？
update dbo.GPS_T_CARNOWPOS set MSG_LONG='5.111111',MSG_LAT='2.222222' WHERE MSG_MSISDN='12345678'
提示如下错误：
消息 137，级别 15，状态 2，第 1 行
必须声明标量变量 "@MSG_MSISDN"。
最佳答案
0
exec sp_executesql @SQL 这句在执行时找不到@MSG_MSISDN，你需要给sp_executesql指定参数。
看下面的随笔：
http://www.cnblogs.com/xbf321/archive/2008/11/02/1325067.html
收获园豆：10
其他回答(1)
0
一下语句执行后怎么没有插入数据
ALTER TRIGGER [dbo].[UpdateOrInsert]
ON [dbo].[GPS_T_CARNOWPOS]
FOR INSERT,UPDATE
AS
BEGIN
declare @sql nvarchar(max)
declare @MSG_MSISDN varchar(20),@MSG_LONG float,@MSG_LAT float,@MSG_ORIANT float,@MSG_TIME datetime,@MSG_GPSTIME datetime,@MSG_SPEED float,@MSG_State varchar(50),@MSG_AccState varchar(50);
declare @TableName varchar(20),@Carid varchar(20),@DepID int;
select @Carid=MSG_MSISDN FROM inserted;
select @DepID=Department.ID, @TableName=DepTable from dbo.Department inner join dbo.T_Card on Department.ID= T_Card.DepID where T_Card.CardSN=@Carid;
select @MSG_MSISDN=MSG_MSISDN,@MSG_LONG=MSG_LONG,@MSG_LAT=MSG_LAT,@MSG_ORIANT=MSG_ORIANT,@MSG_SPEED=MSG_SPEED,@MSG_GPSTIME=MSG_GPSTIME,@MSG_TIME=MSG_TIME,@MSG_State=MSG_State,@MSG_AccState=MSG_AccState from inserted;
SET @sql='insert into '+QUOTENAME(@TableName) +'(MSISDN,LONG,LAT,ORIANT,SPEED,GPSTIME,MSG_TIME,MSG_STATE,ACCSTATE,DepID)values('+@MSG_MSISDN+',+@MSG_LONG+,+@MSG_LAT+,+@MSG_ORIANT+,+@MSG_SPEED+,+@MSG_GPSTIME+,+@MSG_TIME+,'+@MSG_State+','+@MSG_AccState+',+@DepID+)';
exec sp_executesql @sql
-- exec('insert into '+@TableName+'(MSISDN,LONG,LAT,ORIANT,SPEED,GPSTIME,MSG_TIME,MSG_STATE,ACCSTATE,DepID)values('+@MSG_MSISDN+',+@MSG_LONG+,+@MSG_LAT+,+@MSG_ORIANT+,+@MSG_SPEED+,+@MSG_GPSTIME+,+@MSG_TIME+,'+@MSG_State+','+@MSG_AccState+',+@DepID+)')
----
---- exec sp_executesql @sql,@params=N'@MSG_MSISDN1 varchar(20),@MSG_LONG1 float,@MSG_LAT1 float,@MSG_ORIANT1 float,@MSG_SPEED1 float,@MSG_GPSTIME1 datetime,@MSG_TIME1 datetime,@MSG_State1 varchar(50),@MSG_AccState1 varchar(50),@DepID1 int' ,
--
-- EXEC(@SQL)
END
