0

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!

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
0

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.