MS SQL - Create trigger to prevent insert if it doesn't match specific parameters

Question

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!

Answer 1

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 <> '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 <> '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

Answer 2

The check constraint can be helpful on restricting values before insert them.

https://www.w3schools.com/sql/sql_check.asp