Temp table in a trig - SQL Server Forums

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)

Temp table in a trig

Author

Topic

sujosh
Yak Posting Veteran

55 Posts

Posted - 2006-02-10 : 16:16:14

I have a question regarding having a temp table created in my trigger

CREATE TRIGGER ins_employee_details
ON employee_details
FOR INSERT
AS
DECLARE @mytable table
(RequiredAction_Id int IDENTITY (1, 1),
RequiredAction varchar(50) NOT NULL ,
RequiredActionDescription ntext NULL ,
DueDateInDays int NOT NULL)
Declare @req_action_id int,@req_action varchar(50),@req_action_desc varchar(250),@due_date int,@rowid int,@maxrowid int,@pk int,@emp_id int

insert into @mytable
(RequiredAction_Id,RequiredAction,RequiredActionDescription,DueDateInDays)
select
(RequiredAction_Id,RequiredAction,RequiredActionDescription,DueDateInDays)
from required_action

select @rowid = min(rowid),
@maxrowid = max(rowid)
from @mytable

while @rowid <= @maxrowid begin

select

@req_action_id = RequiredAction_Id,
@req_action = RequiredAction,
@req_action_desc = RequiredActionDescription,
@due_date = DueDateInDays

from @mytable
where rowid = @rowid

begin transaction;
select @emp_id = emp_id from inserted
insert checklist(emp_Id)
values (@emp_id)

if @@error = 0

begin
commit transaction;
select @pk = scope_identity()
end
else

begin
rollback transaction;
select 0
end

begin transaction;

insert checklist_detail(checklist_id,RequiredAction_id,RequestStatus_Id,Priority_Id,Owner_Id,due_date,LastActionDate)
values
(@pk,@req_action_id,1,1,1,getdate(),getdate())

if @@error = 0

begin
commit transaction;
end
else

begin
rollback transaction;
select 0
end
set @rowid = @rowid + 1

end

When I run this I get an error that says
Line 15: Incorrect syntax near ','. i.e the row in red. Any help is appreciated!

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-10 : 18:31:16

The error is because you shouldn't have parenthesis around the select column list. You will also need to remove the identity column from the both columnLists.

insert into @mytable
(RequiredAction,RequiredActionDescription,DueDateInDays)
select
RequiredAction,RequiredActionDescription,DueDateInDays
from required_action

EDIT:
or not define the table variable column as identity

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources