Prevent Insert and Update record having OLD date-CodePudding

I want to prevent 3 things

Prevent updating records if the existing date is older than 2022-01-01
Prevent updating record if the new date is older than 2022-01-01
Prevent insert if the date is less than 2022-01-01.

Basically, it should not allow insert or update the record if date is less than 2022-01-01.

I tried with a trigger with a cursor. I have to use cursor since it should work on batch.

CREATE TRIGGER TriggerDate
ON tb
AFTER INSERT,UPDATE 
AS
BEGIN
    DECLARE @IVDate Date;

    DECLARE my_Cursor CURSOR FOR 
         SELECT InvoiceDate 
         FROM INSERTED; 

    OPEN my_Cursor; 
     
    FETCH NEXT FROM my_Cursor INTO @IVDate;
        
    WHILE @@FETCH_STATUS = 0 
    BEGIN
        FETCH NEXT FROM my_Cursor INTO @IVDate;

        IF @IVDate < '2022-01-01'
           ROLLBACK TRANSACTION
    END

    CLOSE my_Cursor; 
    DEALLOCATE my_Cursor;
END

But the issue arises with order of insert statement.

For example - the following statement works well.

insert into tb values (1, '2022-05-02','A');
insert into tb values (2, '2021-08-06','B');

But the following does not work:

insert into tb values (2, '2021-08-06','B');
insert into tb values (1, '2022-05-02','A');   -- This record is correct but still it won't insert

Same thing happens with UPDATE statement having where condition with OR

For quick understanding, please refer to this dbfiddle

https://dbfiddle.uk/bCrXMfUS

CodePudding user response：

Don't use a CURSOR in a TRIGGER. A trigger should have as minimal impact on the calling statement as possible, and a CURSOR is one of the most impactful things you can have.

All you need to do here is use an EXISTS to check if the a row exists that you don't want and the THROW an error. Don't use ROLLBACK in a TRIGGER as thist give a meaningful error to the end user; have the outer query handle the rolling back of the transaction:

CREATE TRIGGER dbo.YourTrigger_ins ON dbo.YourTable
AFTER INSERT AS
BEGIN
    IF EXISTS (SELECT 1
               FROM inserted
               WHERE InvoiceDate < '20220101')
       THROW 50001, N'An error was thrown in the trigger ''dbo.YourTrigger_ins'' on the table ''dbo.YourTable''. A row where the invoice date is prior to 2022-01-01 cannot be inserted.', 16;
END;
GO

CREATE TRIGGER dbo.YourTrigger_upd ON dbo.YourTable
AFTER UPDATE AS
BEGIN
    IF EXISTS (SELECT 1
               FROM deleted
               WHERE InvoiceDate < '20220101')
       THROW 50002, N'An error was thrown in the trigger ''dbo.YourTrigger_upd'' on the table ''dbo.YourTable''. A row where the invoice date is prior to 2022-01-01 cannot be updated.', 16;
    IF EXISTS (SELECT 1
               FROM inserted
               WHERE InvoiceDate < '20220101')
       THROW 50003, N'An error was thrown in the trigger ''dbo.YourTrigger_upd'' on the table ''dbo.YourTable''. The value of the invoice date cannot be updated to be prior to 2022-01-01 .', 16;
END;

db<>fiddle

CodePudding user response：

When you want to exclude inserts from actually being inserteed which a condition you can do it in a INSTEAD OF INSERT trigger

create table tb(InvoiceNo int, InvoiceDate Date, CustomerName varchar(10));

insert into tb values(3,'2021-08-06','B');

1 rows affected

CREATE OR ALTER TRIGGER IgnoreInvoices ON dbo.tb
    INSTEAD OF INSERT
 AS
    BEGIN
    SET NOCOUNT ON;
    INSERT dbo.tb(InvoiceNo, InvoiceDate,CustomerName)
        SELECT InvoiceNo, InvoiceDate,CustomerName
        FROM inserted
        WHERE InvoiceDate >= '2022-01-01';
    END

CREATE OR ALTER TRIGGER IgnoreInvoicesupdate ON dbo.tb
    INSTEAD OF UPDATE
 AS
    BEGIN
    SET NOCOUNT ON;
  IF EXISTS 
  (
     SELECT 1 FROM inserted i
       JOIN deleted AS d ON i.InvoiceNo = d.InvoiceNo
       WHERE d.InvoiceDate >= '2022-01-01' AND i.InvoiceDate >= '2022-01-01'  
  )
  BEGIN
     UPDATE src 
       SET CustomerName = i.CustomerName, 
          InvoiceDate = i.InvoiceDate
       FROM dbo.tb AS src
       INNER JOIN inserted AS i
       ON i.InvoiceNo = src.InvoiceNo
       AND (src.InvoiceDate <> i.InvoiceDate or src.CustomerName = i.CustomerName);
  END
    END

insert into tb values(4,'2022-05-02','A');
insert into tb values(2,'2021-08-06','B');

2 rows affected

select * from tb

InvoiceNo	InvoiceDate	CustomerName
3	2021-08-06	B
4	2022-05-02	A

UPDATE tb SET InvoiceDate = '2021-05-02' WHERE InvoiceNo = 4;;
UPDATE tb SET InvoiceDate = '2022-05-02' WHERE InvoiceNo = 3;;

2 rows affected

select * from tb

InvoiceNo	InvoiceDate	CustomerName
3	2021-08-06	B
4	2022-05-02	A

Delete from TB

2 rows affected

insert into tb values(1,'2021-08-06','B');
insert into tb values(2,'2022-05-02','A');---This record is correct but still it won't insert

2 rows affected

select * from tb

InvoiceNo	InvoiceDate	CustomerName
2	2022-05-02	A

fiddle