MS SQL - Create trigger to prevent insert if it doesn't match specific parameters Suppose I have two tables

payments with columns payment_id, batch_id
and batches with columns batch_id, batch_type

Can I add a trigger to prevent inserts into the payments table when the batch_id being inserted is not a specific batch_type? 

In short, for the inputted batch_id I want the trigger to check in the batches table to see if that batch_id has batch_type P. 

Something similar was discussed here but for MySQL (I am looking for a MS SQL trigger ) :- How to make a trigger that will compare input with a value of other table?.

Thanks for your help!
I think something like this would solve your problem.  If a payment being inserted has a batch_id that points to a batches row where the batch_type &lt;&gt; 'P', the transaction is rolled back.

CREATE TRIGGER [dbo].[Payments_PreventBatchThatAreNotP] ON [dbo].[Payments]
AFTER INSERT
AS
SET NOCOUNT ON;

IF EXISTS (
        SELECT *
        FROM inserted p
        JOIN dbo.Batches b ON b.batch_id = p.batch_id
            AND b.batch_type &lt;&gt; 'P'
        )
BEGIN
    RAISERROR ('Batch type not equal "P" - transaction rolled back',16,1);

    ROLLBACK
END


GO

ALTER TABLE [dbo].[Payments] ENABLE TRIGGER [Payments_PreventBatchThatAreNotP]
GO