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.