Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2007
    Posts
    54

    Unanswered: selecting in a variable in SQL Server 2005 Express

    Hi there. HOpefully this is something really simple ... but i can't figure out why my trigger is failing. The error I'm getting is "incorrect syntax near '='".

    CREATE TRIGGER MYTRIGGER ON DBO.MYTABLE
    AFTER UPDATE
    AS
    DECLARE @PALLETID INT
    DECLARE@CMDSTRING VARCHAR(1000)

    IF EXISTS
    (SELECT @PID = I.PALLETID FROM INSERTED I
    WHERE I.STATUS=1)
    SET @CMDSTRING='ECHO ' + @PID + ' >> C:\JL\SQLOUTPUT.LOG'
    EXEC XP_CMDSHELL @CMDSTRING

    any suggestions?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes.

    There is a lot wrong with this trigger - conceptually and errors in the code.

    My recommendation would be to explain your business need here. There is almost certainly a better way to handle this.
    This thread will probably throw a little cold water on what you are trying:
    SQL Server Forums - Trigger Madness

  3. #3
    Join Date
    Jul 2007
    Posts
    54
    ok. a little overview of what i'm trying to do. then i'll address the why i have to do it this way.

    i need a table trigger that will write the pk value to a file whenever a record has been modified to have a status of 1.

    this file will then be read by a stored procedure which will simply return the pk value it reads. this stored proc will be called by an external application...

    as for why i'm doing it this way. we are using an iway / biztalk type application for the industrial world and as per their support department, this is the only way to get data out from a sql database into their app without "knowing" which record to request. it's a lot more complicated than this but i'm trying to just summarize what's going on at a very high level. in any case, although i dont' quite like the solution myself, i have to give it a try to see if it'll work.
    any suggestions would be appreciated.
    Last edited by chubbypama; 05-20-10 at 14:48.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That's a nasty sounding requirement from a third party app.

    The problem with a trigger is that it is then within the transaction. You should not call or access any resources outside of the database in a transaction - if there is a problem your transaction will hang or even rollback. Also, whoever is updating the table will need sysadmin privileges just to use xp_cmdshell.

    My advice would be write your trigger to write these IDs to another table. Create a job to poll that table every second (or whatever frequency you can live with). DELETE any rows in the table using the OUTPUT clause to write to the file. It is fractionally more involved but substantially more robust.

    Your trigger would now be something like:
    Code:
    CREATE TRIGGER MYTRIGGER ON DBO.MYTABLE
    AFTER UPDATE, INSERT
    AS
    INSERT INTO MyOtherTable (PALLETID)
    SELECT PALLETID
    FROM INSERTED
    WHERE STATUS=1
    If this works for you we can work on the job code.

  5. #5
    Join Date
    Jul 2007
    Posts
    54
    hmm... that sounds a little more slick than writing out to a file. i'm going to be shifting gears this morning to work on another project but this afternoon, i'll give your suggestion a try.
    one question though. AFTER the trigger writes out to this temp table, i'm still going to need to use their (3rd party app) dos utility to call their application, which in turn will call a stored proc that will read from the temp table, save the value as the output parm (and thereby send the value to the 3rd party app) and finally, as you recommend, delete the record from the temp table.

    The way to call their dos utility is via xp_cmdshell. Do you know of any other options other than the xp_cmdshell? why do i get the feeling you're going to be cringing or gasping when you see that i'm going to be using xp_cmdshell???

    thanks.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sometimes one has to use xp_cmdshell. I do. It is best not to if you can avoid it though. The advantage here is the person inserting in to the table now does not need permissions to run xp_cmdshell, only the owner of the job polling the table.

    We will need to work on the logic to call this app - what you have in the trigger does not work (as you know)

  7. #7
    Join Date
    Jul 2007
    Posts
    54
    good to know. just fyi. the latest version of my trigger looks like this:
    CREATE TRIGGER MYTRIGGER ON DBO.MYTABLE
    AFTER UPDATE
    AS
    DECLARE @PID INT

    SELECT @PID = PALLETID
    FROM INSERTED
    WHERE STATUS = 1

    IF @@ROWCOUNT <> 0 --or check for NULL value in @PID
    BEGIN
    DECLARE @CMDSTRING VARCHAR(1000)
    SET @CMDSTRING='ECHO ' + CONVERT(varchar(5), @PID) + ' >> C:\JL\SQLOUTPUT.LOG'
    EXEC XP_CMDSHELL @CMDSTRING
    -- NOW CALL THE 3RD PARTY STUFF
    EXEC XP_CMDSHELL "C:\JL\RSSQL_DOSTRIGGER DOSUTILTRANSACTION"
    END
    GO

    i tested this last night and it works. it creates the file ... although it's hard to tell what other funky characters are being embedded (it looks like some form of a crlf). and the 3rd party app is being invoked properly.

    but i'll try to change the code / trigger to write to a temp table.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Try updating many rows....

  9. #9
    Join Date
    Jul 2007
    Posts
    54
    yep. so i understand the code has to look different if more than one row is involved. but that's never going to be the case. only one at a time....

  10. #10
    Join Date
    Jul 2007
    Posts
    54
    Hi there. I just tried:

    CREATE TRIGGER MYTRIGGER ON DBO.MYTABLE
    AFTER UPDATE
    AS
    INSERT INTO MyOtherTable (PALLETID)
    SELECT PALLETID
    FROM INSERTED
    WHERE STATUS=1

    i'm getting an error invalid column name with the (PALLETID) in the insert statement...
    i tried using a variable name but then i get an error with the select statement saying that you can use variable assignments in an embedded select...
    any suggestions?

  11. #11
    Join Date
    Jul 2007
    Posts
    54
    the above problem has been resolved.
    thanks.

Posting Permissions

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