MS SQL - Create trigger to prevent insert if it doesn't match specific parameters
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