Automated Vendor HOLD

(0) Share

Report

Posted on by ptgjeff

170

I would like to place a vendor hold if any field in the Vendor card is modified. The idea is to use the HOLD removal password as a poor man's approach for Vendor Change Approval. I have been toying with using a SQL trigger but not having much luck. Is the use of an AFTER UPDATE trigger on the PM00200 table a bad idea?

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (2)

Sort by

Verified answer

Redbeard 12,931 on at

Like
a
(0)

Report
Copy link

Link copied!

The following Script will create a Trigger on the PM00200 table. I tested it on my database:

--------------------

IF OBJECT_ID ('Vendor_Hold', 'TR') IS NOT NULL
DROP TRIGGER Vendor_Hold;
GO
CREATE TRIGGER Vendor_Hold
ON PM00200
AFTER INSERT, UPDATE
AS UPDATE PM00200 set HOLD = 1;
GO

-----------------------------

You may be having trouble with the trigger, because you are looking for it in the wrong place.

Was this reply helpful? Yes No
ptgjeff 170 on at

Like
a
(0)

Report
Copy link

Link copied!

I get the following when trying to save the a change to the Vendor Card:

A save operation on table 'PM_Vendor_MSTR' (45).

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like
a
(0)

Report
Copy link

Link copied!

DO NOT ALTER the zDT_PM00200U trigger until this is successfully tested. I have tried resetting the zDT_PM00200U trigger using the original trigger script, and I am still receiving the error 45.

I get the same error when trying to update a Vendor after putting the Trigger in place. Browse to the location an delete it. I show the error as a type conversion for text to numeric, but tried a fix or two, to no avail. I will take a look at this, again later today. Perhaps someone else can figure out what I missed.

Was this reply helpful? Yes No
Verified answer

Mahmoud Saadi 32,738 on at

Like
a
(0)

Report
Copy link

Link copied!
The problem occurs because there is already a trigger running on the PM00200 which is [zDT_PM00200U], its main purpose is to get the DEX_ROW_TS updated accordingly

In this essence, adding another trigger with the specific statement (After Update) will lead to an error on the SQL level, usually called a nesting level error. It is stated as shown below:

Msg 217, Level 16, State 1, Procedure zDT_PM00200U, Line 7 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Therefore, why don't you just add the HOLD line to the already existing stored procedure which runs only after the update of a specific vendor record ?

Here is the script that you need to run in order to modify the [zDT_PM00200U] trigger, and get the hold option automated.

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER TRIGGER [dbo].[zDT_PM00200U] ON [dbo].[PM00200] AFTER UPDATE AS SET nocount ON BEGIN UPDATE dbo.PM00200 SET DEX_ROW_TS = GETUTCDATE(), HOLD = 1 FROM dbo.PM00200 , inserted WHERE PM00200.VENDORID = inserted.VENDORID END SET nocount OFF GO

All of the above is a technical consideration, right ? Let's think logically of how we would get rid of the hold now. As you update the trigger above, any update for any field on the vendor card level will lead to locking the vendor by placing it on hold. When you will uncheck the hold, the trigger will be activated as well (since it is an update) and put it on hold all over again. The result is, the hold will never be unchecked !

So i suggest thinking of another approach after listening to your requirement more closely. Why don't you simply revoke the access of this window for the users who might mistakenly play around with it ?

Your feedback is highly aprpeciated,

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like
a
(1)

Report
Copy link

Link copied!

Mahmoud is right - try as I may to figure out a way around the update locking the record all the time, I was unsuccessful. I tried comparing the before and after values of the HOLD field specifically, which I thought would resolve the issue:

WHERE PM00200.VENDORID = inserted.VENDORID and PM00200.HOLD <> inserted.HOLD

I expected the WHERE clause above would resolve the issue. It stood to reason if the original value was 1 and the new value was 0, the hold would not activate and vice versa. This did not prove out, but did allow for the hold field to be toggled off and on. I am calling no joy - sorry. As an aside. The error I encountered when running a separate trigger was "A save operation on table 'PM_Vendor_MSTR' (45)." There is a KB about this, which indicates in no uncertain terms Microsoft will not support Custom Triggers.

http://support.microsoft.com/kb/2564866

Was this reply helpful? Yes No
Mahmoud Saadi 32,738 on at

Like
a
(0)

Report
Copy link

Link copied!

ptgjeff

What is the status of this request ?

Your feedback is highly appreciated,

Was this reply helpful? Yes No
ptgjeff 170 on at

Like
a
(0)

Report
Copy link

Link copied!

Mahmoud -

The reason I did not simply revoke access to the window is because I was attempting to allow users to be able to enter/edit Vendor records as needed but build in logic that would allow only those users with the "HOLD REMOVAL" password to be able to take them off hold.

I understand your point about the hold removal itself causing the hold to be re-instated. I was going to attempt to code around that as indicated by Redbeard in this same feed.

Thanks for your input. I had previously read that multiple triggers would work OK, they would simply fire in Alphabetical order.

Thanks,

PTGJeff

Was this reply helpful? Yes No
Suggested answer

Justin Thorp 2,265 on at

Like
a
(0)

Report
Copy link

Link copied!

If you are looking for a Change Approval mechanism, I recommend you use the Electronic Signatures module/functionality.

Was this reply helpful? Yes No
ptgjeff 170 on at

Like
a
(0)

Report
Copy link

Link copied!

I just realized that I never closed out this issue. MANY thanks to both Harry and Mahmoud.

I wound up modifying the the zDT_PM00200U trigger and creating a new one for Insert as follows:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

ALTER TRIGGER [dbo].[zDT_PM00200I_USAC] ON [dbo].[PM00200] FOR INSERT AS

set nocount on

BEGIN

UPDATE dbo.PM00200

SET HOLD = 1

FROM dbo.PM00200, inserted

WHERE PM00200.VENDORID = inserted.VENDORID

END

set nocount off

GO

=====================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

ALTER TRIGGER [dbo].[zDT_PM00200U] ON [dbo].[PM00200] AFTER UPDATE AS

set nocount on

BEGIN

UPDATE dbo.PM00200

SET DEX_ROW_TS = GETUTCDATE()

FROM dbo.PM00200, inserted

WHERE PM00200.VENDORID = inserted.VENDORID

END

BEGIN

UPDATE dbo.PM00200

SET HOLD = 1

FROM dbo.PM00200, deleted

where PM00200.VENDORID = deleted.VENDORID and

deleted.HOLD = 0

END

set nocount off

GO

Was this reply helpful? Yes No