Solved

SQL Server Triggers

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

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

708 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

18 Experts available now in Live!

Get 1:1 Help Now