November 6, 2002 at 2:05 pm
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...
November 6, 2002 at 2:27 pm
create trigger mytrigger on mytable for update
as
update database_02.dbo.Table1
set myfield = i.myfield
from inserted i
Steve Jones
November 6, 2002 at 3:34 pm
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?
November 6, 2002 at 5:09 pm
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.
November 23, 2002 at 8:57 pm
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
November 25, 2002 at 8:19 am
quote:
When doing an Update, you do have to do a set value for each columnSo if I specify only 4 of the 25 columns to be updated, do the other 21 columns get changed?
November 25, 2002 at 8:39 am
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
November 25, 2002 at 10:37 am
quote:
NO only the columns you specifiy get changedThank 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
December 3, 2002 at 7:46 pm
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().
December 3, 2002 at 7:54 pm
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