Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    124

    Unanswered: trigger question

    I have written the following trigger:

    CREATE TRIGGER trig_AddABRID
    ON dbo.DIM_ABR_TRNSACT_DETLS
    FOR INSERT
    AS
    DECLARE @newABRID INT
    SELECT @newABRID = (SELECT ABR_ID FROM Inserted)

    I'm getting a syntax error when trying to insert from my web application:

    "syntax error converting the varchar value 'varABRID' to a column of datatype int."

    Here is my sql insert statement:
    INSERT INTO dbo.DIM_ABR_TRNSACT_DETLS (ABR_TRNSACT_APPROP_YR, ABR_TRNSACT_STRATEGY, ABR_BUDG_ACCT, ABR_BUDG_OBJ, ABR_ORIG_BUDG_AMT, ABR_INCR_DECR, ABR_ORIG_ALLO, ABR_REQ_ALLO, ABR_ID)
    VALUES ('varyr','varstrategy','varBA','varBO',convert(mon ey,'varorigBA'),convert(money,'varIncrDecr'),conve rt(money,'varorigallo'),convert(money,'varreqallo' ),'varABRID')

    I'm using the trigger to insert a primary key value from one table as a foreign key into another table.

    Any help is appreciated. Thanks.
    -Dman100-

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    What numeric value are you expecting to get from this: 'varABRID'?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jan 2004
    Posts
    124
    I was trying to write the trigger so that it would take the primary key value from my master table and insert it as a foreign key value into my details table when I inserted data into the details table.

    The data type for that field in both tables is "int".

    I don't think I need to specify the field name or variable in the SQL??

    I think I've written the trigger incorrectly, but not sure how I need to amend it to work properly?

    Thanks for any help.
    -Dman100-

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think the point that rdjabarov was trying to make is that the expression:
    Code:
    convert(mon ey,'varorigBA')
    isn't going to fly, no matter how hard you try to push it

    That string isn't going to convert the money datatype, no matter how you go about it, so the trigger won't ever fire.

    -PatP

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Actually I was referring to 'varABRID' because this was referenced in the error. It is also not your trigger, but your INSERT statement that fails. But you can easily fix this one and the ones that will follow by doing this (replace everything surrounded by <> with valid data types):

    declare @varyr int,@varstrategy <don't know what this is>,@varBA <same_here>,@varBO <same_here>,@varorigBA money,@varIncrDecr money,@varorigallo money,@varreqallo money,@varABRID int
    --Perform variable assignments
    INSERT INTO dbo.DIM_ABR_TRNSACT_DETLS (ABR_TRNSACT_APPROP_YR, ABR_TRNSACT_STRATEGY, ABR_BUDG_ACCT, ABR_BUDG_OBJ, ABR_ORIG_BUDG_AMT, ABR_INCR_DECR, ABR_ORIG_ALLO, ABR_REQ_ALLO, ABR_ID)
    VALUES (@varyr,@varstrategy,@varBA,@varBO,@varorigBA,@var IncrDecr,@varorigallo,@varreqallo,@varABRID)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •