A trigger that informs the user when they have not entered the VAT number

Philip van Gass
Philip van Gass used Ask the Experts™
on
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(ucIDSOrdInvCashSaleVATno, '')
            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(ucIDSOrdInvCashSaleVATno, '')
            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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Do you really need a trigger? Why not use a check constraint?
ALTER TABLE dbo.InvNum   
   ADD CONSTRAINT CHK_VAT_number CHECK (ucIDSOrdInvCashSaleVATno IS NOT NULL AND DocType = 4 AND AccountId IN (1, 36,37) ); 

Open in new window

ste5anSenior Developer

Commented:
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.
Philip van GassDatabase Developer/Administrator

Author

Commented:
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) ?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

IT Engineer
Distinguished Expert 2017
Commented:
You're right. I think the you should negate the CHECK to accept everything that doesn't respect the rule:
ALTER TABLE dbo.InvNum   
   ADD CONSTRAINT CHK_VAT_number CHECK (NOT (ucIDSOrdInvCashSaleVATno IS NOT NULL AND DocType = 4 AND AccountId IN (1, 36,37)));  

Open in new window

Please test this at let us know if it worked properly.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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.
Philip van GassDatabase Developer/Administrator

Author

Commented:
That worked Vitor. Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial