Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    1

    Unanswered: Trigger to update linked table

    Hi,

    I have a trigger

    create trigger trg_UpdateMatter
    on dbo.matter for update
    as

    update SQL.legal.dbo.test_matter
    set CLIENT_ID= inserted.mclient,
    MATTER_ID= inserted.mmatter,
    MATTER_NAME= inserted.mdesc1 ,
    MCLOSEDT= inserted.mclosedt
    from inserted
    where inserted.mmatter=SQL.legal.dbo.test_matter.MATTER_ ID

    It's meant to update data in a table on a linked server when the local table is updated. But when I execute the code above to create the trigger I get the following message is QA.


    Server: Msg 117, Level 15, State 2, Procedure trg_UpdateMatter, Line 12
    The number name 'SQL.legal.dbo.test_matter' contains more than the maximum number of prefixes. The maximum is 3.


    Now, I understand what it's telling me but I can't think of a way round it.

    Any ideas ?

    Matt

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    The way to reference an object in a linked server is

    servername.databasename.owner.objectname .


    this might work ... i m not sure


    create trigger trg_UpdateMatter
    on dbo.matter for update
    as

    update SQL.legal.dbo.test_matter
    set CLIENT_ID= inserted.mclient,
    MATTER_ID= inserted.mmatter,
    MATTER_NAME= inserted.mdesc1 ,
    MCLOSEDT= inserted.mclosedt
    from inserted
    where inserted.mmatter=(select matter_id from SQL.legal.dbo.test_matter)

  3. #3
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    In the FROM section you should specify all the tables interested.
    So, your query should look like this one:

    update SQL.legal.dbo.test_matter
    set CLIENT_ID= inserted.mclient,
    MATTER_ID= inserted.mmatter,
    MATTER_NAME= inserted.mdesc1 ,
    MCLOSEDT= inserted.mclosedt
    from inserted i, SQL.legal.dbo.test_matter t
    where i.mmatter = t.MATTER_ID
    Davide Mauri
    http://www.davidemauri.it

Posting Permissions

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