USE MASTER
DROP DATABASE test
GO
CREATE DATABASE Test
GO
USE Test
GO
CREATE TABLE recipes
(
  recipeNumber INT PRIMARY KEY, recipe VARCHAR(255)
)

CREATE  TABLE ingredients
 (recipeNumber INT ,name VARCHAR(255),amount VARCHAR(255))

ALTER TABLE ingredients
  ADD CONSTRAINT FK_ingredients_to_recipes
    FOREIGN KEY (recipeNumber) REFERENCES recipes(recipeNumber) 

    
INSERT INTO recipes ([recipeNumber],[recipe])
  VALUES (1,'Cook the patatoes, cut them into long thin pieces, the fry them till gold brown')

INSERT INTO ingredients ([recipeNumber],[name],[amount])
  VALUES	(1,'patatoes','2 kg'),
			(1,'vegtable oil','5 liter'),
			(1,'salt','1 spoon')
/code

CREATE TRIGGER trg_minimalNumberIngredientsForRecipe ON recipes
AFTER DELETE,INSERT,UPDATE
AS
BEGIN
  DECLARE @count int = 0;
  IF EXISTS
  ( 
    SELECT 1 WHERE  
    (
      @count = SELECT COUNT(*) FROM INSERTED INNER 
    ) < 2
  
  )

END

CREATE TRIGGER trg_minimalNumberIngredientsForRecipe ON ingredients
AFTER DELETE,INSERT
AS
BEGIN
  IF EXISTS 
  (
    SELECT recipeNumber 
    FROM ingredients 
    GROUP BY recipeNumber
    HAVING COUNT(name) < 2
  )
  BEGIN
    ROLLBACK TRAN
    RAISERROR('error: a recipe must have at least 2 ingredients.',16,1)
    RETURN
  END
END