Professional Web Applications Themes

Insert trigger sqlserver 2000 and Visma Salesoffice - Microsoft SQL / MS SQL Server

Hi, I'm trying to write a trigger that will fire on insert on one database table and make some changes on another database table. The Data database with the table Customer has the trigger, and is supposed to insert the same data into the Web database. I've added the correct users to both databases, and given them the rigth permissions. I've got two questions: 1) When I don't have the trigger, I can easily make a new customer in Salesoffice. But if I add the trigger to the Cusotmer table, I'm not even able to save a new customer in ...

  1. #1

    Default Insert trigger sqlserver 2000 and Visma Salesoffice

    Hi,

    I'm trying to write a trigger that will fire on insert on one database
    table and make some changes on another database table.

    The Data database with the table Customer has the trigger, and is
    supposed to
    insert the same data into the Web database.
    I've added the correct users to both databases, and given them the
    rigth permissions.

    I've got two questions:

    1) When I don't have the trigger, I can easily make a new customer in
    Salesoffice. But if I add the trigger to the Cusotmer table, I'm not
    even able to save a new customer in Salesoffice.

    2) What is wrong with this trigger?
    Here it is:

    CREATE TRIGGER Data.[insert_web] ON Data.Customer
    FOR INSERT
    AS
    DECLARE ActNo INT
    DECLARE Nm VARCHAR(40)

    SELECT ActNo = ins.CustomerNo FROM inserted ins

    IF ActNo > 0
    BEGIN
    SELECT Nm = [Name]
    FROM Data.Customer
    WHERE CustomerNo = ActNo

    IF NOT EXISTS (SELECT* FROM Web.dbo.cpyCompany WHERE cpyid = ActNo)
    BEGIN
    INSERT INTO Web.dbo.cpyCompany (cpyID,cpyName)
    VALUES (ActNo, Nm)
    END

    UPDATE Web.dbo.cpyCompany
    SET cpyName = Nm,
    WHERE cpyId = ActNo

    END

    Thanks in advance.

    Kath
    Kathinka Guest

  2. #2

    Default Re: Insert trigger sqlserver 2000 and Visma Salesoffice

    It would be helpful if you posted the error you get (if any) when using the
    trigger.

    Perhaps you are getting a locking condition because you are attempting to
    SELECT from the same table (data.customer) that was just inserted into. If
    you are getting a "locking" condition and the trigger never completes, my
    suggestions:

    - change the trigger definition from FOR INSERT to AFTER INSERT
    - change SELECT Nm = [Name] FROM Data.Customer WHERE CustomerNo = ActNo
    to SELECT Nm = [Name] FROM Inserted WHERE CustomerNo = ActNo
    -or- SELECT Nm = [Name] FROM Data.Customer (NOLOCK) WHERE CustomerNo =
    ActNo

    regards, -marty nicholson

    "Kathinka Weyns" <no> wrote in message
    news:google.com... 


    Martin Guest

  3. #3

    Default Re: Insert trigger sqlserver 2000 and Visma Salesoffice

    Thank you for your suggestions.

    In the meantime I've succeeded partially.

    Whenever I make a new record in Salesoffice (the Data database), the
    new record will be registered in the Web database, but only with the
    new cpyId (CustomerNo), and all other data is set to NULL.

    However, if I type in a CustomerNo, Name and other data directly into
    the SQL server Enterprise Result pane of the Customer table, the
    trigger manages to enter all the new data into the Web database.

    I suspect I lack some kind of permission on a user.
    Let me give you some info on the names, ownerships and users on each
    database:

    Database 1: Data
    table name: Customer
    owner: RASA
    columns: CustomerNo, Name
    users: dbo, RASA (member of public, db_datareader and db_datawriter),
    SharedData

    Database 2: Web
    table name: cpyCompany
    owner: dbo
    columns: cpyId, cpyName
    users: dbo, RASA(member of public, db_accessadmin, db_datareader,
    db_datawriter), SalesOuser (same membership as RASA)

    ps: I've never gotten any error message.

    Hope this helps you to give me an idea of the problem.

    Thanks in advance, Kath
    Kathinka Guest

Similar Threads

  1. Replies: 1
    Last Post: July 28th, 02:18 PM
  2. Problems with a linked server in SQLServer 2000
    By Kim Bundgaard in forum IBM DB2
    Replies: 0
    Last Post: July 17th, 10:12 AM
  3. Trigger - wont allow me to insert
    By pat in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 3rd, 05:51 PM
  4. Replies: 0
    Last Post: January 8th, 11:52 AM
  5. Trigger: Insert if not exists?
    By FC in forum Oracle Server
    Replies: 1
    Last Post: December 10th, 08:40 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139