How to Implement validation sets using triggers on a table.

Problem: How to Implement validation sets using triggers on a table.

Solution:

Create DML Triggers

Triggers for Insert, Update can be used to check before a record gets inserted into a table. Any type of validation can be implemented for raising errors or checking the values in the lookup if they exist and any type of complications can be done

Example:

In this Article I am going to explain how to validate an insert statement and to check if the value exists in the source table before it inserts.

I am going to create an insert, update DML trigger on Employees table to check the companyID before it inserts, please see the code below.

NOTE: This is just to show how triggers can be useful, we can do this same using constraints but i am just considering this as an base example. 

CREATE TRIGGER dbo.ValidateCompnayEmployee ON dbo.Employees

FOR INSERT,

                UPDATE

AS

BEGIN

                DECLARE @companyID INT

                SET @companyID = (SELECT Company_Id              FROM [inserted]              )

                IF NOT EXISTS (  SELECT [Company_Id] FROM [dbo].[Company]    WHERE company_id = @companyID )

                                RAISERROR (

                                                                N'The company Name or Id Does not exists.',

                                                                16,

                                                                1

                                                                )

                                WITH NOWAIT;

END

Test:

INSERT INTO [dbo].[Employees]

 (

                [Name],

                [PhoneNumber],

                [EmployeeType],

                [Company_Id]

                )

SELECT 'Gusatava',

                123456789,

                'Developer',

                8

FROM [dbo].[Employees]

Output: