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 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
If you are looking for a Change Approval mechanism, I recommend you use the Electronic Signatures module/functionality.
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
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.
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.
Share
Report
All responses (
Answers (
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,
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.
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.
#1
André Arnaud de Cal...
292,516
Super User 2025 Season 1
#2
Martin Dráb
231,430
Most Valuable Professional
#3
nmaenpaa
101,156
