Solved

SQL Server Triggers

Posted on 2010-09-07
3
335 Views
Last Modified: 2012-08-14
Is there an example of a trigger in SQL Server that updates a different table. For example, I need to update the history table by inserting a new record when a field is updated in the parent table. Any suggestions or example?
0
Comment
Question by:dodgerfan
3 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 250 total points
ID: 33620096
create trigger mytrigger
on mytablename
after update
as
begin
insert into anotherdb.dbo.tablename(fieldlist)
select fieldlist
from inserted
end
0
 
LVL 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 250 total points
ID: 33620179
Assuming you want to put the old values into history:
CREATE TRIGGER <trigger name> ON <your table>
INSTEAD OF UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    INSERT INTO <history table>
    SELECT <field values>
    FROM <your table>
        INNER JOIN Inserted ON <your table>.<key field> = Inserted.<key field>

    UPDATE <your table>
    SET <field list> = Inserted.<field list>
    FROM <your table>
        INNER JOIN Inserted ON <your table>.<key field>= Inserted.<key field>
END

Open in new window

0
 

Author Comment

by:dodgerfan
ID: 33620628
This is what I have so far, and I'm getting an error:

create trigger updatehistory
on booking
after update
as
begin
insert into bookinghistory(hotelno,guestno,datefrom,dateto,roomno)
select hotelno,guestno,datefrom,dateto,roomno
from inserted
end

The error says:The row value(s) updated or deleted do not make the row unique or they alter mulitple rows(2 rows).
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Alter a column in sql 34 30
why sql server only update some statistics in the database ? 3 32
database level memory cache..? 8 31
Present Absent from working date rage 11 35
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question