I want delete trigger to be fired

Question

0.00/5 (No votes)

See more:

create table instock
(
storeid smallint not null,
wineid smallint not null,
quantity smallint not null,
primary key (storeid, wineid),
foreign key (storeid) references stores,
foreign key (wineid) references wines
)
create table bigstores
(
storeid smallint not null,
city char(20) not null,
phone char(10) not null,
primary key (storeid)
)
IF EXISTS ( SELECT Name FROM sysobjects
WHERE Name = 'TRI_deleteINSTOCK' AND Type = 'P' )
DROP trigger TRI_DELETEINSTOCK
GO
CREATE TRIGGER TRI_deleteINSTOCK
ON instock
FOR delete
AS
BEGIN
DECLARE
@STOREID SMALLINT,
@WINEID SMALLINT,
@TOTALQUANTITY INT
SELECT @STOREID = d.STOREID FROM deleted d
SELECT @wineid = d.wineid from deleted d

SELECT @TOTALQUANTITY = SUM(QUANTITY)
FROM INSTOCK
WHERE STOREID =@STOREID

DELETE FROM INSTOCK
WHERE storeid =@STOREID AND wineid =@WINEID
IF NOT EXISTS(SELECT storeid FROM bigstores WHERE storeid =@storeid )
BEGIN
DELETE FROM bigstores
where StoreId = @StoreId AND @totalquantity <= 50000
END
END

Posted 20-Jan-15 20:21pm

Member 11387088

Add a Solution

Comments

Deepu S Nair 21-Jan-15 2:29am

What you tried so far?

Member 11387088 21-Jan-15 2:32am

i have created a delete trigger and if i delete values in one table means it should be fired in another table and i've mentioned the quantity if the quantity is lesser than 50000 means it willnot be triggered

Sinisa Hajnal 21-Jan-15 2:45am

The trigger never "fires on another table" - trigger by definition reacts to events on the table it is defined on. However, you can delete from other tables. What is the problem? Do you get an error from your trigger or it behaves unexpectedly?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 11387088 · Accepted Answer · 2015-01-20T20:40:00

IF EXISTS ( SELECT Name FROM sysobjects
WHERE Name = 'TRI_deleteINSTOCK' AND Type = 'P' )
DROP trigger TRI_DELETEINSTOCK
GO
CREATE TRIGGER TRI_deleteINSTOCK
ON instock
FOR delete
AS
BEGIN
DECLARE
@STOREID SMALLINT,
@WINEID SMALLINT,
@TOTALQUANTITY INT
SELECT @STOREID = d.STOREID FROM deleted d
SELECT @wineid = d.wineid from deleted d

SELECT @TOTALQUANTITY = SUM(QUANTITY)
FROM INSTOCK
WHERE STOREID =@STOREID

DELETE FROM bigstores
WHERE storeid =@STOREID
IF NOT EXISTS(SELECT storeid FROM bigstores WHERE storeid =@storeid )
BEGIN
DELETE FROM bigstores
where StoreId = @StoreId AND @totalquantity <= 50000
END
END