首页新闻找找看学习计划

编写了一个触发器有错误请赐教?

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"。

yxf2011的主页 yxf2011 | 初学一级 | 园豆:6
提问于:2012-04-06 17:14
< >
分享
最佳答案
0

exec sp_executesql @SQL   这句在执行时找不到@MSG_MSISDN,你需要给sp_executesql指定参数。

看下面的随笔:

http://www.cnblogs.com/xbf321/archive/2008/11/02/1325067.html

收获园豆:10
玉开 | 大侠五级 |园豆:8822 | 2012-04-06 17:27
其他回答(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

yxf2011 | 园豆:6 (初学一级) | 2012-04-07 11:46
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册