TRIGGER ROLLBACK 3

[osjohnm]

hi all

i've created a trigger that is suppose to prevent a record being deleted if it exists in another table. My syntax seems ok. When I executed it it went through all its checks and printed the message to the screen like it is meant to.
The problem is that it deleted the record anyway.

Here is my syntax, what am I missing:

CREATE TRIGGER [COMMON_CATEGORY] ON [dbo].[PROB_CATEGORY]
FOR DELETE
AS
declare @catcode varchar(20);
declare @cmcode varchar(20)

select @catcode = p.category_code from prob_category p, deleted d
where p.category_code = d.category_code

select @cmcode = category_code from common_problem

if @catcode = @cmcode
ROLLBACK TRANSACTION
print
'The category you attempted to delete has a common problem associated to it'

Thanks

John

 

[tlbroadbent]

There are at least three problems with the TRIGGER.

1) In SQL, if the IF statement evaluates true only one following statement will be executed unless enclosed in a BEGIN... END block. Thus the Print statement will always execute whether the IF statement is true or false. Change the IF statment as follows.

IF @catcode = @cmcode
BEGIN
ROLLBACK TRANSACTION
PRINT 'The category you attempted to delete has a common problem associated to it'
END

2) There is no need to join the PROB_CATEGORY table to the deleted to get @catcode. The deleted table contains the category_code. The following works and is certainly simpler and more efficient.

select @catcode = category_code from deleted d

3) The TRIGGER will only work if one row is deleted at a time. If you can ensure that only one row will be deleted in a transation this is OK but it is generally not good practice to write a TRIGGER that handles only one update at a time. The following modifications would allow the TRIGGER to work for multiple deletes. Of course, if one delete fail, all deletes in the transaction will ROLLBACK.

CREATE TRIGGER [COMMON_CATEGORY] ON [dbo].[PROB_CATEGORY]
FOR DELETE
AS

IF EXISTS
(Select * From common_problem c
Join deleted d
On c.category_code = d.category_code)
Begin
ROLLBACK
End

Also, you should avoid returning results or printing messages in a TRIGGER. If you want to generate an error message, use RAISERROR. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[osjohnm]

Thanks Terry,

Only one record will be deleted at a time. I also noticed that I went the long way round (as usual), there was actually a missing constraint between the columns in prob_category and common_problem.

Atleast I have learn't something about the logic in triggers. I tried the RAISERROR function but I keep getting a error msg about incorrect syntax?

RAISERROR ('The category you attempted to delete has a common problem associated to it')

Thanks again
John

 

[tlbroadbent]

You need to add the severity and state parameters.

RAISERROR ('The category you attempted to delete has a common problem associated to it', 16, 1)

Check out SQL BOL for more details.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_ra-rz_5ooi.asp

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_05_3f76.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[osjohnm]

Thanks Terry

 

[sguslan]

Terry ..
Can you dwell a little on why your code would work for multi deletes and not the other code ?

The SQL2K trigger logic still baffles me ..

Thanx for your continuos help ..

Salim

 

[JohnDTampaBay]

Hi Salim -

Terry's solution works for multiple deletes because the sub-query for the EXISTS. If any row in Deleted has a corresponding row in Common_Problem, the EXISTS is true and the entire transaction rolls back. The original trigger only tested a single value from the Deleted table.

--Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
0 row(s) selected

 

[tlbroadbent]

I want add a little to what Angel said regarding why the trigger works for one or multiple rows. The trigger I proposed dealt with two sets of data - the common_problem table and the deleted table. SQL makes it easy to work with the data sets. The inner query of the IF EXISTS statement joined these two sets. If matching rows exist in the tables IF Exists returns true. If no common rows are found IF EXISTS returns false.

In the original trigger, the first select statement inserts a value in a variable.

select @catcode = p.category_code
from prob_category p, deleted d
where p.category_code = d.category_code

If more than one row exists in the deleted table, this statement would fail with an error because SQL cannot set a variable to multiple values.

Hope this helps.
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.