not able to INSERT or UPDATE table

  • I am having some difficulties. I created a trigger to update a table when oe_pick_ticket has an INSERT or UPDATE. When I attach that trigger to oe_pick_ticket, the table can not be UPDATEd or INSERTed. Can anyone help me? Thanks

  • can you post the trigger code, table definition and the error messages you are getting?

  • I'm sorry but I don't know what you mean by a table definition, and I can not find an error in SQL Server. The only error message I get if from my distribution software when I try to update or insert a new order into the table. that error message is...

    SQLDBCode: 10007

    General SQL Server error: check messages from the SQL Server

    No changes made to database.

    Here is the trigger code...

    CREATE TRIGGER [esi_automated_label_printing] ON [dbo].[oe_pick_ticket]

    FOR INSERT, UPDATE

    AS

    UPDATE esi_auto_label

    SET print_date = oe_pick_ticket.print_date

    ,order_no = oe_pick_ticket.order_no

    FROM oe_pick_ticket

    WHERE oe_pick_ticket.print_date = (SELECT TOP 1 oe_pick_ticket.print_date FROM oe_pick_ticket ORDER BY oe_pick_ticket.print_date DESC)

    EXEC master.dbo.xp_cmdshell 'copy "C:\Documents and Settings\Administrator\My Documents\Commander\AutoPrintPackingLabel.txt" "C:\Documents and Settings\Administrator\My Documents\Commander\Scan"', NO_OUTPUT

  • Should this update have a join to relate the 2 tables??

    Also I would make sur that this is not causing you the error

    : EXEC master.dbo.xp_cmdshell 'copy "C:\Documents and Settings\Administrator\My Documents\Commander\AutoPrintPackingLabel.txt" "C:\Documents and Settings\Administrator\My Documents\Commander\Scan"', NO_OUTPUT

    It's considered a bad pratice to start process inside a trigger because it can cause multiple errors in the application that are almost impossible to track without proper documentation.

  • I am not sure about the join, I'll try it out. I have taken out the EXEC part of the code and I still can't get it to work. I have also put the UPDATE statement into a stored procedure and then called that from the trigger, but that doesn't even work. It just seems that if I add any type of trigger at all to the table, it messes it up. I'll let you know what happens with trying to join the tables.

  • Here's an exemple of the join you need to write... Msg back if you need some help.

    CREATE TRIGGER LogOn_Delete

    on ACCESS

    For

    DELETE

    as

    SET NOCOUNT ON

    UPDATE L

    SET LogoffDate = GETDATE()

    FROM LogOn L inner join Deleted D on L.scrnnbr = D.scrnnbr and L.userid = D.userid and L.accessnbr = D.accessnbr

  • I finally got my UPDATE to work by using the INSERTED table to get my data from. Now I have hit a snag because when I use the EXEC XP_CMDSHELL, my software can no longer insert or update the original table.

  • What code are you using now?

    What error(s) are you getting?

  • Here is the trigger that I am now using to update my second table. It works when I take out the EXEC statement. I also put my cmd into a batch file as you can see to cut down on mistyping errors.

    CREATE TRIGGER [esi_automated_label_printing] ON [dbo].[oe_pick_ticket]

    FOR INSERT

    AS

    UPDATE esi_auto_label

    SET print_date = i.print_date

    ,order_no = i.order_no

    FROM inserted as i

    EXEC master.dbo.xp_cmdshell '"c:\printlabels.bat"', NO_OUTPUT

    Here is a copy of the error that i am getting from my software when I put an order in and save it...

    "

    SQLDBCode: 10038

    Attempt to initiate a new SQL Server operation with results pending.

    No changes made to database.

    INSERT INTO oe_pick_ticket_detail ( pick_ticket_no, ship_quantity, date_created, date_last_modified, last_maintained_by, line_number, print_quantity, unit_of_measure, unit_size, unit_quantity, company_id, oe_line_no, qty_requested, qty_to_pick, freight_in, staged, release_no, inv_mast_uid ) VALUES ( 1000275, 0.000000000, '6-8-2005 13:56:8.000', '6-8-2005 13:56:8.000', 'Matt Adamson', 1, 1.000000000, 'EA', 1.000000000, 1.000000000, 'ESI', 1, 1.000000000, 1.000000000, 0.000000000, 'N', 0, 12542 ).

    "

  • As I said before, it's not a good idea to start a process in a trigger.

    What does the batch file do?

  • All that the batch file does is copy a completely empty .txt file from one folder on the c:\ drive to another folder on the same drive.

  • And what's in that file?

    How does it relate to the insert?

  • copy "C:\Documents and Settings\administrator\My Documents\Commander\autoprintpackinglabel.txt" "C:\Documents and Settings\administrator\My Documents\Commander\Scan"

    this is what my batch file is. When my second table is updated, i need for this .txt file to be copied to the second folder. this will then tell my label software to print labels for the order number that can be found in oe_pick_ticket and in the new table esi_auto_label. but that part of the code is kept in the actual label design.

  • This is a task that should be handled with the client application. while the server can certainly do this. It cannot do it safely, easily. I would stronlgy suggest you call this command from the application after the server has completed the insert and gave control back to the application.

  • I know that xp_cmdshell can only be executed by members of the sysadmin role, so I have granted user permissions to execute it. I also set up the SQL Server Agent proxy account for when non-sysadmin users try to run the xp_cmdshell.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply