SQL Server : Triggers for Insert

Question

create table tab(id int identity,task_id int,task_descp varchar(10),task_code varchar(10))

insert into tab values(7,'BUS','B')
insert into tab values(3,'CAR','C')

create table tab_detail(  task_descp varchar(10),task_code varchar(10),color varchar(10))

create trigger tab_trigger on tab for insert as
  declare @task_descp varchar(10)
  declare @task_code varchar(10)
  declare @task_id int
  set @task_descp=i.task_descp from inserted i
  set @task_code=i.task_code from inserted i 
  set @task_id=i.task_id from inserted i
  if(@task_id=7)
    insert into tab_detail values(@task_descp,@task_code,'BLUE')
  if(@task_id=3)
    insert into tab_detail values(@task_descp,@task_code,'GREEN')
go

I want to create a trigger for table tab where if I insert a record based on the task_id column a record has to be inserted into another table tab_detail.

When executing this I get this error:

Incorrect syntax near the keyword 'from'

Answer 1

Change the SET to SELECT. Also, inserted is a recordset, not a single value. Fixing the code issue still might result in a run time issue!

This code should work fine for a recordset of information.

CREATE TRIGGER tab_trigger ON tab FOR INSERT AS
BEGIN

-- nothing to do?
IF (@@rowcount = 0) RETURN;

-- do not count rows
SET NOCOUNT ON;

-- inserted data
INSERT INTO tab_detail 
  SELECT 
      i.task_descp,
      i.task_code,
      CASE i.taskcode
          WHEN 7 THEN 'BLUE'
          WHEN 3 THEN 'GREEN'
          ELSE ''
      END
  FROM inserted i
  WHERE i.task_code in (3, 7)

END
GO

Answer 2

Instead of:

set @task_descp=i.task_descp from inserted i

Try this:

select @task_descp=i.task_descp from inserted i

Or you could do this:

create trigger tab_trigger on tab for insert as

       insert into tab_detail
       select task_descp, task_code, case @task_id when 7 then 'BLUE' else 'GREEN' end
       from inserted 
       where taskid in (7,3)

go