delete trigger for multiple row deletes

I'm racking my brain trying to figure this one out and I have a feeling it's
simpler than I'm making it out to be. I need a trigger that will prevent
delete of certain rows but not all. For example, I have a customer table:
CustID INT (primary key, auto inc)
CustName VARCHAR(50)
and an orders table:
OrderID INT (primary key, auto inc)
CustID INT (FK to other table of course)
ItemOrdered VARCHAR(100)
Here would be the data:
Customer table:
CustID CustName
1 Jones Lumber
2 Smith's Cars
3 Helen's Carpet
Orders table:
OrderID CustID ItemOrdered
1 1 2x4's
2 1 Dowel
3 3 Berber
4 3 Shag
Notice that Smith's Cars is not in the orders table at all.
Now I select all the rows in the customer table and try to delete them. I'd
like the trigger to prevent the delete of any customer that has orders but
allow the others to be deleted. So in this case Jones Lumber and Helen's
Carpet would remain and Smith's Cars would be deleted.
The trouble I'm having is that if I set up a cursor in the trigger to
sequence through the customers, how do I rollback just some of the deletes
but not all and put up an appropriate message?
DECLARE
@.iCustID AS INT,
@.sMsg AS VARCHAR(500)
SET NOCOUNT ON
SET @.sMsg = ''
DECLARE curCustomers CURSOR FOR SELECT C.CustID FROM DELETED C
OPEN curCustomers
FETCH NEXT FROM curCustomers INTO @.iCustID
IF @.@.FETCH_STATUS = 0
BEGIN
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(*) FROM tblOrders WHERE CustID = @.iCustID) > 0
BEGIN
ROLLBACK TRANSACTION
SET @.sMsg = 'Customers with orders cannot be deleted.'
END
FETCH NEXT FROM curCustomers INTO @.iCustID
END
END
IF @.sMsg <> ''
RAISERROR (@.sMsg, 16, 1)
CLOSE curCustomers
DEALLOCATE curCustomers
===
My original trigger looked like this until I realized it was rolling back
the whole thing without even attempting to test for rows that should be
allowed to be deleted:
IF (SELECT COUNT(*) FROM tblOrders WHERE CustID IN (SELECT CustID FROM
DELETED)) > 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Customers with orders cannot be deleted.', 16, 1)
END
Thanks,
KeithKeith G Hicks wrote:
> I'm racking my brain trying to figure this one out and I have a feeling it
's
> simpler than I'm making it out to be. I need a trigger that will prevent
> delete of certain rows but not all. For example, I have a customer table:
> CustID INT (primary key, auto inc)
> CustName VARCHAR(50)
> and an orders table:
> OrderID INT (primary key, auto inc)
> CustID INT (FK to other table of course)
> ItemOrdered VARCHAR(100)
> Here would be the data:
> Customer table:
> CustID CustName
> 1 Jones Lumber
> 2 Smith's Cars
> 3 Helen's Carpet
> Orders table:
> OrderID CustID ItemOrdered
> 1 1 2x4's
> 2 1 Dowel
> 3 3 Berber
> 4 3 Shag
> Notice that Smith's Cars is not in the orders table at all.
> Now I select all the rows in the customer table and try to delete them. I'
d
> like the trigger to prevent the delete of any customer that has orders but
> allow the others to be deleted. So in this case Jones Lumber and Helen's
> Carpet would remain and Smith's Cars would be deleted.
> The trouble I'm having is that if I set up a cursor in the trigger to
> sequence through the customers, how do I rollback just some of the deletes
> but not all and put up an appropriate message?
> DECLARE
> @.iCustID AS INT,
> @.sMsg AS VARCHAR(500)
> SET NOCOUNT ON
> SET @.sMsg = ''
> DECLARE curCustomers CURSOR FOR SELECT C.CustID FROM DELETED C
> OPEN curCustomers
> FETCH NEXT FROM curCustomers INTO @.iCustID
> IF @.@.FETCH_STATUS = 0
> BEGIN
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (SELECT COUNT(*) FROM tblOrders WHERE CustID = @.iCustID) > 0
> BEGIN
> ROLLBACK TRANSACTION
> SET @.sMsg = 'Customers with orders cannot be deleted.'
> END
> FETCH NEXT FROM curCustomers INTO @.iCustID
> END
> END
> IF @.sMsg <> ''
> RAISERROR (@.sMsg, 16, 1)
> CLOSE curCustomers
> DEALLOCATE curCustomers
>
> ===
> My original trigger looked like this until I realized it was rolling back
> the whole thing without even attempting to test for rows that should be
> allowed to be deleted:
> IF (SELECT COUNT(*) FROM tblOrders WHERE CustID IN (SELECT CustID FROM
> DELETED)) > 0
> BEGIN
> ROLLBACK TRANSACTION
> RAISERROR ('Customers with orders cannot be deleted.', 16, 1)
> END
> Thanks,
> Keith
Do not use cursors in triggers (or anywhere else you don't have to for
that matter).
I've included a solution below but I don't really see the point. Why
not just modify your original DELETE statement to exclude the
referenced rows using the NOT EXISTS check? The foreign key keeps you
safe from accidental deletes.
CREATE TRIGGER trg_customer
ON customer INSTEAD OF DELETE
AS
IF EXISTS
(SELECT *
FROM deleted
WHERE EXISTS
(SELECT *
FROM orders
WHERE custid = deleted.custid))
RAISERROR ('Customers with orders cannot be deleted.', 16, 1);
DELETE FROM customer
WHERE EXISTS
(SELECT *
FROM deleted
WHERE custid = customer.custid)
AND NOT EXISTS
(SELECT *
FROM orders
WHERE custid = customer.custid);
GO
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Ok so just so I understand your strategy, a rollback is not necessary since
you doing INSETAD OF and are putting up a warning if there are any rows that
cannot be deleted and then actually performing the delete on the allowed
rows within the trigger?
The foreign key won't help me here. The actual project is slightly more
complex but my sample got the point across and your solution should do the
trick nicely. Very much appreciated.
What's the reason for avoiding cursors? Is it a performance issue or are
there other reasons?
Thanks again,
Keith
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1140822038.662268.199890@.e56g2000cwe.googlegroups.com...
Do not use cursors in triggers (or anywhere else you don't have to for
that matter).
I've included a solution below but I don't really see the point. Why
not just modify your original DELETE statement to exclude the
referenced rows using the NOT EXISTS check? The foreign key keeps you
safe from accidental deletes.
CREATE TRIGGER trg_customer
ON customer INSTEAD OF DELETE
AS
IF EXISTS
(SELECT *
FROM deleted
WHERE EXISTS
(SELECT *
FROM orders
WHERE custid = deleted.custid))
RAISERROR ('Customers with orders cannot be deleted.', 16, 1);
DELETE FROM customer
WHERE EXISTS
(SELECT *
FROM deleted
WHERE custid = customer.custid)
AND NOT EXISTS
(SELECT *
FROM orders
WHERE custid = customer.custid);
GO
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I implemented your suggestion and it works fine if I swap the sections so
that the test for Raiserror is happening after the delete is actually done.
Otherwise nothing deletes for me if it's done in the order you have below.
Can you explain why?
Thanks,
Keith
CREATE TRIGGER trg_customer
ON customer INSTEAD OF DELETE
AS
DELETE FROM customer
WHERE EXISTS
(SELECT *
FROM deleted
WHERE custid = customer.custid)
AND NOT EXISTS
(SELECT *
FROM orders
WHERE custid = customer.custid);
IF EXISTS
(SELECT *
FROM deleted
WHERE EXISTS
(SELECT *
FROM orders
WHERE custid = deleted.custid))
RAISERROR ('Customers with orders cannot be deleted.', 16, 1);
GO
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Never mind on this question. It works fine either way.
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:e6xNt2ZOGHA.2604@.TK2MSFTNGP09.phx.gbl...
I implemented your suggestion and it works fine if I swap the sections so
that the test for Raiserror is happening after the delete is actually done.
Otherwise nothing deletes for me if it's done in the order you have below.
Can you explain why?
Thanks,
Keith|||There are several reasons to avoid cursors. Set-based operations generally
perform better: heap and index maintenance can be optimized, and transaction
logging is minimized. In addition, triggers fire only once for a set-based
operation, whereas they fire once per iteration for a row-based operation.
But these aren't the only reasons. Cursors reduce the effective isolation
level to REPEATABLE READ if a transaction is outstanding or to READ
COMMITTED if one isn't. Also, if a transaction is outstanding and
modifications occur within the fetch loop, the probability of deadlocks
increases significantly. You should know that a transaction is always
outstanding within the body of a trigger. While there are rare occasions
where a cursor will improve performance, all of the above factors must be
taken into account before converting a set-based solution into a row-based
one. An important note: you should always attempt a set-based solution
first, and only after every other option has failed, such as adding indexes,
hints, and ultimately SET FORCEPLAN, should you convert a set-based solution
to a row-based one. As you can see, cursors are not for the uninitiated.
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:%23uVtziZOGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Ok so just so I understand your strategy, a rollback is not necessary
> since
> you doing INSETAD OF and are putting up a warning if there are any rows
> that
> cannot be deleted and then actually performing the delete on the allowed
> rows within the trigger?
> The foreign key won't help me here. The actual project is slightly more
> complex but my sample got the point across and your solution should do the
> trick nicely. Very much appreciated.
> What's the reason for avoiding cursors? Is it a performance issue or are
> there other reasons?
> Thanks again,
> Keith
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1140822038.662268.199890@.e56g2000cwe.googlegroups.com...
>
> Do not use cursors in triggers (or anywhere else you don't have to for
> that matter).
> I've included a solution below but I don't really see the point. Why
> not just modify your original DELETE statement to exclude the
> referenced rows using the NOT EXISTS check? The foreign key keeps you
> safe from accidental deletes.
> CREATE TRIGGER trg_customer
> ON customer INSTEAD OF DELETE
> AS
> IF EXISTS
> (SELECT *
> FROM deleted
> WHERE EXISTS
> (SELECT *
> FROM orders
> WHERE custid = deleted.custid))
> RAISERROR ('Customers with orders cannot be deleted.', 16, 1);
> DELETE FROM customer
> WHERE EXISTS
> (SELECT *
> FROM deleted
> WHERE custid = customer.custid)
> AND NOT EXISTS
> (SELECT *
> FROM orders
> WHERE custid = customer.custid);
> GO
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Thanks for all the info Brian.

