SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Trg prevent deletion if....
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

chihwahli
Starting Member

11 Posts

Posted - 04/01/2013 :  09:22:22  Show Profile  Reply with Quote

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')

GO

ALTER TRIGGER articleMustHave2Ingridients ON product
AFTER DELETE
AS
BEGIN
  IF EXISTS (	SELECT name
			FROM product
			GROUP BY name
			HAVING COUNT(*) BETWEEN 1 AND 3)
  BEGIN
    ROLLBACK TRAN
  END
END
GO

-- Test 1: delete 1 of 3 ingridients
DELETE FROM product WHERE ingridient = 'ham'

-- Test 2: delete 2 of 3 ingridients
DELETE FROM product WHERE ingridient = 'ham'
DELETE FROM product WHERE ingridient = 'bread'

-- Test 2: delete 2 of 3 ingridients
DELETE FROM product WHERE ingridient = 'ham'
DELETE FROM product WHERE ingridient = 'bread'
DELETE FROM product WHERE ingridient = 'cheese'

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/01/2013 :  09:53:50  Show Profile  Reply with Quote
i think it should be

ALTER TRIGGER articleMustHave2Ingridients ON product
AFTER DELETE
AS
BEGIN
  IF EXISTS (	SELECT ingredient
			FROM product
			GROUP BY ingredient
			HAVING COUNT(*) BETWEEN 1 AND 3)
  BEGIN
    ROLLBACK TRAN
  END
END
GO

as per your explanation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

chihwahli
Starting Member

11 Posts

Posted - 04/01/2013 :  10:11:37  Show Profile  Reply with Quote
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 TRAN
DELETE FROM product WHERE ingridient = 'ham'
DELETE FROM product WHERE ingridient = 'bread'
DELETE FROM product WHERE ingridient = 'cheese'
commit tran

The trigger still fires then....

Edited by - chihwahli on 04/01/2013 10:13:26
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/01/2013 :  10:25:02  Show Profile  Reply with Quote
the trigger will fire always, but it will only do rollback if it has 1-3 ingredients

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/01/2013 :  10:28:19  Show Profile  Reply with Quote
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.

Cheers
MIK
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 04/01/2013 :  10:36:56  Show Profile  Reply with Quote
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 with

DELETE 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...
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/01/2013 :  10:40:16  Show Profile  Reply with Quote
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 product

CREATE TRIGGER articleMustHave2Ingridients ON product
AFTER DELETE
AS
BEGIN
IF EXISTS ( SELECT name
FROM product
GROUP BY name
HAVING COUNT(*) BETWEEN 1 AND 3)
BEGIN
ROLLBACK TRAN
END
END
GO

With that, I belive executing following delete should have processed

DELETE 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.


Cheers
MIK

Edited by - MIK_2008 on 04/01/2013 10:42:33
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 04/01/2013 :  10:58:32  Show Profile  Reply with Quote
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 be


SELECT 
	count(name)
FROM 
	product
GROUP BY 
	name
HAVING 
	 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.


Edited by - Robowski on 04/01/2013 11:07:57
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 04/01/2013 :  14:07:15  Show Profile  Reply with Quote
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
Go to Top of Page

chihwahli
Starting Member

11 Posts

Posted - 04/01/2013 :  14:25:22  Show Profile  Reply with Quote
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??
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/01/2013 :  14:35:24  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

chihwahli
Starting Member

11 Posts

Posted - 04/01/2013 :  14:53:15  Show Profile  Reply with Quote
What is allowed:

Table product with 2 rows or more:
tosti cheese
tosti ham

What 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 ham

Reason: business rule says, products with one ingredient not allowed.


Edited by - chihwahli on 04/01/2013 14:55:58
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/01/2013 :  15:30:09  Show Profile  Reply with Quote
try like this and the try your deletes
Avoid that transaction inside trigger altogether


CREATE TRIGGER articleMustHave2Ingridients 
ON product
INSTEAD OF DELETE
AS
BEGIN
IF EXISTS (	
SELECT 1
FROM product p
JOIN deleted d
ON d.name = p.name
GROUP BY p.name
HAVING COUNT(*) > 2)
BEGIN
DELETE p
FROM Product p
INNER JOIN deleted d
ON d.name = p.name
AND d.ingridient = p.ingridient
END
END



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

chihwahli
Starting Member

11 Posts

Posted - 04/01/2013 :  18:22:30  Show Profile  Reply with Quote

@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 product
AFTER DELETE
AS
BEGIN
IF EXISTS ( SELECT 1
FROM product
WHERE name = (SELECT name FROM deleted )
GROUP BY name
HAVING COUNT(name) < 2)
ROLLBACK TRAN
END

I 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???
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/02/2013 :  01:22:24  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

chihwahli
Starting Member

11 Posts

Posted - 04/02/2013 :  05:33:45  Show Profile  Reply with Quote
CREATE TRIGGER articleMustHave2Ingridients
ON product
INSTEAD OF DELETE
AS
BEGIN
IF EXISTS (
SELECT 1
FROM product p
JOIN deleted d
ON d.name = p.name
GROUP BY p.name
HAVING COUNT(*) > 2)
BEGIN
DELETE p
FROM Product p
INNER JOIN deleted d
ON d.name = p.name
AND d.ingridient = p.ingridient
END
END

-- 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.
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/02/2013 :  05:49:12  Show Profile  Reply with Quote
Well your requirements are not clear enough... Answer the below three questions for following queries

DELETE 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?

Cheers
MIK
Go to Top of Page

chihwahli
Starting Member

11 Posts

Posted - 04/02/2013 :  06:14:29  Show Profile  Reply with Quote
@ MIK 2008:

execute: DELETE FROM product WHERE ingridient = 'ham'
Required Results: yes it must be deleted

Execute: DELETE FROM product WHERE ingridient = 'bread'
Required Results: must not be deleted, because a product must have 2 ingredients

Execute: 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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/02/2013 :  07:30:02  Show Profile  Reply with Quote
quote:
Originally posted by chihwahli

@ MIK 2008:

execute: DELETE FROM product WHERE ingridient = 'ham'
Required Results: yes it must be deleted

Execute: DELETE FROM product WHERE ingridient = 'bread'
Required Results: must not be deleted, because a product must have 2 ingredients

Execute: 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/02/2013 :  07:48:50  Show Profile  Reply with Quote
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?

Cheers
MIK
Go to Top of Page

chihwahli
Starting Member

11 Posts

Posted - 04/02/2013 :  09:50:40  Show Profile  Reply with Quote
@MIK_2008: it's not required to create different behaviors, just 1 user.

@Visakh16: I tried the following as you suggested:
BEGIN TRAN
DELETE FROM product WHERE ingridient = 'bread' AND name = 'tosti'
DELETE FROM product WHERE ingridient = 'cheese' AND name = 'tosti'
COMMIT TRAN


SELECT * FROM product

But it won't delete. Why?? I used all above Trigger codes, that you wrote and I wrote... What am I missing??
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 1.06 seconds. Powered By: Snitz Forums 2000