Author |
Topic |
Patyk
Yak Posting Veteran
74 Posts |
Posted - 2014-11-06 : 19:09:51
|
Below is my trigger which appends record to a table (Completed_Orders) when order status changes to 4.Sometimes order is modified again and status is changed again to 4. In such case I just want the trigger to stop and not append a duplicate record. Or even better to delete the previously appended record and add the new one.Any help is greatly appreciated.IF (UPDATE(OrderStatus))BEGININSERT INTO Reporting.dbo.COMPLETED_ORDERS (Pickslip, Docket, Account, Attention, [Name], [Address], Address2, City )SELECT SalesOrder,'AGPROFFESS',Customer,CustomerName,ShipAddress1,ShipAddress2,,ShipAddress3,ShipAddress4,FROM insertedWHERE OrderStatus = '4' and Branch IN ('AB','BC','SM','IN') |
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2014-11-10 : 13:20:53
|
The UPDATE() function really only shows if the column was referenced in the INSERT or UPDATE statement. It will return True if the column "changes" from a 4 to a 4 (sic) if the UPDATE statement listed the column explicitly. If you want to know if the value actually changed or what the previous value was, you need to join the inserted and deleted tables. No amount of belief makes something a fact. -James Randi |
|
|
Patyk
Yak Posting Veteran
74 Posts |
Posted - 2014-11-10 : 14:20:13
|
I am inserting the values into my completed orders table, but sometimes this this line already exists. If exists how can I delete the existing line and insert it again? |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-10 : 14:44:57
|
Why delete then readd? Why not just update it? |
|
|
Patyk
Yak Posting Veteran
74 Posts |
Posted - 2014-11-10 : 15:05:49
|
Ok update would be great is this possible ? |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-10 : 15:18:31
|
If your trigger is AFTER UPDATE, then the update has already been done. If it is INSTEAD OF UPDATE, then you can re-issue the UPDATE command in the trigger. Note:If an INSTEAD OF trigger defined on a table executes a statement against the table that would ordinarily fire the INSTEAD OF trigger again, the trigger is not called recursively. Instead, the statement is processed as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions. from http://msdn.microsoft.com/en-us/library/ms189799.aspx |
|
|
Patyk
Yak Posting Veteran
74 Posts |
Posted - 2014-11-10 : 15:23:35
|
IF (UPDATE(OrderStatus)) at this point the update occured. Now I don't want to insert but update the record that's already in the table. BEGININSERT INTO Reporting.dbo.COMPLETED_ORDERS What should I change here. I still need an insert for most of the records, only the once that already exist will have to be updated?(Pickslip,Docket,Account,Attention,[Name],[Address],Address2,City)SELECT SalesOrder,'AGPROFFESS',Customer,CustomerName,ShipAddress1,ShipAddress2,,ShipAddress3,ShipAddress4,FROM insertedWHERE OrderStatus = '4' and Branch IN ('AB','BC','SM','IN') |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-10 : 15:31:44
|
If it is an INSTEAD of trigger, then the update has not been done when you issue IF(UPDATE(OrderStatus)). Can you please post the entire trigger definition? |
|
|
Patyk
Yak Posting Veteran
74 Posts |
Posted - 2014-11-10 : 15:44:15
|
this is my entire triggerUSE [companyB]GO/****** Object: Trigger [dbo].[ATSQueue1] Script Date: 11/10/2014 12:43:31 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[ATSQueue1]ON [companyB].[dbo].[SorMaster]AFTER UPDATE ASIF (UPDATE(OrderStatus))BEGININSERT INTO Reporting.dbo.ATS_Shipping_Queue (Pickslip, Docket, Account, Attention, [Name], [Address], Address2, City, Province, Country, Zip, Phone, Email, Instructions, Ref, PO, [Audit_Timestamp])SELECT RTRIM(REPLACE(SalesOrder,',',' ')),'AGPROFFESS', RTRIM(REPLACE(Customer,',',' ')),' ',RTRIM(REPLACE(CustomerName,',',' ')),RTRIM(REPLACE(ShipAddress1,',',' ')),RTRIM(REPLACE(ShipAddress5,',',' ')),RTRIM(REPLACE(ShipAddress2,',',' ')),RTRIM(REPLACE(ShipAddress3,',',' ')),RTRIM(REPLACE(ShipAddress4,',',' ')),RTRIM(REPLACE(ShipPostalCode,',',' ')),RTRIM(REPLACE(ShippingInstrs,',',' ')),RTRIM(REPLACE(Email,',',' ')),RTRIM(REPLACE(SpecialInstrs,',',' ')),RTRIM(REPLACE(SalesOrder,',',' ')),RTRIM(REPLACE(CustomerPoNumber,',',' ')),getdate()FROM insertedWHERE OrderStatus = '4' and Branch IN ('AB','BC','SM','IN')END |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-10 : 16:03:22
|
Is this the correct trigger? Previously you said that your trigger "appends record to a table (Completed_Orders)" but your code references the ATS_Shipping_Queue table instead.Assuming this is the correct trigger -- It's an AFTER trigger. So, how do you uniquely identify rows in the ATS_Shipping_Queue table? You see what you want is something likeMERGE INTO Reporting.dbo.ATS_Shipping_Queue qUSING inserted ins ON q.<your keys> = ins.<your keys>WHEN MATCHED AND (test for values being updated) THEN UPDATE SET q.<your columns> = ins.<your columns>WHEN NOT MATCHED THEN INSERT ins. ... etc.; |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-13 : 14:08:26
|
where you now have INSERT...SELECT |
|
|
Patyk
Yak Posting Veteran
74 Posts |
Posted - 2014-11-13 : 15:14:09
|
This is what I have now. I get the following error Msg 102, Level 15, State 1, Procedure ATSQueue1, Line 12Incorrect syntax near 'MERGE'.USE [companyT]GO/****** Object: Trigger [dbo].[ATSQueue1] Script Date: 11/10/2014 12:43:31 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate TRIGGER [dbo].[ATSQueue1]ON [companyT].[dbo].[SorMaster]AFTER UPDATE ASBEGINSET NOCOUNT ON;IF (UPDATE(OrderStatus))MERGE INTO Reporting.dbo.ATS_Shipping_QueueUSING INSERTED ON Reporting.dbo.ATS_Shipping_Queue.Pickslip = companyB.dbo.SorMaster.SalesOrderWHEN MATCHED THEN UPDATE SET Reporting.dbo.ATS_Shipping_Queue.Address = companyB.dbo.SorMaster.ShipAddress1WHEN NOT MATCHED THEN INSERT (Pickslip, Docket, Account, Attention, [Name], [Address], Address2, City, Province, Country, Zip, Phone, Email, Instructions, Ref, PO, [Audit_Timestamp])SELECT RTRIM(REPLACE(SalesOrder,',',' ')),'AGPROFFESS', RTRIM(REPLACE(Customer,',',' ')),' ',RTRIM(REPLACE(CustomerName,',',' ')),RTRIM(REPLACE(ShipAddress1,',',' ')),RTRIM(REPLACE(ShipAddress5,',',' ')),RTRIM(REPLACE(ShipAddress2,',',' ')),RTRIM(REPLACE(ShipAddress3,',',' ')),RTRIM(REPLACE(ShipAddress4,',',' ')),RTRIM(REPLACE(ShipPostalCode,',',' ')),RTRIM(REPLACE(ShippingInstrs,',',' ')),RTRIM(REPLACE(Email,',',' ')),RTRIM(REPLACE(SpecialInstrs,',',' ')),RTRIM(REPLACE(SalesOrder,',',' ')),RTRIM(REPLACE(CustomerPoNumber,',',' ')),getdate()FROM insertedWHERE OrderStatus = '4' and Branch IN ('AB','BC','SM','IN')END |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-13 : 15:32:46
|
you need to read up on MERGE.There's lots of problems here. 1. Your MERGE ON clause doesn't refer to the USING table (companyB is not in scope)2. Your UPDATE command doesn't refer to the USING table (companyB is not in scope)3. The keyword SELECT in the when NOT Matched clause shouldn't be there. Use VALUES instead (refer to documentation)4. FROM inserted shouldn't be there5. WHERE OrderStatus = '4' and Branch IN ('AB','BC','SM','IN') should be in the WHEN NOT MATCHED CLAUSE like this:WHEN NOT MATCHED AND OrderStatus = '4' and Branch IN ('AB','BC','SM','IN') |
|
|
Patyk
Yak Posting Veteran
74 Posts |
Posted - 2014-11-13 : 16:50:58
|
STILL GET THIS ERRORMsg 102, Level 15, State 1, Procedure ATSQueue1, Line 11Incorrect syntax near '.'.USE [companyT]GO/****** Object: Trigger [dbo].[ATSQueue1] Script Date: 11/10/2014 12:43:31 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate TRIGGER [dbo].[ATSQueue1]ON [companyT].[dbo].[SorMaster]AFTER UPDATE ASBEGINSET NOCOUNT ON;IF (UPDATE(companyT.dbo.SorOrder.OrderStatus))MERGE INTO Reporting.dbo.ATS_Shipping_QueueUSING INSERTED as companyT.dbo.SorMaster.SalesOrderON Reporting.dbo.ATS_Shipping_Queue.Pickslip = companyT.dbo.SorMaster.SalesOrderWHEN MATCHEDTHEN UPDATE SET Reporting.dbo.ATS_Shipping_Queue.Address = companyT.dbo.SorMaster.ShipAddress1WHEN NOT MATCHED AND companyT.dbo.SorMaster.OrderStatus = '4' and companyT.dbo.SorMaster.OrderStatus.Branch IN ('AB','BC','SM','IN')THEN INSERT (Pickslip, Docket, Account, Attention, [Name], [Address], Address2, City, Province, Country, Zip, Phone, Email, Instructions, Ref, PO, [Audit_Timestamp])VALUES (RTRIM(REPLACE(SalesOrder,',',' ')),'AGPROFFESS', RTRIM(REPLACE(Customer,',',' ')),' ',RTRIM(REPLACE(CustomerName,',',' ')),RTRIM(REPLACE(ShipAddress1,',',' ')),RTRIM(REPLACE(ShipAddress5,',',' ')),RTRIM(REPLACE(ShipAddress2,',',' ')),RTRIM(REPLACE(ShipAddress3,',',' ')),RTRIM(REPLACE(ShipAddress4,',',' ')),RTRIM(REPLACE(ShipPostalCode,',',' ')),RTRIM(REPLACE(ShippingInstrs,',',' ')),RTRIM(REPLACE(Email,',',' ')),RTRIM(REPLACE(SpecialInstrs,',',' ')),RTRIM(REPLACE(SalesOrder,',',' ')),RTRIM(REPLACE(CustomerPoNumber,',',' ')),getdate())END |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-13 : 20:20:04
|
USING INSERTED as companyT.dbo.SorMaster.SalesOrder is invalid since the alias may not have dots in it. try using just SalesOrder as your aliasYou can't writeReporting.dbo.ATS_Shipping_Queue.Address = companyT.dbo.SorMaster.ShipAddress1since the table ShipAddress1 is not part of the MERGE operation. Is that column not in the inserted columns? If not, you'll have to make the USING clause into a derived table (basically a subquery) that joins INSERTED with ShipAddress1.One last thing. THe entire MERGE statement MUST be terminated with a semicolon. |
|
|
Kristen
Test
22859 Posts |
Posted - 2014-11-14 : 08:00:54
|
MERGE adds complexity to my way of thinking. Why not just UPDATE any that exist and INSERT any that don't?Using your original code as an example something like:-- Update any rows that already existUPDATE COSET Pickslip = SalesOrder,Docket = 'AGPROFFESS',Account = Customer,...City = ShipAddress4FROM inserted as IJOIN Reporting.dbo.COMPLETED_ORDERS AS COON CO.SomePK = I.SomePKWHERE OrderStatus = '4' and Branch IN ('AB','BC','SM','IN')-- Insert new rows that don't already existINSERT INTO Reporting.dbo.COMPLETED_ORDERS(Pickslip,Docket,Account,Attention,[Name],[Address],Address2,City)SELECT SalesOrder,'AGPROFFESS',Customer,CustomerName,ShipAddress1,ShipAddress2,,ShipAddress3,ShipAddress4,FROM inserted as ILEFT OUTER JOIN Reporting.dbo.COMPLETED_ORDERS AS COON CO.SomePK = I.SomePKWHERE OrderStatus = '4' and Branch IN ('AB','BC','SM','IN') AND CO.SomePK IS NULL -- No existing record |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-14 : 08:51:42
|
quote: Originally posted by Kristen MERGE adds complexity to my way of thinking. Why not just UPDATE any that exist and INSERT any that don't?
that's exactly what MERGE does, but more succinctly and much more efficiently |
|
|
Kristen
Test
22859 Posts |
Posted - 2014-11-14 : 08:57:17
|
quote: Originally posted by gbritton that's exactly what MERGE does, but more succinctly and much more efficiently
Yeah, but it is complex for people to understand, more especially Newbies |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-14 : 09:01:51
|
IMHO not that hard to understand. Plus it's ANSI (and thus portable) whereas UPDATE...JOIN is notEven if it is harder to understand at first, it's worth the effort because it results in more succinct code and better performance. |
|
|
Kristen
Test
22859 Posts |
Posted - 2014-11-14 : 09:12:27
|
Well that's "for you", I am comfortable with that too. Juniors here find MERGE hard, and IME they find debugging using separate Update / Insert is easier for them. Not that hard to then convert their code to MERGE if necessary (in most cases our triggers and UpSert SProcs are processing only 1 row 90+% of the time, so performance would not be a significant issue either way, and in such cases our standard coding style favours the code that is cheapest to write and maintain - which is irrespective of the skill level of the coder!!) |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-14 : 09:31:35
|
Well OK then. I try to push our juniors to learn and use ANSI-compliant code first then go for a vendor's dialect when the result is awkward or inefficient or fails to take advantage of some important feature. Turns out to be important for us since we support DB2, MySQL and Oracle along with SQL Server. To the extent that those products are ANSI-compliant, it flattens the learning curve somewhat and makes their developing skills more portable.The INSERT/UPDATE approach usually results in some DRY violations. That tends to make maintenance harder, not easier. |
|
|
Next Page
|
|
|