Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Trg prevent deletion if....

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

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

52326 Posts

Posted - 2013-04-01 : 09:53:50
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 - 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 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....
Go to Top of Page

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

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.

Cheers
MIK
Go to Top of Page

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

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

Go to Top of Page

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

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

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

chihwahli
Starting Member

11 Posts

Posted - 2013-04-01 : 14:53:15
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.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 15:30:09
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 - 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 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

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

chihwahli
Starting Member

11 Posts

Posted - 2013-04-02 : 05:33:45
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
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 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 - 2013-04-02 : 06:14:29
@ 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

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

Cheers
MIK
Go to Top of Page

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 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
  Previous Page&nsp;  Next Page

- Advertisement -