delete records

I am having trouble trying to figure out how to delete some records. I have
the following query:
select * from p join ppt on p.PatNo=ppt.PatNo
where p.DayTime>=ppt.Discharge
this returns 1,000 records. These are the records I want to delete from p.
I created this delete query:
delete from p
where exists (select * from p join ppt on p.PatNo=ppt.PatNo
where p.DayTime>=ppt.Discharge)
This query deletes all 16,000 records from p. Logically, what am I missing?
Thanks
DionTry this
DELETE p
FROM pjoin ppt on p.PatNo=ppt.PatNo where p.DayTime>=ppt.Discharge
"Dion" wrote:

> I am having trouble trying to figure out how to delete some records. I ha
ve
> the following query:
> select * from p join ppt on p.PatNo=ppt.PatNo
> where p.DayTime>=ppt.Discharge
> this returns 1,000 records. These are the records I want to delete from p
.
> I created this delete query:
> delete from p
> where exists (select * from p join ppt on p.PatNo=ppt.PatNo
> where p.DayTime>=ppt.Discharge)
> This query deletes all 16,000 records from p. Logically, what am I missin
g?
> Thanks
> Dion
>
>|||Try this:
DELETE P
FROM P JOIN PPT
ON P.PATNO =PPT.PATNO
WHERE P.DAYTIME>=PPT.DISCHARGE
I usually wrap admin statements like this in a BEGIN TRAN then query the
table to see if the correct results are obtained if so COMMIT TRAN if not
ROLLBACK TRAN.
HTH
Jerry
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:DB2A229F-F7E6-4107-A646-F4C915DDE1B8@.microsoft.com...
>I am having trouble trying to figure out how to delete some records. I
>have
> the following query:
> select * from p join ppt on p.PatNo=ppt.PatNo
> where p.DayTime>=ppt.Discharge
> this returns 1,000 records. These are the records I want to delete from
> p.
> I created this delete query:
> delete from p
> where exists (select * from p join ppt on p.PatNo=ppt.PatNo
> where p.DayTime>=ppt.Discharge)
> This query deletes all 16,000 records from p. Logically, what am I
> missing?
> Thanks
> Dion
>
>|||On Fri, 30 Sep 2005 14:45:02 -0700, Dion wrote:
>I am having trouble trying to figure out how to delete some records. I hav
e
>the following query:
>select * from p join ppt on p.PatNo=ppt.PatNo
>where p.DayTime>=ppt.Discharge
>this returns 1,000 records. These are the records I want to delete from p.
>I created this delete query:
>delete from p
>where exists (select * from p join ppt on p.PatNo=ppt.PatNo
>where p.DayTime>=ppt.Discharge)
>This query deletes all 16,000 records from p. Logically, what am I missing?[/color
]
Hi Dion,
You didn't correlate the subquery to the main query. The subquery runs
by itself, so it will return the same result (1,000 rows) for each row
in the outer query - and that means that the EXISTS is true for each row
in p.
DELETE FROM p
WHERE EXISTS (SELECT *
FROM ppt
WHERE p.PetNo = ppt.PatNo
AND p.DayTime >= ppt.Discharge)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try:
DELETE
FROM P
WHERE P.<PK column name> IN (SELECT P.<PK COlum name>
FROM P
INNER
JOIN PPT
ON
P.PatNo = ppt.PatNo
WHERE P.DayTime >=
PPT.DIscharge )
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:DB2A229F-F7E6-4107-A646-F4C915DDE1B8@.microsoft.com...
> I am having trouble trying to figure out how to delete some records. I
have
> the following query:
> select * from p join ppt on p.PatNo=ppt.PatNo
> where p.DayTime>=ppt.Discharge
> this returns 1,000 records. These are the records I want to delete from
p.
> I created this delete query:
> delete from p
> where exists (select * from p join ppt on p.PatNo=ppt.PatNo
> where p.DayTime>=ppt.Discharge)
> This query deletes all 16,000 records from p. Logically, what am I
missing?
> Thanks
> Dion
>
>

