Results 1 to 12 of 12
  1. #1
    Join Date
    May 2003
    Location
    Bogota
    Posts
    8

    Question Unanswered: Stored Procedures

    I have three databases and I need that when I add a row to one database that same row should be added to the other database.

    HOw can I do that?

    JP

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    triggers?

    check this out:

    http://www.dbforums.com/t972624.html
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2003
    Location
    Bogota
    Posts
    8
    How is the syntax of the trigger if I need to update two different databases

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    first you need to set up a linked server using sp_addlinkedserver

    Then you create the trigger like

    CREATE TRIGGER myTrigger
    FOR INSERT
    AS
    -- 4 part table name
    INSERT INTO linkname.database.owner.table (col1, ect...
    SELECT Col1, ect... FROM inserted

    then create 1 for DELETE and 1 for UPDATE
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    the cleanest way to do it to do your insert using a stored procedure. from there you can do your inserts into other databases without having to use triggers.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    But there's no gaurentee that the row will get there...

    Do you consider this a lot of overhead?

    I don't like it too much either though...what's the other dbs going to be used for?

    Will they be live? Or for reporting?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Stored Procedures

    Originally posted by jpruizs
    I have three databases and I need that when I add a row to one database that same row should be added to the other database.

    HOw can I do that?

    JP
    What about a replication? For some cases it is not bad choice...

  8. #8
    Join Date
    Sep 2003
    Posts
    522
    if all databases are on the same server, than begin tran...if @@error<>0 rollback tran...commit tran can be a guarantor. if not, - linked server and distributed tran would do the same thing. transactional replication can also do the job just fine, but it does come with a cost of server performance.

  9. #9
    Join Date
    May 2003
    Location
    Bogota
    Posts
    8
    Originally posted by ms_sql_dba
    the cleanest way to do it to do your insert using a stored procedure. from there you can do your inserts into other databases without having to use triggers.
    How is the syntax of the stored procedure to change of databases....

    Get the values of the first database and then change to the other and finally insert the same values in the second database

    Thanks

    JP

  10. #10
    Join Date
    Oct 2003
    Posts
    706

    Wink

    When I look at this problem, I immediately find myself wondering why the row needs to be inserted into three tables simultaneously. Could there be a structural problem?

    (Obviously, in the case of an existing system one often does not have much choice in the matter.)

    Another consideration to be pondered here is that stored procedures are sometimes over-relied upon and sometimes do not execute efficiently; not at all. For example, in the InterBase system, stored procedures are apparently executed in a strictly serialized fashion and are dirt-dog bad-dog all the way. You need to plan to test your design early and under intensive load to make sure that it will hold up.

    It may be that the best way to do this is to submit three separate and distinct INSERTs under the auspices of a transaction. Gather actual data to determine what is the "best way" in your case.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    There doesn't have to be a problem in the design to get into situations like this. We have a similar situation too. We have a configuration database and then 4 other databases for different applications in the same suite that access this master database for all the configuration information. As we build new Apps, we add new databases but the Master (config) database will still be the same. This will avoid a lot of duplication of data. Now the question is how do I build the RI between these databases and the MasterConfig db? SQL Server does not allow RI across databases. So we had to put a dummy table in the app databases and write triggers to replicate the ids and build RI between these dummy tables and the tables in the App DBs.

    jpruizs, when it comes to syntax,

    To refer DatabaseB object in a object of DatabaseA

    DatabaseB.Owner.tablename (You can use it as if you are using any table in the same database as long as you qualify it with the DB and owner.)

    Personally, I would not recommend SPs to implement this because it might lead to data inconsistency. What if someone inserted data into the table in the Main database using a INSERT statement instead of using the SP. You are in trouble. Just a thought.

    - CB






    Originally posted by jpruizs
    How is the syntax of the stored procedure to change of databases....

    Get the values of the first database and then change to the other and finally insert the same values in the second database

    Thanks

    JP

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    If these DBs are on different instances, then add the linked server name to the begining.

    LinkedServerA.DatabaseA.Owner.ObjectName

    While triggers add an over head, I think, in this situation they are the safest.

    - CB

    Originally posted by sbaru
    There doesn't have to be a problem in the design to get into situations like this. We have a similar situation too. We have a configuration database and then 4 other databases for different applications in the same suite that access this master database for all the configuration information. As we build new Apps, we add new databases but the Master (config) database will still be the same. This will avoid a lot of duplication of data. Now the question is how do I build the RI between these databases and the MasterConfig db? SQL Server does not allow RI across databases. So we had to put a dummy table in the app databases and write triggers to replicate the ids and build RI between these dummy tables and the tables in the App DBs.

    jpruizs, when it comes to syntax,

    To refer DatabaseB object in a object of DatabaseA

    DatabaseB.Owner.tablename (You can use it as if you are using any table in the same database as long as you qualify it with the DB and owner.)

    Personally, I would not recommend SPs to implement this because it might lead to data inconsistency. What if someone inserted data into the table in the Main database using a INSERT statement instead of using the SP. You are in trouble. Just a thought.

    - CB

Posting Permissions

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