1

Here is my tables:

Report Table:

ReportID    Status
1           'out'
2           null

Log Table

LogID       Status      ReportID
1           'out'       1

When I insert a new record into Log table I want a trigger to update the corresponding status field. Example: Insert to log (LogID=2, Status='out', ReportID=2) I would want to update the report table for the matching ReportID(2) fild for Status so they match.

The output would end up like this; Report Table:

ReportID    Status
1           'out'
2           'out'     //changed from null to out by the Log trigger

Log Table

LogID       Status      ReportID
1           'out'       1
2           'out'       2

The question here is how do I create a Trigger to update the second table when the first one either has a new insert, or an update to an existing record?

Kairan
  • 5,342
  • 27
  • 65
  • 104
  • Why wouldn't you just use a view? – lc. Apr 22 '13 at 01:32
  • @lc. - What would a view do for OP here? I'm genuinely curious, not making a smart aleck comment :) – Tim Apr 22 '13 at 01:46
  • @Tim I'm reading into the question a little bit and guessing the OP wants the Report table to show the latest Status as per the rows in the Log table. Therefore I am questioning the need to denormalize and store the same data in two different places. – lc. Apr 22 '13 at 01:49
  • @lc. - Ah, ok. I see where you're going. Thanks for answering - I was scratching my head there. You should post that as an alternative way to resolve the OP's question, as others in the future might prefer it to a trigger, depending on requirements. – Tim Apr 22 '13 at 01:51

1 Answers1

3

I think you might be looking for something like this:

create trigger updatereport on log 
after insert, update
as
update r
set status = i.status
from report r 
  inner join inserted i on r.reportid = i.reportid;

SQL Fiddle Demo

sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • In MS SQL Server Mgmt Studio should the trigger show up under Programmability>Database Triggers ... if so it didn't show up but I also received no errors when running the code you gave – Kairan Apr 22 '13 at 02:19
  • @Kairan -- don't have it in front of me. I'm pretty sure it shows up under the table -- expand the table and one of the folders you should see is triggers. Here's a post I just looked up: http://stackoverflow.com/questions/12346914/how-to-find-all-trigger-associated-with-a-table-with-sql-server – sgeddes Apr 22 '13 at 02:22
  • Thanks that is where it is found. – Kairan Apr 22 '13 at 02:25