Select Case In Delete Trigger Does Not Work

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

SQL Server 2000 Forums

SQL Server Development (2000)

Select Case In Delete Trigger Does Not Work

Author

Topic

scptech
Starting Member

8 Posts

Posted - 2010-01-03 : 20:51:38

In SQL 2005 Express, I am writing a delete trigger that uses a select case statement to determine whether to allow a delete on a table, based on a dayXBilled column being null or not. The idea is to count how many days are not null in a single record. For this article I am using two columns (day1DateBilled, day2DateBilled), in my production application there are be 31 columns, one for each possible day of the month. (I have made the assumption that only one record can be deleted at one time).

The select case works fine when run by itself. However, inside the trigger it always results in a null value.

Any help / guidance is greatly appreciated.

------------Sample Table & Data ----------------------------------
CREATE TABLE [dbo].[testTable] (
[noteId] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
[service] [varchar] (10) NOT NULL,
[day1Units] int NOT NULL DEFAULT 0,
[day1DateBilled] [datetime] NULL,
[day2DateBilled] [datetime] NULL
) ON [PRIMARY]
go

INSERT INTO [testTable] ([service],[day1Units],[day1DateBilled],[day2DateBilled])VALUES('Cleaning',10,NULL,NULL)
INSERT INTO [testTable] ([service],[day1Units],[day1DateBilled],[day2DateBilled])VALUES('Ironing',15,'Jan 3 2009 12:00:00:000AM','Jan 2 2010 12:00:00:000AM')

------------Delete Trigger ----------------------------------
CREATE TRIGGER [dbo].[trDelTestTable] ON [dbo].[testTable]
FOR DELETE
AS
declare @noteId int, @billedDayCt int, @errmsg varchar(250)
select @noteId = noteId from deleted
-- Find out if any days in this note have been billed
select @billedDayCt =
case when day1DateBilled is null then 0 else 1 end + case when day2DateBilled is null then 0 else 1 end
from testTable where noteId = @noteId
-- If any days billed, do not allow delete

-- @billedDayCt is ALWAYS NULL -- CAN'T FIGURE OUT WHY------------

IF @billedDayCt >= 0
BEGIN
select @errmsg = 'Cannot delete note ' + convert(varchar(10), @noteId) + ', there are ' + convert(varchar(10), @billedDayCt) + ' days billed in the note'
raiserror (@errmsg, 19,1) with log
rollback transaction
END
go

--------------To Test --------------------------------------------

select noteId, case when day1DateBilled is null then 0 else 1 end + case when day2DateBilled is null then 0 else 1 end billedDayCt
from testTable

delete from testTable where noteId = 2

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-03 : 22:55:20

Your trigger has a fundamental problem in that it assumes the trigger fires for every row in a batch, which isn't true. It fires once for the entire batch, so the logic of the trigger must handle that. See this for more information: http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

But the reason why your trigger isn't working properly is because you need to use the deleted trigger table in your select/case. You can't use testTable since the row doesn't exist there anymore. The row only exists in the deleted trigger table now.


------------Delete Trigger ----------------------------------
CREATE TRIGGER [dbo].[trDelTestTable] ON [dbo].[testTable] 
FOR DELETE 
AS
declare @noteId int, @billedDayCt int, @errmsg varchar(250)
select @noteId = noteId from deleted
-- Find out if any days in this note have been billed 
select @billedDayCt = 
case when day1DateBilled is null then 0 else 1 end + case when day2DateBilled is null then 0 else 1 end 
from deleted where noteId = @noteId
-- If any days billed, do not allow delete

-- @billedDayCt is ALWAYS NULL -- CAN'T FIGURE OUT WHY------------

IF @billedDayCt >= 0
BEGIN
select @errmsg = 'Cannot delete note ' + convert(varchar(10), @noteId) + ', there are ' + convert(varchar(10), @billedDayCt) + ' days billed in the note'
raiserror (@errmsg, 19,1) with log
rollback transaction
END
go

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

scptech
Starting Member

8 Posts

Posted - 2010-01-04 : 10:17:15

Thank you very much....I completely missed that fact!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-04 : 10:36:40

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources