Why after this trigger has completed is the value in tmp null? Its driving
me nuts!
tbl_Criteria just has 1 column with 1 row
ct_accountant_1 varchar(30)

CREATE trigger tg_Accountant
on dbo.tbl_Criteria
For insert,update
as
SET NOCOUNT ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

if update(ct_accountant_1)

declare @TEST1 tinyint

set @TEST1 = (select case when a.ct_accountant_1 is not null then '1' else
'2' end from inserted a)

truncate table tmp
insert into tmp (tmp)
select @TEST1


I have tried with:
SET NOCOUNT ON
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
also, same result.

I've also tried <> null instead of is not null, again no joy.

I also added into the trigger (select into temp2 * from inserted), which
usually works fine, just to double check what is in inserted. But this time
it kept erroring saying
"There is already an object called temp2 in the database"
Which implies that the trigger is firing twice, but there are no other
triggers and only one alteration was made to the single row in the table.

Blood pressure has been rising steadily for the last few hours!
Help very much appreciated!

Steve,

1 - Declare the variable outside the IF
2 - Use BEGIN END to delimit the block

....
set nocount on

declare @TEST1 tinyint

if update(ct_accountant_1)
begin

set @TEST1 = (select case when a.ct_accountant_1 is not null then '1'
else
'2' end from inserted a)

truncate table tmp
insert into tmp (tmp)
select @TEST1

end
....


AMB

"Steve'o" wrote:

Have a quick look at your syntax.

The syntax should be...

USE pubs
GO
SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END,
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO

Whereas you have "select case when a.ct_accountant_1"

It should be

SELECT @TEST1 =
CASE a.ct_accountant_1
WHEN not null THEN 1
ELSE
2

Or something along those lines.

Have fun

Peter

"I favor the Civil Rights Act of 1964 and it must be
enforced at gunpoint if necessary."
Ronald Reagan

The CASE statement the OP uses is correct. It is a searched CASE, not a
simple CASE (see the article about CASE in Books Online).

--
Jacco Schalkwijk
SQL Server MVP


"Peter The Spate" <anonymous (AT) discussions (DOT) microsoft.com> wrote

It does work either way, "simple case function" or "searched case function"

select @VAR = case col1 when a then b else c end
or
@VAR = select case when col1 = a then b else c end

But thanks for the reply anyway, Alejandro got it spot on.



"Peter The Spate" wrote:

I replied to your post 20 mins ago, but it has not yet appeared. It was just
to say a big thanks for the answer, I can (yes, can) believe I had been
stupid and had not seen the answer, I tried many strange things and wasted a
lot of time, so thanks again.

"Alejandro Mesa" wrote:

Steve,

You are welcome. Do not feel bad for that, I have been there too.


AMB

"Steve'o" wrote: