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(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
Philip van GassDatabase Developer/AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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 DeveloperCommented:
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/AdministratorAuthor 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) ?
Maximize Customer Retention with Superior Service

The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornSQL Server Data DudeCommented:
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/AdministratorAuthor Commented:
That worked Vitor. Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Accounting

From novice to tech pro — start learning today.