December 10, 2012 at 4:34 am
Hello,
I have a question about creating a trigger that works set based. I have no clue how I can
check if a recipe has minimal 2 ingredients, if not Raiserror. Please give me a pointer, complete solution not required. I may not use Cursors to solve the problem.
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
December 10, 2012 at 4:39 am
Is this a homework question by any chance? Also what is the rules behind using a trigger, why not a stored procedure etc? Also what happens should you upload 1 ingredient at a time to a recipe? As that recipe only then has 1 ingredient it will cause the trigger to fire or do you support multiple inserts inside the same insert statement?
December 10, 2012 at 4:55 am
I am trying to solve possible constraints that my coming test at my study might ask, that could be implemented with Trigger, check constraints or stored procedures.
Your question: Also what is the rules behind using a trigger, why not a stored procedure etc?
I need to know everything, eventually I need to be able to solve the business rule with Stored procedures , tiggers and if possible with a check constraint. But for now I am trying to solve it with a trigger. Trigger is a good solution I think. Then the constraint is set at the table itself, whatever way you find to add or remove data, this trigger will fire if the business rule is violated. With a stored procedure is also possible but I need to prevent people to access and modify the database directly.
A stored procedure does not protect if it's not used....
Your question: As that recipe only then has 1 ingredient it will cause the trigger to fire or do you support multiple inserts inside the same insert statement?
Any insertion / deletion of a row that causes the recipe to remain with 1 or less ingredients must fire a raiserror. Multiple inserts are supported of course, but must be within an transaction of course.
anthony.green (12/10/2012)
Is this a homework question by any chance? Also what is the rules behind using a trigger, why not a stored procedure etc? Also what happens should you upload 1 ingredient at a time to a recipe? As that recipe only then has 1 ingredient it will cause the trigger to fire or do you support multiple inserts inside the same insert statement?
December 10, 2012 at 5:02 am
You will simply just need to query the ingredients table for a sum of the ingredients for the particular recipe that has just been inserted / updated / deleted by using the inserted and deleted tables which are persisted when using triggers.
Then if the SUM < 2 drop into a RAISERROR block.
December 10, 2012 at 5:12 am
I started with a small idea. I know how to check for one row, if 1 row is inserted, but I don't know
how to do it for severel sets. Because it plausible that 2 recipes are inserted at the same time.
I had made this so far, it's far from complete, but it does show I think the same as you:
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
But I am stuck at the point you ask: " .... for the particular recipe "
Does that mean I have to use a while loop to get past every row of data and do COUNT then
compare, if more then 1 rows of ingredients, partially INSERT that set of data, then continue?
Edit: looks like i need 1trigger on ingredients..... not on recipes..... big mistake....no...wait I think I need 2 triggers.... it's also possible that a recipe is inserted with ingredients, but then deleted.
anthony.green (12/10/2012)
You will simply just need to query the ingredients table for a sum of the ingredients for the particular recipe that has just been inserted / updated / deleted by using the inserted and deleted tables which are persisted when using triggers.Then if the SUM < 2 drop into a RAISERROR block.
December 10, 2012 at 5:18 am
INSERTED / DELETED are persisted tables based on the definition of the table you have defined the trigger on, so it will mimic the recipes table.
Personally I would have put the trigger on the ingredients table as that is the table your more bothered about checking.
Then just use inserted as a join to ingredients and recipes.
December 10, 2012 at 7:27 am
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
-- test 1
-- Expected result: Raiserror, because the number of ingredients is lower then 2.
BEGIN TRAN
INSERT INTO recipes ([recipeNumber],[recipe])
VALUES (3,'Cook the egg for 6 minutes, apply salt & pepper as you like')
INSERT INTO ingredients ([recipeNumber],[name],[amount])
VALUES(3,'egg','1 piece')
ROLLBACK TRAN
-- Test 2
-- Expected result: Raiserror
INSERT INTO recipes ([recipeNumber],[recipe])
VALUES (3,'Cook the egg for 6 minutes, apply salt & pepper as you like')
INSERT INTO ingredients ([recipeNumber],[name],[amount])
VALUES(3,'egg','1 piece')
-- reset test
DELETE FROM ingredients
WHERE name = 'egg'
DELETE FROM recipes
WHERE recipe = 'Cook the egg for 6 minutes, apply salt & pepper as you like'
I find it hard to used INSERTED / DELETED table, a collogue suggested GROUP BY / HAVING .
This code I can understand and apply. But after making 2 tests, I find there is a issue.
Test 2 has the expected results, that's good.
Test 1 should fail, but it succeeds, because I was in an Transaction already. Why is that?
A trigger is always part of the transaction right? The whole batch of inserts should fail or succeed based upon the check in the trigger above....why does it succeed?? :crazy:
Thanks so far!
December 10, 2012 at 7:35 am
The trigger will fire at the transaction scope and the scope of the transaction is to insert two records which will not meet the criteria of the trigger and not cause the trigger to fire.
The second one is actually two transactions hence why that will fail.
December 10, 2012 at 8:35 am
So if I say " the trigger will fire when a record is added or removed from the table ingredient" is not 100% correct. Because it depends on what's inside the 'transaction' will determine if the trigger fires or not.
There are 2 possible solutions I can think of :
1) Alter the behavior of the trigger, make it fire if the table it is assigned to, data is deleted or inserted.
And does not look at the transaction scope as a whole... (not sure if this is possible or wise)
2) Use Stored procedure and put my business logic in there.
Good ideas? Or flawed? and why?
Thanks Antony, been really informative whole the way.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply