June 7, 2005 at 1:47 pm
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
June 7, 2005 at 1:51 pm
can you post the trigger code, table definition and the error messages you are getting?
June 7, 2005 at 2:33 pm
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
June 7, 2005 at 2:43 pm
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.
June 7, 2005 at 2:50 pm
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.
June 7, 2005 at 2:58 pm
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
June 8, 2005 at 11:53 am
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.
June 8, 2005 at 11:58 am
What code are you using now?
What error(s) are you getting?
June 8, 2005 at 12:14 pm
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 ).
"
June 8, 2005 at 12:17 pm
As I said before, it's not a good idea to start a process in a trigger.
What does the batch file do?
June 8, 2005 at 12:20 pm
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.
June 8, 2005 at 12:23 pm
And what's in that file?
How does it relate to the insert?
June 8, 2005 at 12:27 pm
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.
June 8, 2005 at 12:30 pm
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.
June 8, 2005 at 12:32 pm
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