Delete Query

I have part of a stored proc that I need help with.

I need to figure out how to delete infro from the

tblManifest using the @.ssnDelete variable.

I Have multiple records ffor the given above variable.

Would this need to loop in order to work and that is the solution for this.

I get ther following error when I run the full stored proc.

Msg 512, Level 16, State 1, Line 2

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Here is the code fro part of the proceedure.

Thanks in advance.

Gene

-

CREATE TABLE dbo.tblTempSSN(

ssn varchar(11) null);

OPEN SYMMETRIC KEY SymKeySSN

DECRYPTION BY CERTIFICATE CertSSN;

INSERT INTO tblTempSSN(ssn)

SELECT CONVERT(VARCHAR(11), DecryptByKey(s.SSN)) AS SSN

FROM tblSoldier s, tblManifest m

WHERE CONVERT(VARCHAR(11), DecryptByKey(s.SSN)) = m.ssn

CLOSE SYMMETRIC KEY SymKeySSN;

declare @.ssnDelete varchar(11);

set @.ssnDelete = (select (m.ssn) as ssn

from tblManifest m, tblTempSSN t

where t.ssn = m.ssn);

delete

from tblManifest

where ssn = '@.ssnDelete';

DROP TABLE dbo.tblTempSSN;

-

Try:

delete tblManifest

where exists (

select *

from dbo.tblTempSSN as a

where a.SSN = tblManifest.SSN

)

go

AMB

|||

Code Snippet

CREATE TABLE dbo.tblTempSSN(

ssn varchar(11) null);

OPEN SYMMETRIC KEY SymKeySSN

DECRYPTION BY CERTIFICATE CertSSN;

INSERT INTO tblTempSSN(ssn)

SELECT CONVERT(VARCHAR(11), DecryptByKey(s.SSN)) AS SSN

FROM tblSoldier s, tblManifest m

WHERE CONVERT(VARCHAR(11), DecryptByKey(s.SSN)) = m.ssn

CLOSE SYMMETRIC KEY SymKeySSN;

delete

from tblManifest

from tblManifest

inner join tblTempSSN on tblTempSSN.ssn = tblManifest.ssn

DROP TABLE dbo.tblTempSSN;