Explain why triggers aren't working after adding the following migration

[a-sharifov]

Issue Description

After applying the Initialize_DB migration containing several CREATE TRIGGER statements for the CartItems table, it appears that the triggers are not functioning as expected. Specifically, the triggers designed to manage the book quantity based on CartItems insertions, deletions, and updates are not modifying the Books table as intended.

Migration Context

The Initialize_DB migration creates several tables, including CartItems, Books, and related tables like Authors, Genres, Images, Languages, and Users. It also adds the following triggers:

• trg_after_insert_decrease_book_quantity: Before inserting into CartItems, it decreases the quantity of the corresponding book.
• trg_after_delete_increase_book_quantity: Before deleting from CartItems, it increases the quantity of the corresponding book.
• trg_after_update_adjust_book_quantity: Before updating in CartItems, it adjusts the quantity of the corresponding book.

The migration also contains the following code snippets:

migrationBuilder.Sql(@"
    CREATE OR REPLACE FUNCTION trg_decrease_book_quantity()
    RETURNS TRIGGER AS $$
    BEGIN
        UPDATE public.\"Books\"
        SET \"Quantity\" = \"Quantity\" - NEW.\"Quantity\"
        WHERE \"Id\" = NEW.\"BookId\";
        RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;
");

migrationBuilder.Sql(@"
    CREATE TRIGGER trg_after_insert_decrease_book_quantity
    BEFORE INSERT ON public.\"CartItems\"
    FOR EACH ROW
    EXECUTE FUNCTION trg_decrease_book_quantity();
");

migrationBuilder.Sql(@"
    CREATE OR REPLACE FUNCTION trg_increase_book_quantity()
    RETURNS TRIGGER AS $$
    BEGIN
        UPDATE public.\"Books\"
        SET \"Quantity\" = \"Quantity\" + OLD.\"Quantity\"
        WHERE \"Id\" = OLD.\"BookId\";
        RETURN OLD;
    END;
    $$ LANGUAGE plpgsql;
");

migrationBuilder.Sql(@"
    CREATE TRIGGER trg_after_delete_increase_book_quantity
    BEFORE DELETE ON public.\"CartItems\"
    FOR EACH ROW
    EXECUTE FUNCTION trg_increase_book_quantity();
");

migrationBuilder.Sql(@"
    CREATE OR REPLACE FUNCTION trg_adjust_book_quantity()
    RETURNS TRIGGER AS $$
    BEGIN
        UPDATE public.\"Books\"
        SET \"Quantity\" = (\"Quantity\" + OLD.\"Quantity\") - NEW.\"Quantity\"
        WHERE \"Id\" = NEW.\"BookId\";
        RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;
");

migrationBuilder.Sql(@"
    CREATE TRIGGER trg_after_update_adjust_book_quantity
    BEFORE UPDATE ON public.\"CartItems\"
    FOR EACH ROW
    EXECUTE FUNCTION trg_adjust_book_quantity();
");

Despite these triggers being created without errors, their intended functionality isn't being observed.

Expected Behavior

The triggers should adjust the Books table's Quantity field based on operations performed on the CartItems table. When adding items to a cart, the quantity should decrease. Similarly, when removing or updating cart items, the book quantity should adjust accordingly.