PL/SQL Trigger BEFORE INSERT based on values in the changing table-CodePudding

so I have this problem.

I want to prevent adding new carts if the client already at least one unpaid shopping cart using trigger before insert on each row (in case more than one new cart is being inserted).

Tables' model screenshot

I managed to solve this problem quite easily in T-SQL counting records containing NULL in paidTime column in the modified table.

CREATE TRIGGER one_unpaid_cart_per_client  ON Cart
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE newCart_cursor CURSOR FOR SELECT IdClient, IdCart, isPaid FROM inserted;

    DECLARE @IdClient int;
    DECLARE @IdCart int;
    DECLARE @isPaid datetime;

    OPEN newCart_cursor;
    FETCH NEXT FROM newCart_cursor INTO @IdClient, @IdCart, @isPaid;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @isPaid is null
        BEGIN
            if 1 < (SELECT COUNT(1) FROM Cart WHERE IdClient = @IdClient AND isPaid is Null)
            BEGIN
                PRINT 'Unpaid cart already exists for client id: '   Cast(@IdClient as Varchar);
                DELETE FROM Cart WHERE IdCart = @IdCart;
                Raiserror('New cart has not been added', 1, 1);
            END;
        END;
        FETCH NEXT FROM newCart_cursor INTO @IdClient, @IdCart, @isPaid;
    END;

    CLOSE newCart_cursor;
    DEALLOCATE newCart_cursor;
    SET NOCOUNT OFF;
END;

In PL/SQL though as far as I know you cannot query the changing table, so I can't check how many unpaid carts are there.

Is there anything I can do to get this done using trigger in PL/SQL?

Thanks for any help!

CodePudding user response：

As OldProgrammer suggested, what you are thinking of doing can be done using a compound trigger in Oracle. In that approach, you would have a BEFORE INSERT...FOR EACH ROW trigger to record the client IDs in memory (a PL/SQL array or something) and then a BEFORE INSERT statement level trigger to check for multiple unpaid carts for any of the recorded client IDs (throwing an exception if any are found).

However, using triggers to enforce referential integrity is hard to get right. This is mostly because triggers fire before transactions are committed. So, for example:

Session A, time 1: insert unpaid cart for client 100 (trigger check passes)
Session B, time 2: insert unpaid cart for client 100 (trigger check ALSO passes, because session A has not committed yet!)
Session A: time 3: commit
Session B: time 4: commit

... and your trigger has failed to accomplish its purpose.

A better way to do this is to create a unique constraint, relying on the fact that unique constraints do not reject rows having NULL values in all the columns of the constraint.

Something like the example below should avoid the race condition described above and work 100% of the time.

--drop table cart;

CREATE TABLE cart
  ( cart_id          NUMBER NOT NULL,
    client_id        NUMBER NOT NULL,
    payment_time     TIMESTAMP,
    unpaid_client_id NUMBER 
      INVISIBLE GENERATED ALWAYS AS (DECODE(payment_time,NULL,client_id, NULL)) VIRTUAL,
    is_unpaid        VARCHAR2(1) 
      INVISIBLE GENERATED ALWAYS AS (DECODE(payment_time,NULL,'Y',NULL)) VIRTUAL,
    CONSTRAINT cart_pk PRIMARY KEY ( cart_id ),
    CONSTRAINT max_one_unpaid UNIQUE ( unpaid_client_id, is_unpaid)
  );

delete from cart;  
  
-- This should be allowed: the first unpaid cart for a client
insert into cart ( cart_id, client_id, payment_time ) values ( 100, 1, null );
--1 row(s) inserted.

-- This should be rejected: a second unpaid cart for a client
insert into cart ( cart_id, client_id, payment_time ) values ( 101, 1, null );
--ORA-00001: unique constraint (SQL_PRUOVRWCMSHZECACDIWPGIBED.MAX_ONE_UNPAID) violated ORA-06512: at "SYS.DBMS_SQL", line 1721

-- This should be allowed, a paid cart for a client having an unpaid one    
insert into cart ( cart_id, client_id, payment_time ) values ( 102, 1, SYSTIMESTAMP );
--1 row(s) inserted.

-- This should be allowed, a second paid cart for a client having an unpaid one    
insert into cart ( cart_id, client_id, payment_time ) values ( 103, 1, SYSTIMESTAMP );
--1 row(s) inserted.