Solved

SQL Server Triggers

Posted on 2010-09-07
3
318 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

     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 …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

867 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now