Philip van Gass
asked on
A trigger that informs the user when they have not entered the VAT number
We have a situation where a user-defined field has been added to the main Invoices table on a customer's Pastel Accounting system to store a VAT number. In certain situations the user has to enter that VAT number and if they do not then a trigger will come into operation to warn them by displaying a message. The problem arises when, after having shown the message, the user goes back to enter the value in the field but the system does not perform the update. I am showing below various versions of the trigger that we have tried to no avail.
USE [Foodspec Co (Pty) Ltd]
GO
/****** Object: Trigger [dbo].[tnt_VatNoPrommpt] Script Date: 3/20/2017 2:26:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tnt_VatNoPrommpt]
ON [dbo].[InvNum]
INSTEAD OF UPDATE
AS
BEGIN
/*
ucIDSOrdInvCashSaleVATno UDF IS REQUIRED FOR THIS TO WORK!
FIELD NAME: InvCashSaleVATno
TYPE: STRING
SIZE: 100
CREATE ON:INVENTORY DOCUMENT - SALES ORDER
*/
DECLARE @custID INTEGER
DECLARE @vatNo VARCHAR(100)
DECLARE @DocType INTEGER
SELECT @custID = AccountId,
@DocType = DocType,
@vatNo = ISNULL(ucIDSOrdInvCashSale VATno, '')
FROM inserted
IF @custID IN(36,37,1) AND @DocType = 4 AND @vatNo = ''
BEGIN;
--RAISERROR ('Please Supply A Cash Sale VAT Number!
-- This Transaction Will Be Rolled Back. '
-- , 16, 1) WITH NOWAIT
--ROLLBACK TRANSACTION
THROW 50000, 'Please Supply A Cash Sale VAT Number!', 0;
END
END
USE [Foodspec Co (Pty) Ltd]
GO
/****** Object: Trigger [dbo].[PVG_VatNoPrommpt] Script Date: 3/20/2017 3:04:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[PVG_VatNoPrommpt]
ON [dbo].[InvNum]
AFTER UPDATE
AS
BEGIN TRY
/*
ucIDSOrdInvCashSaleVATno UDF IS REQUIRED FOR THIS TO WORK!
FIELD NAME: InvCashSaleVATno
TYPE: STRING
SIZE: 100
CREATE ON:INVENTORY DOCUMENT - SALES ORDER
*/
DECLARE @custID INTEGER
DECLARE @vatNo VARCHAR(100)
DECLARE @DocType INTEGER
SELECT @custID = AccountId,
@DocType = DocType,
@vatNo = ISNULL(ucIDSOrdInvCashSale VATno, '')
FROM inserted
IF @custID IN(36,37,1) AND @DocType = 4 AND @vatNo = ''
BEGIN;
--RAISERROR ('Please Supply A Cash Sale VAT Number!
-- This Transaction Will Be Rolled Back. '
-- , 16, 1) WITH NOWAIT
--ROLLBACK TRANSACTION
THROW 50000, 'Please Supply A Cash Sale VAT Number!', 0;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
THROW
END CATCH
USE [Foodspec Co (Pty) Ltd]
GO
/****** Object: Trigger [dbo].[tnt_VatNoPrommpt] Script Date: 3/20/2017 2:26:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tnt_VatNoPrommpt]
ON [dbo].[InvNum]
INSTEAD OF UPDATE
AS
BEGIN
/*
ucIDSOrdInvCashSaleVATno UDF IS REQUIRED FOR THIS TO WORK!
FIELD NAME: InvCashSaleVATno
TYPE: STRING
SIZE: 100
CREATE ON:INVENTORY DOCUMENT - SALES ORDER
*/
DECLARE @custID INTEGER
DECLARE @vatNo VARCHAR(100)
DECLARE @DocType INTEGER
SELECT @custID = AccountId,
@DocType = DocType,
@vatNo = ISNULL(ucIDSOrdInvCashSale
FROM inserted
IF @custID IN(36,37,1) AND @DocType = 4 AND @vatNo = ''
BEGIN;
--RAISERROR ('Please Supply A Cash Sale VAT Number!
-- This Transaction Will Be Rolled Back. '
-- , 16, 1) WITH NOWAIT
--ROLLBACK TRANSACTION
THROW 50000, 'Please Supply A Cash Sale VAT Number!', 0;
END
END
USE [Foodspec Co (Pty) Ltd]
GO
/****** Object: Trigger [dbo].[PVG_VatNoPrommpt] Script Date: 3/20/2017 3:04:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[PVG_VatNoPrommpt]
ON [dbo].[InvNum]
AFTER UPDATE
AS
BEGIN TRY
/*
ucIDSOrdInvCashSaleVATno UDF IS REQUIRED FOR THIS TO WORK!
FIELD NAME: InvCashSaleVATno
TYPE: STRING
SIZE: 100
CREATE ON:INVENTORY DOCUMENT - SALES ORDER
*/
DECLARE @custID INTEGER
DECLARE @vatNo VARCHAR(100)
DECLARE @DocType INTEGER
SELECT @custID = AccountId,
@DocType = DocType,
@vatNo = ISNULL(ucIDSOrdInvCashSale
FROM inserted
IF @custID IN(36,37,1) AND @DocType = 4 AND @vatNo = ''
BEGIN;
--RAISERROR ('Please Supply A Cash Sale VAT Number!
-- This Transaction Will Be Rolled Back. '
-- , 16, 1) WITH NOWAIT
--ROLLBACK TRANSACTION
THROW 50000, 'Please Supply A Cash Sale VAT Number!', 0;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
THROW
END CATCH
Vitor is right, consider using a constraint in the first place.
But, much more important: Your triggers do not work correctly on multi-row updates.
And: The first trigger does not save any data. INSTEAD OF means that you need to call a separate SQL statement to store the data from the INSERTED virtual table.
But, much more important: Your triggers do not work correctly on multi-row updates.
And: The first trigger does not save any data. INSTEAD OF means that you need to call a separate SQL statement to store the data from the INSERTED virtual table.
ASKER
I had to add 'WITH NOCHECK' to get it to compile but what happens if the user tries to add an invoice where the DocType is not equal to 4 or if the AccountID is not within the range of values (1, 36, 37) ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Looks like Vitor and ste5an addressed the direct question, so I'll add a couple of tangents:
>The problem arises when, after having shown the message, the user goes back to enter the value in the field but the system does not perform the update
Recommend contacting the front-end developer responsible for this happening, smack them upside the head, and telling them to fix it.
>I had to add 'WITH NOCHECK' to get it to compile
This is a VERY dangerous road to go down, as changing a FK constraint to NOCHECK will mean that the query engine will not use that FK at all when executing queries, which means full table scans and slow query execution. I really recommend implementing whatever the above experts recommend and not screwing with the FK.
>The problem arises when, after having shown the message, the user goes back to enter the value in the field but the system does not perform the update
Recommend contacting the front-end developer responsible for this happening, smack them upside the head, and telling them to fix it.
>I had to add 'WITH NOCHECK' to get it to compile
This is a VERY dangerous road to go down, as changing a FK constraint to NOCHECK will mean that the query engine will not use that FK at all when executing queries, which means full table scans and slow query execution. I really recommend implementing whatever the above experts recommend and not screwing with the FK.
ASKER
That worked Vitor. Thanks.
Open in new window