SQL Server : Trigger after insert, delete

Question

Please help me with the triggers in the language of SQL.

I have 2 tables product_subcategories and Products.

product_subcategories: id, name, count_products
Products: id, name, price, id_product_subcategories

Necessary when adding or deleting rows in the product table to update the number of products in the subcategories

For example, we have two sub-categories "name = first, count_products = 0", "name = second, count_products = 0". When added to the products table 3 lines: "name = pr1, price = 1, id_product_subcategories = 1", "name = np2, price = 2, id_product_subcategories = 1", "name = PR3, price = 5 , id_product_subcategories = 2"

Table subcategories should look

"name = first, count_products = 2", "name = second, count_products = 1"

Here's what I wrote, but for some reason he does not work for one line, and I do not understand how to do, when you add a few lines as a walk through each row in the inserted table?

CREATE TRIGGER 
    countproductscategories
ON
    dbo.products
AFTER
    INSERT
AS
IF @@ROWCOUNT = 1
BEGIN
    UPDATE dbo.product_subcategories
    SET count_products = count_products + 1
    WHERE dbo.product_subcategories.id = (SELECT id FROM inserted)
END;

Gordon Linoff · Accepted Answer · 2014-12-05 04:56:39Z

1

What is the @@ROWCOUNT = 1? This should do what you want:

CREATE TRIGGER 
    countproductscategories
ON dbo.products AFTER INSERT
AS
BEGIN
    UPDATE dbo.product_subcategories
        SET count_products = count_products + 1
        WHERE dbo.product_subcategories.id IN (SELECT id FROM inserted)
END;

If you want to do this when only one row is inserted, then you can do:

CREATE TRIGGER 
    countproductscategories
ON dbo.products AFTER INSERT
AS
BEGIN
    UPDATE dbo.product_subcategories
        SET count_products = count_products + 1
        WHERE dbo.product_subcategories.id IN (SELECT id FROM inserted) AND
              (SELECT COUNT(*) FROM inserted) = 1;
END;

answered Dec 5, 2014 at 4:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server : Trigger after insert, delete

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related