update trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

update trigger

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))
BEGIN
INSERT INTO Reporting.dbo.COMPLETED_ORDERS
(Pickslip,
Docket,
Account,
Attention,
[Name],
[Address],
Address2,
City
)

SELECT SalesOrder,
'AGPROFFESS',
Customer,
CustomerName,
ShipAddress1,
ShipAddress2,,
ShipAddress3,
ShipAddress4,

FROM inserted
WHERE 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.
BEGIN
INSERT 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 inserted
WHERE 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 trigger
USE [companyB]
GO
/****** Object: Trigger [dbo].[ATSQueue1] Script Date: 11/10/2014 12:43:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[ATSQueue1]
ON [companyB].[dbo].[SorMaster]
AFTER UPDATE
AS

IF (UPDATE(OrderStatus))
BEGIN
INSERT 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 inserted
WHERE 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 like

MERGE INTO Reporting.dbo.ATS_Shipping_Queue q
USING 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 12
Incorrect syntax near 'MERGE'.

USE [companyT]
GO
/****** Object: Trigger [dbo].[ATSQueue1] Script Date: 11/10/2014 12:43:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create TRIGGER [dbo].[ATSQueue1]
ON [companyT].[dbo].[SorMaster]

AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

IF (UPDATE(OrderStatus))

MERGE INTO Reporting.dbo.ATS_Shipping_Queue
USING INSERTED
ON Reporting.dbo.ATS_Shipping_Queue.Pickslip = companyB.dbo.SorMaster.SalesOrder
WHEN MATCHED
THEN UPDATE SET Reporting.dbo.ATS_Shipping_Queue.Address = companyB.dbo.SorMaster.ShipAddress1
WHEN 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 inserted
WHERE 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 there
5. 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 ERROR

Msg 102, Level 15, State 1, Procedure ATSQueue1, Line 11
Incorrect syntax near '.'.

USE [companyT]
GO
/****** Object: Trigger [dbo].[ATSQueue1] Script Date: 11/10/2014 12:43:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create TRIGGER [dbo].[ATSQueue1]

ON [companyT].[dbo].[SorMaster]

AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

IF (UPDATE(companyT.dbo.SorOrder.OrderStatus))

MERGE INTO Reporting.dbo.ATS_Shipping_Queue

USING INSERTED as companyT.dbo.SorMaster.SalesOrder

ON

Reporting.dbo.ATS_Shipping_Queue.Pickslip = companyT.dbo.SorMaster.SalesOrder

WHEN MATCHED

THEN UPDATE SET

Reporting.dbo.ATS_Shipping_Queue.Address = companyT.dbo.SorMaster.ShipAddress1

WHEN 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 alias

You can't write

Reporting.dbo.ATS_Shipping_Queue.Address = companyT.dbo.SorMaster.ShipAddress1

since 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 exist
UPDATE CO
SET Pickslip = SalesOrder,
Docket = 'AGPROFFESS',
Account = Customer,
...
City = ShipAddress4
FROM inserted as I
JOIN Reporting.dbo.COMPLETED_ORDERS AS CO
ON CO.SomePK = I.SomePK
WHERE OrderStatus = '4' and Branch IN ('AB','BC','SM','IN')

-- Insert new rows that don't already exist
INSERT 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 I
LEFT OUTER JOIN Reporting.dbo.COMPLETED_ORDERS AS CO
ON CO.SomePK = I.SomePK
WHERE 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 not

Even 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources