insert query in trigger

[GerryGoldberg]

I am trying to write an insert trigger that will simply take the inserted row and insert it in another table. The trigger code is:

INSERT INTO tblHistory ( Client_ID,Visit_ID,Visit_Date)
SELECT i.client_ID,i.visit_ID,i.visit_Date FROM inserted i

I have tried many variations of this query but nothing works. I always get an error trying to insert into tblHistory. What am I doing wrong?

Gerry Goldberg

 

[hellferret]

What error do you get?
HF

 

[GerryGoldberg]

The error that I get is:
Line 1: Incorrect syntax near ')'. (#170)

Here is the actual trigger code:

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'tblEncProgram_trgAdd' AND type = 'TR')
DROP TRIGGER tblEncProgram_trgAdd
GO

CREATE TRIGGER tblEncProgram_trgAdd ON tblEncProgram FOR Insert AS

set nocount on
declare @pgmID varchar(2),
@table varchar(10),
@sqlString varchar(100)

select @pgmID=i.program_ID
from inserted i

set @table = 'rptProg_' + @pgmID
set @sqlString = 'INSERT INTO ' + @table + ' (client_ID,staff_ID,clinic_ID,initial_Date,start_Date,end_Date,close_reason) SELECT i.Client_ID, i.Staff_ID, i.Clinic_ID, i.Initial_Date, i.Start_date, i.End_date, i.Close_reason FROM inserted i'

execute (@sqlString)

 

[rajeevnandanmishra]

hi Gerry,

It seems that your string exeeds 100 character.

Try to increase your @sqlstring size to varchar(200)