Author |
Topic |
chihwahli
Starting Member
11 Posts |
Posted - 2013-04-01 : 09:22:22
|
I have the following that I need help with. I made a small tmp database and the product may not be deleted if there it has 2 ingredients. Why does my trigger fire even when I delete all rows of the product tosti?CREATE TABLE product ( name VARCHAR(15), ingridient VARCHAR(15))CREATE TABLE ingredient ( name VARCHAR(15) NOT NULL)ALTER TABLE ingredient ADD CONSTRAINT PK_ingredient PRIMARY KEY (name);ALTER TABLE product ADD CONSTRAINT FK_product_REF_ingridient FOREIGN KEY (ingridient) REFERENCES ingredient(name);INSERT INTO ingredient ([name]) VALUES ('tomato'),('bread'),('cheese'),('ham'),('onion'),('butter'), ('flour'),('egg'),('garlic'),('patatoe'),('salt')INSERT INTO product ([name],[ingridient]) VALUES ('tosti','bread'), ('tosti','cheese'), ('tosti','ham')GOALTER TRIGGER articleMustHave2Ingridients ON productAFTER DELETEASBEGIN IF EXISTS ( SELECT name FROM product GROUP BY name HAVING COUNT(*) BETWEEN 1 AND 3) BEGIN ROLLBACK TRAN ENDENDGO-- Test 1: delete 1 of 3 ingridientsDELETE FROM product WHERE ingridient = 'ham'-- Test 2: delete 2 of 3 ingridientsDELETE FROM product WHERE ingridient = 'ham'DELETE FROM product WHERE ingridient = 'bread'-- Test 2: delete 2 of 3 ingridientsDELETE FROM product WHERE ingridient = 'ham'DELETE FROM product WHERE ingridient = 'bread'DELETE FROM product WHERE ingridient = 'cheese' |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-01 : 09:53:50
|
i think it should beALTER TRIGGER articleMustHave2Ingridients ON productAFTER DELETEASBEGIN IF EXISTS ( SELECT ingredient FROM product GROUP BY ingredient HAVING COUNT(*) BETWEEN 1 AND 3) BEGIN ROLLBACK TRAN ENDENDGO as per your explanation------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
chihwahli
Starting Member
11 Posts |
Posted - 2013-04-01 : 10:11:37
|
I changed the code but I still cannot delete my product tosti.I don't understand why the trigger does not allow:I select all 3 then execute:DELETE FROM product WHERE ingridient = 'ham'DELETE FROM product WHERE ingridient = 'bread'DELETE FROM product WHERE ingridient = 'cheese'also this does not help:BEGIN TRANDELETE FROM product WHERE ingridient = 'ham'DELETE FROM product WHERE ingridient = 'bread'DELETE FROM product WHERE ingridient = 'cheese'commit tranThe trigger still fires then.... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-01 : 10:25:02
|
the trigger will fire always, but it will only do rollback if it has 1-3 ingredients------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-04-01 : 10:28:19
|
If I manipulate the trigger .. it says something like Upon deletion, check if a product "name" has rows between 1 and 3 then rollback the transaction. Check the records and you'll see that it has three rcords for "tosti" - which fulfills the condition of rows between 1 and 3, and thus trigger is fired.CheersMIK |
|
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-04-01 : 10:36:56
|
I'm not entirely sure what you mean by 2 ingredients? There is only one field for ingredients in the Product column? Or are you doing it in a way that if your product appears more than once it has more than one ingredient?The IF exist in your trigger will always return a value if that is the case and there for always fire off a trigger...If you run it withDELETE FROM product WHERE ingredient = 'ham'DELETE FROM product WHERE ingredient = 'bread'SQL will see that as to separate statements and the trigger will fire as only one line is being deleted at a time...I think you need to redesign your table or create a relationship table for this to work. If you can explain how you are looking at something as having to ingredients then perhaps I can help... |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-04-01 : 10:40:16
|
well well.. I think there is something that I don't know and would like to understand as well. Further explanation to the issue (to clarify OP question)@visakh correct tigger will fire always but don't know of a reaosn why is it rollbacking the transaction if there are four records in product table .. taking the same example as OP mentioned INSERT INTO product ([name],[ingridient]) VALUES ('tosti','bread'), ('tosti','cheese'), ('tosti','ham'), ('tosti','egg');Then create this trigger on productCREATE TRIGGER articleMustHave2Ingridients ON productAFTER DELETEASBEGIN IF EXISTS ( SELECT name FROM product GROUP BY name HAVING COUNT(*) BETWEEN 1 AND 3) BEGIN ROLLBACK TRAN ENDENDGOWith that, I belive executing following delete should have processedDELETE FROM product WHERE ingridient = 'ham'As if i execute only the SELECT query inside the "If exists" it is giving null result since count(*)=4, so the rollback should not be executed. But I checked and it still executed. CheersMIK |
|
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-04-01 : 10:58:32
|
I don't understand how the select is working for you from within the IF EXIST as using the data you provided it always returns tosti for me.If you rewrite it to beSELECT count(name)FROM productGROUP BY nameHAVING COUNT(name) > 1 AND COUNT(name) < 3 Might be that the db I'm using is set to look at between as >= and <= and that's why it is returning. |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-04-01 : 14:07:15
|
The BETWEEN operator is inclusive by definition. "COUNT(name) BETWEEN 1 and 3" is functionaly equivalent to "COUNT(name) >= 1 and COUNT(name) <= 3". There is no configuration option to change this.=================================================There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber |
|
|
chihwahli
Starting Member
11 Posts |
Posted - 2013-04-01 : 14:25:22
|
I am using SQL server 2012 developer.Perhaps I should have given the products table another name like: productrequirements.If I use HAVING COUNT(*) = 1 )then all goes well until I try to delete the last 2 rows. There must be a way to create the business rule: A product must have 2 or more ingredients. A product can be deleted entirely.But how?? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-01 : 14:35:24
|
quote: Originally posted by chihwahli I am using SQL server 2012 developer.Perhaps I should have given the products table another name like: productrequirements.If I use HAVING COUNT(*) = 1 )then all goes well until I try to delete the last 2 rows. There must be a way to create the business rule: A product must have 2 or more ingredients. A product can be deleted entirely.But how??
you mean allow deletion only if product has 2 or more ingredients?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
chihwahli
Starting Member
11 Posts |
Posted - 2013-04-01 : 14:53:15
|
What is allowed:Table product with 2 rows or more:tosti cheesetosti hamWhat also is allowed: delete all rows of tosti. Rollback must not execute.The trigger should ROLLBACK, if one row with any product stays in the table product.It should only trigger, if for example ,the following remains in the table after deleting one or more rows:tosti hamReason: business rule says, products with one ingredient not allowed. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-01 : 15:30:09
|
try like this and the try your deletesAvoid that transaction inside trigger altogetherCREATE TRIGGER articleMustHave2Ingridients ON productINSTEAD OF DELETEASBEGINIF EXISTS ( SELECT 1FROM product pJOIN deleted dON d.name = p.nameGROUP BY p.nameHAVING COUNT(*) > 2)BEGINDELETE pFROM Product pINNER JOIN deleted dON d.name = p.nameAND d.ingridient = p.ingridientENDEND ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
chihwahli
Starting Member
11 Posts |
Posted - 2013-04-01 : 18:22:30
|
@Visakh16: I tried your version, but it does not delete the last 2 entries....I tried my new version, but it does not let me delete the last 2 also:ALTER TRIGGER articleMustHave2Ingridients ON productAFTER DELETEASBEGIN IF EXISTS ( SELECT 1 FROM product WHERE name = (SELECT name FROM deleted ) GROUP BY name HAVING COUNT(name) < 2) ROLLBACK TRANENDI am wondering: after using the delete statement that delete all rows with 'tosti', my table product should be empty right?Then why does the trigger still rollback??? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-02 : 01:22:24
|
why cant you use as suggested. This is not what i sugested. I'm using INSTEAD OF trigger and what you've here is an after trigger------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
chihwahli
Starting Member
11 Posts |
Posted - 2013-04-02 : 05:33:45
|
CREATE TRIGGER articleMustHave2Ingridients ON productINSTEAD OF DELETEASBEGINIF EXISTS ( SELECT 1FROM product pJOIN deleted dON d.name = p.nameGROUP BY p.nameHAVING COUNT(*) > 2)BEGINDELETE pFROM Product pINNER JOIN deleted dON d.name = p.nameAND d.ingridient = p.ingridientENDEND-- I tried your version... it does not delete.the last 2 entries , it will say in SQL manager console:"(1 row(s) affected)", but checking with "SELECT * FROM product" the row is still there. |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-04-02 : 05:49:12
|
Well your requirements are not clear enough... Answer the below three questions for following queriesDELETE FROM product WHERE ingridient = 'ham'DELETE FROM product WHERE ingridient = 'bread'DELETE FROM product WHERE ingridient = 'cheese'Executing the first one .. should it work? Then executing the second one.. should it work? If not why? Then executing the third one.. should it work? If not why?CheersMIK |
|
|
chihwahli
Starting Member
11 Posts |
Posted - 2013-04-02 : 06:14:29
|
@ MIK 2008:execute: DELETE FROM product WHERE ingridient = 'ham'Required Results: yes it must be deletedExecute: DELETE FROM product WHERE ingridient = 'bread'Required Results: must not be deleted, because a product must have 2 ingredientsExecute: DELETE FROM product WHERE ingridient = 'bread' DELETE FROM product WHERE ingridient = 'cheese'Required Results: The remaining 2 rows with product 'tosti' must be deleted, because I the user want this product to be removed completely.Extra requirements:- Products with more then 2 ingredients must stay of course, because they have 2 or more ingredients.- Products will be deleted only If I manually delete all remaining rows.- If user deleted only one product row, and the product has 2 rows in total in table Product, then rollback or leave the 2 rows alone, but message user that it may not be deleted, because it's a violation of the business rule : "products must have at least 2 ingredients" Thanks helping... I hope that it is clear now, if it's not clear ask please. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-02 : 07:30:02
|
quote: Originally posted by chihwahli @ MIK 2008:execute: DELETE FROM product WHERE ingridient = 'ham'Required Results: yes it must be deletedExecute: DELETE FROM product WHERE ingridient = 'bread'Required Results: must not be deleted, because a product must have 2 ingredientsExecute: DELETE FROM product WHERE ingridient = 'bread' DELETE FROM product WHERE ingridient = 'cheese'Required Results: The remaining 2 rows with product 'tosti' must be deleted, because I the user want this product to be removed completely.Extra requirements:- Products with more then 2 ingredients must stay of course, because they have 2 or more ingredients.- Products will be deleted only If I manually delete all remaining rows.- If user deleted only one product row, and the product has 2 rows in total in table Product, then rollback or leave the 2 rows alone, but message user that it may not be deleted, because it's a violation of the business rule : "products must have at least 2 ingredients" Thanks helping... I hope that it is clear now, if it's not clear ask please.
this will still be executed as two separate statements unless you enclose them in a transaction------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-04-02 : 07:48:50
|
What I have understood from your requirements, are:1) A product must have at least two records(ingredients) in the table. But, there are two types of users say a)"General User" b) "SuperUser"a) General user can delete records from Product table as far as Rule1 is intact - General User cannot by pass rule 1 (business logic). b) SuperUser can delete all records from product, irrespective of rule 1 (business logic) Is that so?CheersMIK |
|
|
chihwahli
Starting Member
11 Posts |
Posted - 2013-04-02 : 09:50:40
|
@MIK_2008: it's not required to create different behaviors, just 1 user.@Visakh16: I tried the following as you suggested:BEGIN TRANDELETE FROM product WHERE ingridient = 'bread' AND name = 'tosti'DELETE FROM product WHERE ingridient = 'cheese' AND name = 'tosti'COMMIT TRANSELECT * FROM productBut it won't delete. Why?? I used all above Trigger codes, that you wrote and I wrote... What am I missing?? |
|
|
Next Page
|