inserted table doesn't have values

[asolds]

The variables @queue_name and @app_id are null in the following trigger. However, the app_id and queue_activity_id fields within the t_ccs_queue table are not null - seems like the inseretd table is not storing the values. Any suggestions?

CREATE trigger ats_file_retrieve on dbo.t_ccs_queue for insert as
begin
declare @queue_name varchar(50)
declare @app_id int

select @queue_name = act.activity_desc, @app_id = inserted.app_id
from dbo.t_ccs_admin_activities as act, inserted
where act.activity_id = inserted.queue_activity_id

end

 

[philhege]

It appears that you are trying to return values from a trigger. I'm not sure this is a viable approach. You may want to insert those values into another table and inspect them there.

Here's what I don't get: A trigger is an internal SQL process, so
1. You insert a record into the t_ccs_queue table;
2. The insert trigger fires and performs a SELECT query, populating two local variables.

How do you expect to be able to read the variables?

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[asolds]

Sorry, I insert the values into another table. Here is the full trigger:

CREATE trigger ats_file_retrieve on dbo.t_ccs_queue for insert as
begin
declare @queue_name varchar(50)
declare @app_id int

select @queue_name = act.activity_desc, @app_id = inserted.app_id
from dbo.t_ccs_admin_activities as act, inserted
where act.activity_id = inserted.queue_activity_id

insert into dbo.ATSFileRetrieve values(@app_id,1,@queue_name,getdate())
end

 

[vongrunt]

This trigger is created on t_ccs_queue.
You are joining inserted with ccs_admin_activities (act).
Two different tables...

So if inserted queue_activity_id value does not exist in act, @queue_name and @app_id will become NULLs.

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[asolds]

That's correct, however, it does exist and always will.

 

[asolds]

Thank you for your help, but I figured out what teh issue was. The reason I believe the inseretd logical table wasn't storing the values was because the application was first performing an insert into t_ccs_queue table to set the identity column and then updating this record with the app_id, queue_activity_id, and other field values. Thus, when the query ran in the trigger, the @app_id and @queue_name variables were in fact NULL. Therefore, by changing the trigger to run for update resolved my issue.