none
SQL SERVER 触发器问题,求教 RRS feed

  • 问题

  • 我也的触发器里有变量,所以用了连接字符串,最后exec(@sql)
    @sql语句写法没有问题,一切正常,但是现在有个问题。
    比如,我有10列,我只更新一列,其他都不管(我是直接在数据库里添加的),结果发现不起作用

    如果我把10列全部赋值,就可以了

    但是如果我不用连接字符串,直接使用insert into...这个就可以的,就算我没有赋值,也是可以的

     

    目前的情况是我的必须要用到连接字符串,因为我的数据库名称是变量

    我想了个解决办法就是用ISNULL来做,请问有没有其他办法,或者哪儿没有写对

    直接写

    insert into TEST.dbo.ImEvent(DeviceID,DeviceName) values(@DeviceID,@DeviceName)
    第二种写法,带入参数,这个时候如果@DeviceID或@DeviceName有一个我不输入值就不能插入到新表中,两个都有值是可以的
    
    set @sql = 'insert into ['+@DatabaseName+'].[dbo].[ImEvent](DeviceID,DeviceName) values('+cast(@DeviceID as varchar(18))+','''+@DeviceName+''')'
    exec(@sql)
    

    第三种写法,将NULL换为0 和空是可以的

    set @sql = 'insert into ['+@DatabaseName+'].[dbo].[ImEvent](DeviceID,DeviceName) values('+isnull(cast(@DeviceID as varchar(18)),'0')+','''+isnull(@DeviceName,' ')+''')'
    exec(@sql)
    
    
    
    

     

     

     

     

     

     

     

    2010年7月13日 12:41

答案

  • 第一种方法在字段允许NULL值的情况下,没对字段进行赋值也能直接运行插入数据;

    第二种方法其中某个字段为NULL值,整个字符串将为NULL值,因为不管什么值与NULL值拼接或计算,结果都为NULL值;

    第三种方法因为消除了NULL值,拼串后最终不会是NULL值,如果语法正常,就能正常运行。

    楼主可以把EXEC改为PRINT就可以看到EXEC将执行什么语句了。

    处理该问题方法很多,以下供一种存储过程传递参数的方法:

    CREATE PROC p_test 
    	@DatabaseName SYSNAME,
    	@DeviceID INT,
    	@DeviceName VARCHAR(100) --自己做好防恶意注入过滤
    AS
    SELECT @DeviceID=ISNULL(@DeviceID,0),@DeviceName=ISNULL(@DeviceName,'')
    EXEC(
    	'INSERT INTO ['+@DatabaseName+'].[dbo].[ImEvent](DeviceID,DeviceName) 
    		VALUES('+@DeviceID+','''+@DeviceName+''')'
    )
    GO
    
    2010年7月14日 0:23
  • USE mydb 
    GO 
    --> 生成测试数据表: [ImEvent]
    IF OBJECT_ID('[ImEvent]') > 0
      DROP TABLE [ImEvent]
    GO
    CREATE TABLE [ImEvent] ([DeviceID] [int],[DeviceName] [nvarchar](10))
    INSERT INTO [ImEvent]
    SELECT '1','a' UNION ALL
    SELECT '2','b'
    
    --SELECT * FROM [ImEvent]
    
    -->SQL查询如下:
    
    IF OBJECT_ID('[p_test]') > 0
      DROP PROC [p_test]
    GO
    CREATE PROC p_test 
      @DatabaseName SYSNAME,
      @DeviceID INT=0,
      @DeviceName VARCHAR(100)='' --自己做好防恶意注入过滤
    AS
    SELECT @DeviceID=ISNULL(@DeviceID,0),@DeviceName=ISNULL(@DeviceName,'')
    EXEC(
      'INSERT INTO ['+@DatabaseName+'].[dbo].[ImEvent](DeviceID,DeviceName) 
        VALUES('+@DeviceID+','''+@DeviceName+''')'
    )
    GO
    
    EXEC p_test mydb,3,'c'
    
    SELECT * FROM ImEvent
    /*
    DeviceID  DeviceName
    ----------- ----------
    1      a
    2      b
    3      c
    
    (3 行受影响)
    */
    
    EXEC p_test @DatabaseName=mydb,@DeviceID=4
    
    SELECT * FROM ImEvent
    /*
    DeviceID  DeviceName
    ----------- ----------
    1      a
    2      b
    3      c
    4      
    
    (4 行受影响)
    */
    EXEC p_test @DatabaseName=mydb,@DeviceName='e'
    
    SELECT * FROM ImEvent
    /*
    DeviceID  DeviceName
    ----------- ----------
    1      a
    2      b
    3      c
    4      
    0      e
    
    (5 行受影响)
    */
    
    以上是测试过程
    • 已编辑 htl258 2010年7月14日 0:39 完善
    • 已标记为答案 燕过无声 2010年7月14日 14:12
    2010年7月14日 0:34
  • 参数传递不就行了, 搞那么麻烦?

     

    DECLARE
    	@sql nvarchar(4000),	-- 注意一定要 unicode 类型
    	@DatabaseName sysname,
    
    	@DeviceID int,
    	@DeviceName nvarchar(100)
    ;
    
    set @sql = N'
    insert into ['+@DatabaseName+ N'].[dbo].[ImEvent](DeviceID,DeviceName) 
    values(@DeviceID, @DeviceName
    ';
    EXEC sp_executesql
    	@sql,
    	N'
    		@DeviceID int,
    		@DeviceName nvarchar(100)
    	',
    	@DeviceID, @DeviceName
    ;
    
    

     

    2010年7月14日 4:40

全部回复

  • Possible to post code?
    2010年7月13日 14:39
  • 贴出你的代码看看.

    2010年7月13日 16:54
  • 第一种方法在字段允许NULL值的情况下,没对字段进行赋值也能直接运行插入数据;

    第二种方法其中某个字段为NULL值,整个字符串将为NULL值,因为不管什么值与NULL值拼接或计算,结果都为NULL值;

    第三种方法因为消除了NULL值,拼串后最终不会是NULL值,如果语法正常,就能正常运行。

    楼主可以把EXEC改为PRINT就可以看到EXEC将执行什么语句了。

    处理该问题方法很多,以下供一种存储过程传递参数的方法:

    CREATE PROC p_test 
    	@DatabaseName SYSNAME,
    	@DeviceID INT,
    	@DeviceName VARCHAR(100) --自己做好防恶意注入过滤
    AS
    SELECT @DeviceID=ISNULL(@DeviceID,0),@DeviceName=ISNULL(@DeviceName,'')
    EXEC(
    	'INSERT INTO ['+@DatabaseName+'].[dbo].[ImEvent](DeviceID,DeviceName) 
    		VALUES('+@DeviceID+','''+@DeviceName+''')'
    )
    GO
    
    2010年7月14日 0:23
  • USE mydb 
    GO 
    --> 生成测试数据表: [ImEvent]
    IF OBJECT_ID('[ImEvent]') > 0
      DROP TABLE [ImEvent]
    GO
    CREATE TABLE [ImEvent] ([DeviceID] [int],[DeviceName] [nvarchar](10))
    INSERT INTO [ImEvent]
    SELECT '1','a' UNION ALL
    SELECT '2','b'
    
    --SELECT * FROM [ImEvent]
    
    -->SQL查询如下:
    
    IF OBJECT_ID('[p_test]') > 0
      DROP PROC [p_test]
    GO
    CREATE PROC p_test 
      @DatabaseName SYSNAME,
      @DeviceID INT=0,
      @DeviceName VARCHAR(100)='' --自己做好防恶意注入过滤
    AS
    SELECT @DeviceID=ISNULL(@DeviceID,0),@DeviceName=ISNULL(@DeviceName,'')
    EXEC(
      'INSERT INTO ['+@DatabaseName+'].[dbo].[ImEvent](DeviceID,DeviceName) 
        VALUES('+@DeviceID+','''+@DeviceName+''')'
    )
    GO
    
    EXEC p_test mydb,3,'c'
    
    SELECT * FROM ImEvent
    /*
    DeviceID  DeviceName
    ----------- ----------
    1      a
    2      b
    3      c
    
    (3 行受影响)
    */
    
    EXEC p_test @DatabaseName=mydb,@DeviceID=4
    
    SELECT * FROM ImEvent
    /*
    DeviceID  DeviceName
    ----------- ----------
    1      a
    2      b
    3      c
    4      
    
    (4 行受影响)
    */
    EXEC p_test @DatabaseName=mydb,@DeviceName='e'
    
    SELECT * FROM ImEvent
    /*
    DeviceID  DeviceName
    ----------- ----------
    1      a
    2      b
    3      c
    4      
    0      e
    
    (5 行受影响)
    */
    
    以上是测试过程
    • 已编辑 htl258 2010年7月14日 0:39 完善
    • 已标记为答案 燕过无声 2010年7月14日 14:12
    2010年7月14日 0:34
  • 参数传递不就行了, 搞那么麻烦?

     

    DECLARE
    	@sql nvarchar(4000),	-- 注意一定要 unicode 类型
    	@DatabaseName sysname,
    
    	@DeviceID int,
    	@DeviceName nvarchar(100)
    ;
    
    set @sql = N'
    insert into ['+@DatabaseName+ N'].[dbo].[ImEvent](DeviceID,DeviceName) 
    values(@DeviceID, @DeviceName
    ';
    EXEC sp_executesql
    	@sql,
    	N'
    		@DeviceID int,
    		@DeviceName nvarchar(100)
    	',
    	@DeviceID, @DeviceName
    ;
    
    

     

    2010年7月14日 4:40
  •  

    就算你一定要拼字符串, NULL 还是应该用 NULL 嘛, 不然不是和源不一样么?

    (顺便说一下原因, NULL加入字符串+的处理会导致整个字符串为NULL, 所以值拼入字符串, 一定要做 ISNULL处理, 参数传递不存在这个问题, 也是推荐的处理方式)

    set @sql = 'insert into ['+@DatabaseName+'].[dbo].[ImEvent](DeviceID,DeviceName) values('+isnull(cast(@DeviceID as varchar(18)),'NULL')+','+isnull(''''+@DeviceName +'''',')+')'
    exec(@sql)
    NULL '

    2010年7月14日 4:45
  • 谢谢两位的帮助,学了不少知识

    我一直不知道字符串与NULL相连,整个就变NULL了

    再次谢谢两位,非常感谢!

    2010年7月14日 14:13