Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Location
    United States
    Posts
    65
    Provided Answers: 1

    Unanswered: Why Trigger is not working

    Hi,
    here is my code for a trigger,

    --------------------------------
    CREATE TRIGGER trIns_Test
    ON tblTest
    FOR INSERT
    AS

    DECLARE @Message VARCHAR(100),
    @Num INT

    SELECT @Num = user_id from INSERTED
    SELECT @Message = 'NET SEND NICK '+' Here is the new # - ' + CONVERT(VARCHAR(100), @Num)
    EXEC master.dbo.xp_cmdshell @Message
    --------------------------------

    But when i insert a row into tblTest table i'am supposed to receive a NET SEND message. But its not happening if i hardcode the value @Num i.e

    SELECT @Num = 321

    Then i receive a message, but why this INSERTED clause does't work i'm unable to trace out.

    Version of SQL is 2K SP3

    Thanks,

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A trigger runs once for each DML statement (INSERT, UPDATE, etc), with all of the rows affected in the inserted/deleted views. Your code is written to handle only one row of data, which is probably a problem. You could kind of sneak up on what you want using:
    Code:
    CREATE TRIGGER trIns_Test
       ON tblTest FOR INSERT
    AS
    
    DECLARE @Message VARCHAR(100)
    ,  @Num INT
    
    SELECT @Num = Min(user_id) from INSERTED
    SELECT @Message = 'NET SEND NICK "Here is the new # - '
    +  CONVERT(VARCHAR(100), @Num) + '"'
    
    EXEC master.dbo.xp_cmdshell @Message
    
    RETURN
    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    There are problems specifying user name vs. computer name in NET SEND. Try using computer name instead. Also, make sure that the receiving computer's Messenger service is running with no problems (net send <hostname> test should result in a popup with the word "test")
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •