June 6, 2005 at 8:01 am
I created this trigger and everything works except the UPDATE part. Can someone help me out? Thanks
CREATE TRIGGER [esi_automated_label_printing] ON [dbo].[oe_pick_ticket]
FOR INSERT
AS TRUNCATE TABLE esi_auto_label
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\Matt\My Documents\Commander\test.txt" "C:\Documents and Settings\Matt\My Documents\Commander\Scan"', NO_OUTPUT
June 6, 2005 at 8:33 am
After --> TRUNCATE TABLE esi_auto_label there are no rows in the table to update.
UPDATE esi_auto_label ...
June 6, 2005 at 8:39 am
So if I get rid of the TRUNCATE statement then will my UPDATE replace the existing data or just add on to it?
June 6, 2005 at 8:54 am
Update will modify existing row(s).
June 6, 2005 at 9:13 am
I got rid of the TRUNCATE statement and the UPDATE statement is still getting me nothing. The statement is returning "0" rows and I don't understand why. When I run just a standard SELECT statement using the same "TOP" parameters, it returns me exactly what I want. Any ideas?
June 7, 2005 at 1:33 am
I am not sure what you are trying to do but...
During the trigger execution sql server creates temporary tables named inserted & deleted (first for insert & update trigger, sec. for delete trigger). These temporary tables includes those rows that are pre-images of the update before they are actually committed into the corresponing tables. During the trigger execution you have the option to disregard the update or do something other with the inserted / deleted data.
Try for instance
... for insert as
trunc table esi_auto_label
insert into esi_auto_label select * from inserted
--> esi_auto_label will hold the inserted rows
June 7, 2005 at 2:35 am
Hi All,
Is this not a silly date comparison issue?
Have you tried CASTing or CONVERTing both dates in your WHERE clause?
Just a thought, haven't tried it yet.....
Have fun
Steve
We need men who can dream of things that never were.
June 7, 2005 at 7:02 am
Forgive the confused look that I would be giving you if you could see my face. As I read the trigger, I am assuming that you are placing values in a table to be printed as a label when an order is placed in your first table. Hence the truncate at the beginning of the trigger.
Since you are truncating the values at the beginning, and the select returns the values you want, wouldn't an 'insert into' be more appropriate here?
In short, after the order is placed in table on, insert the record into table to and spit it out into your file.
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
June 7, 2005 at 7:39 am
Here is what my trigger has evolved to. It now works. Honestly, I don't know whether an INSERT INTO would work here or not. This is the first trigger I have ever wrote. Needless to say, It's probably not the most effective way of doing it, but it does work. How would an INSERT INTO work in this instance?
CREATE TRIGGER [esi_automated_label_printing] ON [dbo].[oe_pick_ticket]
FOR INSERT
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\Matt\My Documents\Commander\test.txt" "C:\Documents and Settings\Matt\My Documents\Commander\Scan"', NO_OUTPUT
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply