SQL Server Triggers

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?
dodgerfanAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
create trigger mytrigger
on mytablename
after update
as
begin
insert into anotherdb.dbo.tablename(fieldlist)
select fieldlist
from inserted
end
0
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
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
 
dodgerfanAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.