Im trying to design a trigger that will:

1. Allow people to Update a column in our TsysQueue.ResponseDate column from
a NULL value to a real value.
2. Allow people to Update a column in our TsysQueue.ResponseDate column from
a real value to a NULL value but only 1 row at a time.

--would be OK
Update TsysQueue
set ResponseDate = '01/01/04'
where PrimaryKey between 1 and 5)

--would be OK
Update TsysQueue
set ResponseDate = NULL
where PrimaryKey = 1)

--would NOT be OK
Update TsysQueue
set ResponseDate = NULL
where PrimaryKey between 1 and 5)


The only thing I can make happen here is restrict Updates on any column in
my table to 1 record at a time. Can anyone help me out here?

alter trigger ModifyQATsysQueue on dbo.TsysQueue for update
As

BEGIN
declare @msg varchar(128)
set @msg = 'Can only Update 1 TsysQueue record to NULL at a time.'
if (select count (*) from inserted) >1
Begin
RaisError (@msg, 16, 1)
Rollback Transaction
End
end










--
SQL2K SP3

TIA, ChrisR

ChrisR wrote:
That's tough, since you would need to check each column against the
deleted table to see if it was changed from a NOT NULL to NULL. I would
say that maybe the best you can do is join the deleted and inserted
tables and count the number of rows where a not null was changed to a
null. Unless you are only concerned with 1 column. Why do you need this
implenmented exactly?

-- untested
Set @NULLCount = (
Select count(*)
from inserted i
inner join deleted d
on i.id = d.id and
and ((d.col1 IS NOT NULL and i.col1 IS NULL)
or (d.col2 IS NOT NULL and i.col2 IS NULL))

If @NULLCount > 1
Rollback tran


--
David Gugick
Imceda Software
www.imceda.com

Becuase I work for a company that has our QA db connected to our Production
mainframe. If the wrong columns are set to NULL, we credit peoples accounts.
(long story)

"David Gugick" <davidg-nospam (AT) imceda (DOT) com> wrote

On Fri, 3 Dec 2004 16:50:05 -0800, ChrisR wrote:

Hi Chris,

I hope that you're aware that such a trigger would disallow setting the
column to NULL in three rows at once, but would allow the same
modification if carried out through three seperate updates. That is: the
same end result is rejected or accepted, based on the queries used.

Are you sure that this is your actual requirement?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)