?
Solved

SQL Trigger Help - Update another table after INSERT

Posted on 2012-03-23
3
Medium Priority
?
345 Views
Last Modified: 2012-08-14
Hi Experts, I need a trigger to update table tScheduleRequests when an INSERT is performed on table AR1.  This is actually my first attempt at a trigger and I know it's not too difficult, but I need a push in the right direction... This is what I have so far using the SQL template as a guide:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER trUpdateScheduleRequestsA1
   ON  dbo.AR1
   AFTER INSERT
AS

IF EXISTS (SELECT OrderNumber, SchDte FROM Schedule.dbo.tScheduleRequests, inserted WHERE OrderNumber=Ordno)

BEGIN

	SET NOCOUNT ON;

	UPDATE Schedule.dbo.tScheduleRequests
	SET ScheduledDate=i.SchDte
	INNER JOIN inserted As i
		ON tScheduleRequests.OrderNumber=i.Ordno

END
GO

Open in new window


Thanks in advance

-Jeremy
0
Comment
Question by:FamousMortimer
  • 2
3 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 37758467
CREATE TRIGGER trUpdateScheduleRequestsA1
   ON  dbo.AR1
   AFTER INSERT
AS

UPDATE t
SET ScheduledDate=i.SchDte
FROM Schedule.dbo.tScheduleRequests As t
INNER JOIN inserted As i
      ON t.OrderNumber=i.Ordno

GO
0
 
LVL 10

Author Closing Comment

by:FamousMortimer
ID: 37758663
Thanks a lot
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37758828
You're welcome.

The INNER JOIN automatically prevents unwanted rows from being UPDATEd.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

850 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