Solved

SQL Trigger Help - Update another table after INSERT

Posted on 2012-03-23
3
336 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 69

Accepted Solution

by:
Scott Pletcher earned 500 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 69

Expert Comment

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

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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Near realtime alert if SQL Server services stop. 20 73
Sql query 107 64
Error in SSIS while executing  - Potential data loss 4 24
TSQL - How to declare table name 26 31
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

821 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