CODEWITHSUNDEEP
sqlsql-server
Roman Kotov
Roman Kotov

Reputation: 9

SQL Server : Trigger after insert, delete

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

I have 2 tables product_subcategories and Products.

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;

Upvotes: 0

Views: 66

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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;

Upvotes: 1

Related Questions