Can a Trigger Update another database?

  • I'm trying to teach myself how to write triggers. I understand the basics, but my boss wants me to figure out the following:

    How to INSERT or UPDATE a record from Table1 in DATABASE_01 to Table1 in DATABASE_02.

    From what I've read so far, it can be done, but am unable to find more info on how to do it.

    Am I looking in the wrong place? Neither the Book Online or the MS site had anything...

  • create trigger mytrigger on mytable for update

    as

    update database_02.dbo.Table1

    set myfield = i.myfield

    from inserted i

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • I don't quite understand the line

    set myfield = i.myfield

    I would like to insert the record in it's entirety from inserted to DATABASE_02. Do I need to set myfield for every field to be written? What does the i represent?

  • When doing an Update, you do have to do a set value for each column

    SET col1 = i.col1, col2 = i.col2

    The i is the alias name Steve associated with the inserted table which you do not need to use but if the column names are the same you do have to prefix with the table name or table alias name or you will get an error (inserted is the table that the data goin in is place in that can only be seen in a trigger). There is also a deleted table. When an update occurrs the old rows are stored in deleted and the new in inserted (so in truth an update affects the whole row).

    Now if this was for INSERT instead of update then you do

    INSERT database_02.dbo.Table1 (col1, col2, ...) SELECT (* if all the same column order in the col1 list to insert into or express properly col1, col2, ...) FROM inserted.

  • You guys are the best!

    I needed to do something similar and was stumped. So I cruised the Forum - found this example - and now am off to the races.

    Thanks,

    Bill Mais

  • quote:


    When doing an Update, you do have to do a set value for each column

    So if I specify only 4 of the 25 columns to be updated, do the other 21 columns get changed?


  • NO only the columns you specifiy get changed

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • quote:


    NO only the columns you specifiy get changed

    Thank you, that's what I thought, but I just wanted to verify it.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


  • quote:


    I'm trying to teach myself how to write triggers. I understand the basics, but my boss wants me to figure out the following:

    How to INSERT or UPDATE a record from Table1 in DATABASE_01 to Table1 in DATABASE_02.

    From what I've read so far, it can be done, but am unable to find more info on how to do it.

    Am I looking in the wrong place? Neither the Book Online or the MS site had anything...


    See topic "Using the inserted and deleted Tables" in BOL. It pretty much gives you what you need spoon by spoon. 🙂

    If you are creating audit records to track changes to your base table in a history or audit location, TRIGGERS are an excellent approach. You can also set up a linked server to route your requests to it. Just be sure to test rollback so failures don't appear in your history location for non-updated rows.

    If possible, you should avoid having an identity column on the table(s) inserted into by the trigger. Your users can have an incorrect @@IDENTITY value returned to them. if you have this situation, you will need to educate your users about IDENT_CURRENT().

  • Also SCOPE_IDENTITY() which works more like we'd expect @@IDENTITY to. These options are available as of SQL2K. They aren't in SQL 7.

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply