I have created a trigger that i want to fire everytime a new record is inserted,

this is the trigger


CREATE TRIGGER NewProjectInserted
ON project
FOR INSERT
AS

DECLARE @PROJECTID BIGINT,
@COUNTRYID INT

SELECT @PROJECTID = i.ProjectID,
   @COUNTRYID = i.CountryID
FROM inserted i


IF (@COUNTRYID = 1)
    BEGIN
SET NOCOUNT ON;INSERT INTO Project (ProjectID, Organisation, ProjectName, ProjectTypeID, UserID) VALUES INSERT INTO
    FUNDALLOCATION
    (FundSourceID, ProjectID, Allocation )
    VALUES
    ('1', '@PROJECTID', '100')
    END
IF (@COUNTRYID = 2)
    BEGIN
    INSERT INTO
    FUNDALLOCATION
    (FundSourceID, ProjectID, Allocation )
    VALUES
    ('2', '@PROJECTID', '100')
    END
IF (@COUNTRYID = 3)
    BEGIN
    INSERT INTO
    FUNDALLOCATION
    (FundSourceID, ProjectID, Allocation )
    VALUES
    ('3', '@PROJECTID', '100')
    END


However, when I run it i am getting the following error

Server: Msg 156, Level 15, State 1, Procedure NewProjectInserted, Line 16
Incorrect syntax near the keyword 'INSERT'.

and I cant see what is the problem. Can anyone spot what I am doing wrong?

many thanks

Sorry, Ignore that, I have just spotted the obvious, I had pasted a bit of earlier code into mine without noticing, My code now looks like this



CREATE TRIGGER NewProjectInserted
ON project
FOR INSERT
AS

DECLARE @PROJECTID bigint,
@COUNTRYID INT

SELECT @PROJECTID = i.ProjectID,
   @COUNTRYID = i.CountryID
FROM inserted i


IF (@COUNTRYID = 1)
    BEGIN
    INSERT INTO
    FUNDALLOCATION
    (FundSourceID, ProjectID, Allocation )
    VALUES
    ('1', '@PROJECTID', '100')
    END
IF (@COUNTRYID = 2)
    BEGIN
    INSERT INTO
    FUNDALLOCATION
    (FundSourceID, ProjectID, Allocation )
    VALUES
    ('2', '@PROJECTID', '100')
    END
IF (@COUNTRYID = 3)
    BEGIN
    INSERT INTO
    FUNDALLOCATION
    (FundSourceID, ProjectID, Allocation )
    VALUES
    ('3', '@PROJECTID', '100')
    END


Which seems to work fine, only when i have set the trigger and insert a new line into my projects table, i am getting this

error convertind datatype varchar to bigint, I can only presume this is happening within the trigger, as when i take the trigger off, it doesn't happen. Does anyone know why? I think I have declared my variables in the trigger OK?

You're inserting @PROJECTID as a string literal; not as a variable. Drop the quotes from '@PROJECTID' and you should be good to go...

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

i have sorted it, I had single quotes around my @ProjectID, I have no brain today its Christmas eve !!

Many Thanks